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

[Calvo Arias, Francisco]

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-bounces at lists.osgeo.org> En nombre de Giuseppe Broccolo
Enviado el: martes, 24 de noviembre de 2020 13:41
Para: PostGIS Users Discussion <postgis-users at 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 <fcaa at tragsa.es<mailto:fcaa at 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 at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20201124/01dc4f4c/attachment.html>