[postgis-users] ST_Intersects

Jonathan Moules jonathan-lists at lightpear.com
Mon Mar 20 03:14:39 PDT 2017


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 && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)"
"  Rows Removed by Index Recheck: 414647"
"  Filter: _st_intersects(geom, '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::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 && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::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 at 2ndquadrant.it> wrote ---- 

Hi Jonathan,

2017-03-17 13:18 GMT+01:00 Jonathan Moules <jonathan-lists at 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 && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)
  Filter: _st_intersects(geom, '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::geometry)
  ->  Bitmap Index Scan on pg_geom_gix_active  (cost=0.00..36653.75 rows=1210444 width=0)
        Index Cond: (geom && '0103000020346C000001000000050000000000000000BD0F410000000030FD23410000000000BD0F41000000004024244100000000C07A1041000000004024244100000000C07A10410000000030FD23410000000000BD0F410000000030FD2341'::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 at 2ndQuadrant.it | www.2ndQuadrant.it 



 


 _______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170320/d5e4f1e9/attachment.html>


More information about the postgis-users mailing list