[postgis-users] Postgis too slow

[Stefano Bonnin]

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