[postgis-users] Spatial query not using the spatial index

[Calvo Arias, Francisco]

Hi all,

I'm having an issue with the planner using a sequential scan instead of the spatial index over a big table with several millions of geometries. My query is similar to this, where the query parameters are the coordinates for a point:

select * from my table
where st_contains(geometry, st_setsrid(st_point(x, y), find_srid('public', 'my_table', 'geometry')))

This occurs with PostGIS 3.0 ('POSTGIS="3.0.3 3.0.3" [EXTENSION] PGSQL="110" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.1.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)"') on Potgres 11 and 9.6, both for Windows and fully patched. Works as expected on other instances with Postgres 10 and 12 and PostGIS 2.5.3 and 3.0.3, respectively.

Using other predicates as st_intersection instead of st_contains or reindexing and analyzing the table doesn't work. Changing find_srid(...) for a constant value uses the expected plan. As a workaround, I've extracted the construction of the second geometry (st_setsrid(...)) to a CTE, giving a plan similar to the expected one.

I'm thinking of a different configuration of the plannner in those instances with the problem, but I'm not really sure.

Any help is welcome.

Thanks in advance,
Paco Calvo

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