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