[postgis-users] very Poor Performance for Contains

[Wood Brent]

>  1. Spatial Operators are very slow

I believe the contains operator does not use the index. See this message/thread
from a month ago when I had a similar problem. Use the && operator in your
where clause to force the spatial index to be used & you should see a huge
performance boost (at least it solved most of my PostGIS performance problems).

http://postgis.refractions.net/pipermail/postgis-users/2004-May/004803.html


In addition to this, I also found that running the query in such a way (eg:
looping through a script with each iteration of the loop including a where
clause restricting the query to a subset of the data) to reduce the size of the
datasets being queried against also made things faster.


eg:

querying 500,000 line features against 50,000 polygons:

The code iterated through the entire dataset in 100 steps instead of one.
Initially this seemes less efficient than one single query, but the memory
useage of the single query meant heaps of disk swapping, the iterative version
below ran entirely in memory and was MUCH faster.

Hope this makes sense...

  Brent Wood



MIN=min(ID) from polygons
MAX=max(ID) from polygons
INTERVAL=( MAX - MIN ) / 100

LOOP=0
while QUERY_MAX <= MAX
do
  QUERY_MIN = MIN + ( LOOP * INTERVAL )
  LOOP = LOOP + 1
  QUERY_MAX = MIN + ( LOOP * INTERVAL )

  select * from poly, line
  where poly.ID < QUERY_MAX
    and poly_ID >= QUERY_MIN
    and poly.the_geom && line.the_geom
    and intersect( poly.the_geom, line.the_geom );   
done