[postgis-users] Postgis too slow

[Paul Ramsey]

OK, I am now pretty sure the problem was (2) in my previous email. Your
mapfile layer definition is below. What you are doing is emulating an
item query by using the FILTER parameter. This is *bad*. It will *not*
work, because the spatial parameter is way too big, and your attribute
indexes will never ever get used. The pgsql query optimizer just does
not know how to handle this combination of spatial and attribute
indexes. 

To verify that this is the problem, either (1) change your EXTENT so
that it is a very *small* box which contains just your one test road and
very little else. Now see how fast it runs. Or (2) remove the FILTER
entirely (don't to 1=2, *remove* it) and zoom into a small area. Now pan
around the dataset. That should demonstrate the native spatial query
speed.

You are not going to be able to do this query with the standard CGI
interface. You are going to have to use PHP MapScript and use PHP direct
connection to pgsql to do the attribute query and find the extent of the
resultant lines, then set the extent of your map appropriately, then
render. If you have simple queries, you can use the itemquery in the CGI
interface, but your query is too complex for that.

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


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