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

[Calvo Arias, Francisco]

Hi Raúl,

Thanks of your quick reply. Tought about that also, but find_srid is defined equally in every instance as 
CREATE OR REPLACE FUNCTION public.find_srid(
	character varying,
	character varying,
	character varying)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    STABLE STRICT PARALLEL SAFE 

100 is the usual cost for a plpgsql-based function, but maybe it could be defined as INMUTABLE instead of STABLE (despite having a database lookup)

As I've said before, the bad planning occurs in some instances (PostGIS 3.0 on Pg 9.6 and 11) but not in others (PostGIS 2.5 and 3.0 on Pg 10 and 12, plus several other combinations).

I'll open an issue there.

Regards,
Paco Calvo

-----Mensaje original-----
De: postgis-users <postgis-users-bounces at lists.osgeo.org> En nombre de rmrodriguez at carto.com
Enviado el: martes, 24 de noviembre de 2020 11:47
Para: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Asunto: Re: [postgis-users] Spatial query not using the spatial index

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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users