[postgis-users] ST_Intersects
Jonathan Moules
jonathan-lists at lightpear.com
Fri Mar 17 05:18:56 PDT 2017
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>
More information about the postgis-users
mailing list