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

Tue Sep 18 14:11:17 PDT 2012

Please do put it online and let me know.
P

On Tue, Sep 18, 2012 at 2:49 PM, Shira Bezalel <shira at sfei.org> wrote:
> 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
>
> ________________________________
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>