<!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 && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"<br>" Rows Removed by Index Recheck: 414647"<br>" Filter: _st_intersects(geom, '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::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 && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::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 && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)<br> Filter: _st_intersects(geom, '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)<br> -> Bitmap Index Scan on pg_geom_gix_active (cost=0.00..36653.75 rows=1210444 width=0)<br> Index Cond: (geom && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::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>