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

[Paul Ramsey]

There are few things left to tweak here...
your streams are (presumably) relatively small features?
your input polygon is very small and simple, so that's not the problem
This is one of the few cases where I actually wonder about PostgreSQL
tuning parameters: is your shared_buffered increased to a reasonable
amount relative to your physical memory (50% of physical memory up to
about 3GB)? If you're having to pull those records off physical disk,
that could slow things down. Does the query run faster the second time
you run it? (see how much caching effects change things)
To the extent that your drawn polygon is non-square, chopping it into
smaller objects will reduce the number of objects that are
fetched-but-not-used. Otherwise, chopping it won't yield any big
improvements, since it's already so simple.
Sorry, not seeing much to tweak,
P.

On Mon, Sep 17, 2012 at 5:57 PM, Shira Bezalel <shira at sfei.org> wrote:
>
> 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>