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 <f...@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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
--
Raúl Marín Rodríguez
carto.com
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
