Hi, I'm unsure how good ST_Transform performs. Did you consider adding a partial functional index like described in [1] ?
-S. [1] http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html 2012/9/18 Shira Bezalel <sh...@sfei.org>: > > 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 > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
