ST_Intersects

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ST_Intersects

Jonathan Moules-4
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.

Thanks,
Jonathan

PostgreSQL 9.4.8 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
"POSTGIS="2.1.8 r13780" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER"

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_Intersects

Giuseppe Broccolo
Hi Jonathan,

2017-03-17 13:18 GMT+01:00 Jonathan Moules <[hidden email]>:
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
[hidden email] | www.2ndQuadrant.it

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ST_Intersects

Jonathan Moules-4
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<[hidden email]> wrote ----
Hi Jonathan,

2017-03-17 13:18 GMT+01:00 Jonathan Moules <[hidden email]>:
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
[hidden email] | www.2ndQuadrant.it
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...