[postgis-users] Postgis too slow

[Paul Ramsey]

You will need an OID index for sure...

create index street_oid_index on streets ( oid );


Stefano Bonnin wrote:
> 
> Yesterday I didn't explain my problem very well, but now I can do this.
> 
> This is map postgis layer:
> 
> LAYER
> STATUS OFF
>   NAME "stradeQ"
> CONNECTIONTYPE postgis
> CONNECTION "user=postgres password=postgres dbname=market host=server
> port=5432"
>   DATA "the_geom from streets"
> TYPE LINE
> FILTER "(upper(st_nm_base) Like upper('%street%')) AND (l_area_id Like
> '%value%')
> AND ('%number%'='' or '%number%' between l_refaddr and l_nrefaddr or
> '%number%'
> between r_refaddr and r_nrefaddr) "
> 
> CLASS
>   SYMBOL 0
>   OUTLINECOLOR 25 25 0
> END
> 
> TEMPLATE market.html
> 
> END
> 
> I have created the following index:
> 
> create index street_index on streets (st_nm_base, l_area_id);
> 
> where st_nm_base is the street name and l_area_id is a city identifier.
> 
> if I try this query under postgresql, it responds immediately (but postgis
> is always very very slow).
> 
> I know that I'm doing something wrong but I don't know what!
> 
> Thanks!
> 
> Stefano Bonnin
> 
> ----- Original Message -----
> From: "Dave Blasby" <dblasby at refractions.net>
> To: <postgis-users at postgis.refractions.net>
> Sent: Wednesday, May 15, 2002 6:51 PM
> Subject: Re: [postgis-users] Postgis too slow
> 
> > Paul Ramsey wrote:
> > >
> > > Check some queries, do some bounding box queries using 'explain' and see
> > > if you are getting index scans. Remember to 'vacuum analyze' your
> > > database after building the indexes. Be peaceful and good.
> >
> > Dont forget to put an index on the OID of the table you're using.
> >
> > create index oid_geo_idx on <table> (OID);
> >
> > dave
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> ---
> Outgoing mail is certified Virus Free. (FLASHH!)
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.361 / Virus Database: 199 - Release Date: 07/05/02
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users