[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] ST_Intersects
From: Jonathan Moules <jonathan-lists () lightpear ! com>
Date: 2017-03-20 10:14:39
Message-ID: 15aeb3539f9.b24348b869034.4507481367730047452 () lightpear ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi Giuseppe,
Thanks for your response. I've now tried with Explain Analyze - runtime was 16.5 \
minutes. Below is the output:
"Bitmap Heap Scan on polygon_active (cost=36754.62..2156740.85 rows=403481 \
width=358) (actual time=160971.128..991459.050 rows=920180 loops=1)" " Recheck Cond: \
(geom && '0103000020346C000001000000050000000000000000BD0F410000000030FD234100 \
00000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"
" Rows Removed by Index Recheck: 414647"
" Filter: _st_intersects(geom, \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"
" Rows Removed by Filter: 113"
" Heap Blocks: exact=88041 lossy=59345"
" -> Bitmap Index Scan on pg_geom_gix_active (cost=0.00..36653.75 rows=1210444 \
width=0) (actual time=160871.309..160871.309 rows=920293 loops=1)" " Index \
Cond: (geom && \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"
"Planning time: 0.225 ms"
"Execution time: 991601.431 ms"
As best I can tell from my very limited experience reading these, it did what it \
thought it would do, but the index scan was slightly slower and the Bitmap Heap Scan \
faster. It seems there are twice as many rows as it expected (920180). Is 16 mins a \
reasonable amount of time for such a query? Can it be sped up?
Thanks,
Jonathan
---- On Fri, 17 Mar 2017 14:19:55 +0000 Giuseppe \
Broccolo<giuseppe.broccolo@2ndquadrant.it> wrote ----
Hi Jonathan,
2017-03-17 13:18 GMT+01:00 Jonathan Moules <jonathan-lists@lightpear.com>:
Hi List,
I'm doing a simple ST_Intersects:
select geom
from TABLENAME
where
ST_Intersects(geom, ST_GeomFromText('POLYGON((260000 655000, 260000 660000, \
270000 660000, 270000 655000, 260000 655000))', 27700))
TABLENAME does have a spatial index. But this query is still taking an exceptionally \
long time to run. The source table has about 62million features of relatively low \
complexity (no donuts, but a few self/ring-intersections). ANALYZE has been run.
This is the Explain:
Bitmap Heap Scan on polygon_active (cost=36754.62..2156740.85 rows=403481 width=381)
Recheck Cond: (geom && \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)
Filter: _st_intersects(geom, \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)
-> Bitmap Index Scan on pg_geom_gix_active (cost=0.00..36653.75 rows=1210444 \
width=0) Index Cond: (geom && \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)
From my interpretation of the above, the index scan is quick, but then the \
fine-detail confirmation is very-very slow. Reading around on the list archives, it \
probably shouldn't be this slow given this is a relatively recent version of PostGIS \
(version info below). Is there a way to speed this up given my input geometry is a \
simple bounding box.
To better understand the corresponding time of each execution node, you should run an \
EXPLAIN ANALYSE of the query. From what I can see here, the planner expects to do a \
lot of work during recheck condition where the exact intersection (i.e.
not between just bounding boxes) is performed: here it expects to inspect more than \
2M of data blocks, filtering 400k rows from 1M rows.
Could you attach here the output of an EXPLAIN (ANALYSE, BUFFER) of the query, if the \
execution does not take too long?
In any case, I'm posting here an useful link by Regina:
http://postgis.net/2014/03/14/tip_intersection_faster/
About how to rewrite and improve queries when intersections are involved (some \
computation can be avoided for specific cases).
All the best,
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><meta \
content="text/html;charset=UTF-8" http-equiv="Content-Type"></head><body ><div \
style='font-size:10pt;font-family:Verdana,Arial,Helvetica,sans-serif;'>Hi \
Giuseppe,<br>Thanks for your response. I've now tried with Explain Analyze - runtime \
was 16.5 minutes. Below is the output:<br><br><br>"Bitmap Heap Scan on \
polygon_active (cost=36754.62..2156740.85 rows=403481 width=358) (actual \
time=160971.128..991459.050 rows=920180 loops=1)"<br>" Recheck Cond: (geom \
&& '0103000020346C000001000000050000000000000000BD0F410000000030FD234100000000 \
00BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"<br>" \
Rows Removed by Index Recheck: 414647"<br>" Filter: _st_intersects(geom, \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"<br>" \
Rows Removed by Filter: 113"<br>" Heap Blocks: exact=88041 \
lossy=59345"<br>" -> Bitmap Index Scan on pg_geom_gix_active \
(cost=0.00..36653.75 rows=1210444 width=0) (actual time=160871.309..160871.309 \
rows=920293 loops=1)"<br>" Index Cond: \
(geom && '0103000020346C000001000000050000000000000000BD0F410000000030FD234100 \
00000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"<br>"Planning \
time: 0.225 ms"<br>"Execution time: 991601.431 ms"<br><br>As best I can tell from my \
very limited experience reading these, it did what it thought it would do, but the \
index scan was slightly slower and the Bitmap Heap Scan faster.<br>It seems there are \
twice as many rows as it expected (920180). Is 16 mins a reasonable amount of time \
for such a query? Can it be sped up?<br><br>Thanks,<br>Jonathan<br><br><div \
class="zmail_extra"><div id="1"><br>---- On Fri, 17 Mar 2017 14:19:55 +0000 \
<b>Giuseppe Broccolo<giuseppe.broccolo@2ndquadrant.it></b> wrote ---- \
<br></div><blockquote style="border-left: 1px solid #0000FF; padding-left: 6px; \
margin:0 0 0 5px"><div><div dir="ltr">Hi Jonathan,<br><div><div><br><div>2017-03-17 \
13:18 GMT+01:00 Jonathan Moules <span><<a subj="" \
mailid="jonathan-lists%40lightpear.com" rel="noreferrer" \
href="mailto:jonathan-lists@lightpear.com" \
target="_blank">jonathan-lists@lightpear.com</a>></span>:<br><blockquote \
style="margin: 0.0px 0.0px 0.0px 0.8ex;border-left: 1.0px solid \
rgb(204,204,204);padding-left: 1.0ex;"><u></u><div><div style="font-size: \
10.0pt;font-family: verdana , arial , helvetica , sans-serif;">Hi List,<br>I'm doing \
a simple ST_Intersects:<br><br>select geom<br> from \
TABLENAME<br> where <br> ST_Intersects(geom, \
ST_GeomFromText('POLYGON((260000 655000, 260000 660000, 270000 660000, 270000 655000, \
260000 655000))', 27700))<br> <br>TABLENAME does have a spatial \
index. But this query is still taking an exceptionally long time to run. The source \
table has about 62million features of relatively low complexity (no donuts, but a few \
self/ring-intersections). ANALYZE has been run.<br><br>This is the Explain:<br>Bitmap \
Heap Scan on polygon_active (cost=36754.62..2156740.85 rows=403481 \
width=381)<br> Recheck Cond: (geom && \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)<br> \
Filter: _st_intersects(geom, \
'0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000 \
000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)<br> \
-> Bitmap Index Scan on pg_geom_gix_active (cost=0.00..36653.75 \
rows=1210444 width=0)<br> Index Cond: (geom \
&& '0103000020346C000001000000050000000000000000BD0F410000000030FD234100000000 \
00BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)<br><br><br>From \
my interpretation of the above, the index scan is quick, but then the fine-detail \
confirmation is very-very slow. Reading around on the list archives, it probably \
shouldn't be this slow given this is a relatively recent version of PostGIS (version \
info below).<br>Is there a way to speed this up given my input geometry is a simple \
bounding box.<br></div></div></blockquote><div><br></div><div>To better understand \
the corresponding time of each execution node, you should run an EXPLAIN ANALYSE of \
the query. From what I can see here, the planner expects to do a lot of work during \
recheck condition where the exact intersection (i.e.<br></div><div>not between just \
bounding boxes) is performed: here it expects to inspect more than 2M of data blocks, \
filtering 400k rows from 1M rows. <br></div></div><br></div><div>Could you \
attach here the output of an EXPLAIN (ANALYSE, BUFFER) of the query, if the execution \
does not take too long?<br><br></div><div>In any case, I'm posting here an useful \
link by Regina:<br><br><a rel="noreferrer" \
href="http://postgis.net/2014/03/14/tip_intersection_faster/" \
target="_blank">http://postgis.net/2014/03/14/tip_intersection_faster/</a><br><br></div><div>About \
how to rewrite and improve queries when intersections are involved (some computation \
can be avoided for specific cases).<br></div><div><br></div><div>All the \
best,<br></div><div>Giuseppe.<br></div><div><br>-- <br><div><div dir="ltr"><div><div \
dir="ltr"><span></span>Giuseppe Broccolo - 2ndQuadrant Italy <br>PostgreSQL & \
PostGIS Training, Services and Support <br><a subj="" \
mailid="giuseppe.broccolo%402ndQuadrant.it" rel="noreferrer" \
href="mailto:giuseppe.broccolo@2ndQuadrant.it" \
target="_blank">giuseppe.broccolo@2ndQuadrant.it</a> | <a rel="noreferrer" \
href="http://www.2ndQuadrant.it" target="_blank">www.2ndQuadrant.it</a> \
</div></div></div></div> </div></div></div> \
_______________________________________________<br>postgis-users mailing list<br><a \
subj="" mailid="postgis-users%40lists.osgeo.org" rel="noreferrer" \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br><a rel="noreferrer" \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div></blockquote><br></div><br></div></body></html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic