[postgis-users] Postgis too slow

Obe, Regina DND\MIS robe.dnd at ci.boston.ma.us
Thu May 23 07:40:04 PDT 2002


I find all this pretty fascinating and so far educational.  I didn't realize
how the mapserver and postgis integration works.  I have a similar query
setup using CGI and the speed is pretty decent (under a second local)  (but
the table I use generally for street segment only has 40,000 street segments
and my memory and processor are a bit better than Stefano's. 

One thing I was wondering about - and whether anyone has tried this.
Stefano says he has a 600 Mhz processor with 192mb of ram and an 800,000
record set.  I'm wondering if anyone has actually tried doing what he is
doing using 192mb of memory with that many records.  I told him I can barely
get my server to work with all the services it has to run for that little
amount of on-board memory.  All my servers have at least 256MB ram - even
the really stupid ones that do nothing.

The other curiosity I had is that if you set the Layer type to QUERY instead
of LINE does it still incur the same spatial query penalty - or would
mapserver-postgis be smart enough to say ("I'm not going to render this so
just need the extent") and further if the map mode was set to itemquery
instead of itemnquery - if that minimizes on spatial query penalty since it
knows to only grab first result.


 

-----Original Message-----
From: Paul Ramsey [mailto:pramsey at refractions.net]
Sent: Wednesday, May 22, 2002 7:09 PM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] Postgis too slow


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
     \_

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users




More information about the postgis-users mailing list