Hi Giusseppe, Thanks for your reply. As I was told before, there is now a ticket opened for this (https://trac.osgeo.org/postgis/ticket/4802)
The query is executed as a parameterized query from a client, but also testing from pgAdmin with the parameters substituted for constants (for instance, as 0,0) gives the same result: select * from my_table where st_contains(geometry, st_setsrid(st_point(0, 0), find_srid('public', 'my_table', 'geometry'))); I’ve run ANALYZE on the table but the plan didn’t change. Regards, Paco Calvo De: postgis-users <postgis-users-boun...@lists.osgeo.org> En nombre de Giuseppe Broccolo Enviado el: martes, 24 de noviembre de 2020 13:41 Para: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Asunto: Re: [postgis-users] Spatial query not using the spatial index Hi Paco, How is the query executed in the DB? Is it a prepared query, or are parameters (x, y) passed on the client side and then the resolved query is executed in the DB? In case of prepared query, the planner uses a "general" plan to execute the query: the plan is eventually corrected comparing the estimated costs in executing the query with the costs of the general plan, or used otherwise. The general plan is computed after the first 5 executions of the query, where execution plans are always estimated. From the 6th execution, the planner will try to use the general plan. Although the plan can be corrected basing on the passed parameters to the prepared statement, it can lead to odd behaviour since the correction is based on the costs, specially if the statistics related to the table are not properly updated. But please provide more details about how you execute the query. Giuseppe. Il giorno mar 24 nov 2020 alle ore 10:23 Calvo Arias, Francisco <f...@tragsa.es<mailto:f...@tragsa.es>> ha scritto: 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 _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users