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

Tue Sep 18 12:49:44 PDT 2012

Well, I don't *think* the lines layer is large, but it's hard for me to say. Here are some stats from the intersecting lines on the original query: 

Number of lines: 28863 
Sum of vertices/points = 296079 
Avg number of points per feature = 10.258 

Is that considered large or small? 

I'd be happy to pass on the streams layer if you're interested in taking a look at it. I've got a 40 MB zipped up shape file that I can send to you or place on a FTP site. 

Shira 

----- Original Message -----

From: "Paul Ramsey" <pramsey at opengeo.org> 
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net> 
Sent: Tuesday, September 18, 2012 11:25:41 AM 
Subject: Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly? 

Well, I still feel that 3.5 seconds is too long for pulling and 
intersecting 20000 features, *assuming* those features are not large 
(not too many vertices) so if you confirm that the streams are not 
massive objects I'm out of guesses. I'd be interested to stick your 
data and query into a profiler and see if there's a spanner in the 
gears somewhere. 

P. 

On Tue, Sep 18, 2012 at 10:01 AM, Shira Bezalel <shira at sfei.org> wrote: 
> Hi Paul, 
> 
> Thanks for the response. It's helpful to have the confirmation that this is 
> probably about as fast as we're going to get for now. 
> 
> The shared_buffers parameter is set to 3 GB (total machine RAM = 12 GB) so I 
> think we're good on that front. 
> 
> Yes, it's somewhat faster the second time I run the query. Observing the 
> cache hit ratio, it definitely looks like it is having to pull records off 
> physical disk the first time. But can we do anything about this? Isn't the 
> first read always going to be from disk? And the second time from cache? Is 
> there a way to bring the data into cache ahead of time? 
> 
> Shira 
> 
> 
> 
> ________________________________ 
> From: "Paul Ramsey" <pramsey at opengeo.org> 
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net> 
> Sent: Tuesday, September 18, 2012 5:47:36 AM 
> Subject: Re: [postgis-users] How to speed up a spatial intersection where 
> the intersecting polygon is drawn on-the-fly? 
> 
> 
> 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 
>> 
> _______________________________________________ 
> postgis-users mailing list 
> postgis-users at postgis.refractions.net 
> http://postgis.refractions.net/mailman/listinfo/postgis-users 
> 
> 
> _______________________________________________ 
> postgis-users mailing list 
> postgis-users at postgis.refractions.net 
> http://postgis.refractions.net/mailman/listinfo/postgis-users 
> 
_______________________________________________ 
postgis-users mailing list 
postgis-users at postgis.refractions.net 
http://postgis.refractions.net/mailman/listinfo/postgis-users 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120918/2182e752/attachment.html>