[postgis-tickets] [PostGIS] #4802: Spatial query with find_srid() not using the spatial index

[PostGIS]

#4802: Spatial query with find_srid() not using the spatial index
------------------------+---------------------------
 Reporter:  Paco Calvo  |      Owner:  pramsey
     Type:  defect      |     Status:  new
 Priority:  medium      |  Milestone:  PostGIS 3.1.0
Component:  postgis     |    Version:  3.0.x
 Keywords:              |
------------------------+---------------------------
 Hi,

 After posting a message on postgis-users about this, I'd been advised to
 open a ticket here. That message follows.

 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.

 Thanks in advance,
 Paco Calvo

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4802>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.