[postgis-users] ST_Intersects

[Jonathan Moules]

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"

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