[postgis-users] ST_Intersects

[Giuseppe Broccolo]

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 && '0103000020346C0000010000000500
> 00000000000000BD0F410000000030FD23410000000000BD0F4100000000
> 4024244100000000C07A1041000000004024244100000000C07A10410000
> 000030FD23410000000000BD0F410000000030FD2341'::geometry)
>   Filter: _st_intersects(geom, '0103000020346C0000010000000500
> 00000000000000BD0F410000000030FD23410000000000BD0F4100000000
> 4024244100000000C07A1041000000004024244100000000C07A10410000
> 000030FD23410000000000BD0F410000000030FD2341'::geometry)
>   ->  Bitmap Index Scan on pg_geom_gix_active  (cost=0.00..36653.75
> rows=1210444 width=0)
>         Index Cond: (geom && '0103000020346C0000010000000500
> 00000000000000BD0F410000000030FD23410000000000BD0F4100000000
> 4024244100000000C07A1041000000004024244100000000C07A10410000
> 000030FD23410000000000BD0F410000000030FD2341'::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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170317/d9374669/attachment.html>