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

[Giuseppe Broccolo]

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> 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
> 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/72bcdc66/attachment.html>