[postgis-users] Query performance issue

[Stephen Woodbridge]

I want to thank everyone for their help, suggestions and in general all 
the support I got from the list. Here are the results:

I had to do 4 updates like the following:

update streets set boundary=b.name from polygon_layer b
where
     streets.the_geom && b.the_geom
   and
     intersects(streets.the_geom, b.the_geom)

streets has just under 2 million records

the polygon_layers and run times follow. The size of the polygons in 
spatial area decrease from A -> D

layer| #poly | time hr | function
---------------------------------
A    |    14 |    *    | n/a
B    |   230 |   30    | intersects()
C    |  3991 |   33    | distance() <=0
D    |  2432 |    3    | intersects()

I tried A first and aborted it after to separate 15 hr runs and 
ultimately solve that one a different way by updating the records based 
on min and max gid values. After have some success with D, I decided to 
run the others and see how long it took.

I guess I should look at the GEOS code and see how it works. Does anyone 
know if it uses the Sutherland line clipping algorithm which is probably 
the fastest way to test if two segments intersect without computing the 
intersection.

Anyway, Thanks again everyone.
   -Steve