Please do put it online and let me know. P
On Tue, Sep 18, 2012 at 2:49 PM, Shira Bezalel <sh...@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" <pram...@opengeo.org> > To: "PostGIS Users Discussion" <postgis-users@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 <sh...@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" <pram...@opengeo.org> >> To: "PostGIS Users Discussion" <postgis-users@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 <sh...@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@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 >> >> >> _______________________________________________ >> 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 > > > _______________________________________________ > 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
