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 <[email protected]> En nombre de Giuseppe
Broccolo
Enviado el: martes, 24 de noviembre de 2020 13:41
Para: PostGIS Users Discussion <[email protected]>
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
<[email protected]<mailto:[email protected]>> 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
[email protected]<mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
