
<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: Arial; font-size: 10pt; color: #000000'><br><font face="Arial" size="2">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. </font><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">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.</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">Here is the SQL for one particular polygon I drew while testing:</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><blockquote style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; margin: 0px 0px 0px 40px; border: none; padding: 0px; "><div><div>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 </div></div><div><div>FROM baari_streams s </div></div><div><div>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 )) </div></div><div><div>GROUP BY s.legcode, s.strahler </div></div><div><div>ORDER BY s.legcode, s.strahler;</div></div></blockquote><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">The explain analyze output:</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><a href="http://explain.depesz.com/s/PNZ">http://explain.depesz.com/s/PNZ</a>

</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">The line table has 254833 records. <span style="font-size: 10pt; ">It has a spatial index and the optimizer is using it. The index is clustered. And I have run vacuum analyze on it.</span></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">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?</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">Version info:</div><div><font face="Arial" size="2">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</font></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">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</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">Thanks for any insight you can provide.</div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; "><br></div><div style="color: rgb(0, 0, 0); font-family: Arial; font-size: 10pt; ">Shira</div></div></body></html>