Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
Hi, I'm unsure how good ST_Transform performs. Did you consider adding a partial functional index like described in [1] ? -S. [1] http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html 2012/9/18 Shira Bezalel sh...@sfei.org: 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
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
Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
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 - Original Message - 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
Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
Hi Stefan. Thanks for your response. Can you clarify: Where would we put the functional index? The polygon is being drawn dynamically. Shira - Original Message - From: Stefan Keller sfkel...@gmail.com To: PostGIS Users Discussion postgis-users@postgis.refractions.net Cc: Patty Frontiera pat...@sfei.org Sent: Monday, September 17, 2012 11:23:13 PM Subject: Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly? Hi, I'm unsure how good ST_Transform performs. Did you consider adding a partial functional index like described in [1] ? -S. [1] http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html 2012/9/18 Shira Bezalel sh...@sfei.org: 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
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 2 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
Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
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 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 2 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
Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
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 2 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
[postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
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
