[postgis-users] Query performance issue

Stephen Woodbridge woodbri at swoodbridge.com
Mon Oct 17 07:22:56 PDT 2005

Robert Burgholzer wrote:
> So what do queries A-D look like? (or were they in a previous post)?
> r.b.

All the queries were structurally identical to the update below only 
each referenced a separate polygon table called "polygon_layer" below.

The exact queries were also list in previous emails.


> On Mon, 2005-10-17 at 00:16, Stephen Woodbridge wrote:
>>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
>>     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 
>>Anyway, Thanks again everyone.
>>   -Steve
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net

More information about the postgis-users mailing list