[postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?

[Shira Bezalel]

I'm trying to determine if the response time we're seeing on a query is reasonable or if there's anything we can do to speed it up. Looking for advice and/or a reality check. 


In general, we benefit from dicing our large polygon layers to speed up intersection queries (a big thanks to Paul Ramsey for that pointer), but the catch with this particular query is that it is issued from a web-based GIS application where a user draws a (potentially very large) polygon on-the-fly and then total length calculations are made for intersecting line features within this dynamically drawn polygon. I've even tried dicing dynamically, but it seems to only add more overall time. 


Here is the SQL for one particular polygon I drew while testing: 





SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) * 0.00062137119AS miles 

FROM baari_streams s 

WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 )) 

GROUP BY s.legcode, s.strahler 

ORDER BY s.legcode, s.strahler; 






The explain analyze output: 


http://explain.depesz.com/s/PNZ 


The line table has 254833 records. It has a spatial index and the optimizer is using it. The index is clustered. And I have run vacuum analyze on it. 


So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863 rows). Is this about as good as can be expected? Ideally, we'd love to see this return in about 1 second or less. Is that unreasonable? 


Version info: 
PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit 
POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS 


Thanks for any insight you can provide. 


Shira 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120917/b5529a4e/attachment.html>