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

[rmrodriguez at carto.com]

Hi,

This is likely an issue with the support functions for PG12+ thinking
`find_srid('public', 'my_table', 'geometry')` is not constant, or not
enough. Can you open an issue on trac
(https://trac.osgeo.org/postgis/) please?

On Tue, Nov 24, 2020 at 11:23 AM Calvo Arias, Francisco <fcaa at tragsa.es> wrote:
>
> 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



-- 
Raúl Marín Rodríguez
carto.com