[postgis-users] Postgis too slow

[Paul Ramsey]

I think I see it... there are two possibilities:

1)

You created an index on st_nm_base, but you are querying on
upper(st_nm_base). It is not guaranteed that you will get to use the
index when you transform the value you are searching on. You say that
the query works very fast in psql and very slow in postgis. Did you try
*exactly* the same query in both?

2)

By using a layer like this, you are doing two searches at once. The
spatial side of mapserver is saying "OK, I need every feature inside the
bounding box". If your search area is unconstrainted (the whole extent
of the data, for example) that will be a very large set of lines.
Secondly, there is the filter restriction. Ordinarily, the PostgreSQL
optimizer will analyze the query plan and try to figure out which index
will get the best "bang for the buck" in terms of reducing the result
set, and use that one. In an ideal world, the planner would recognize
that your attribute restriction will reduce the set to almost nothing
while the spatial restriction hardly reduces it at all. What is
happening is that you are getting zero advantage from the filter. The
spatial restriction is overriding the attribute restriction, so the
attribute tests end up being sequence scans instead of index scans. 

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

-- 
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey at refractions.net
     | Phone: (250) 885-0632
     \_