Spatial query with find_srid() not using the spatial index

[Paco Calvo]

Hi,

After posting a message on postgis-users about this, I'd been advised to open a ticket here. That message follows.

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.

Thanks in advance, Paco Calvo

[Paco Calvo]

As said before, st_intersects() also presents this problem, but && operator (forgiving its different result) doesn't, using the spatial index as expected.

[Paco Calvo]

Hi,

Could this be related to ​https://trac.osgeo.org/postgis/ticket/4341 (Use PgSQL12 "support function" API for indexed functions) as suggested by Raúl Marín on postigs-users?

[pramsey]

Yes, it probably is related to support functions. You can test by re-defining find_srid() as an IMMUTABLE (rather than STABLE) function and see if the problem goes away. I suspect that PgSQL doesn't consider the function to pass is_pseudo_constant_for_index(). Since there's a workaround and the problem is rare, I'm not inclined to spend a lot of time trying to unpick this knot.

[Paco Calvo]

Re-defining find_srid() as an IMMUTABLE function works as expected.

Also note, this problem seems to arise with PostGIS 3.0 on PostgreSQL previous to v12 (as tested in 9.6 and 11). Using PostGIS 3.0 on PostgreSQL 12 and 13 works fine.

[robe]

As Paco Calvo mentioned in last comment - this actually works fine in PostGIS 3.0 and PostgreSQL 12. It's an issue with non-support function system. I've pushed this back to 3.0.4 as it's not serious and only impacts lower PostgreSQL versions and also downgrading to low.

I just confirmed it's a non-issue with PostGIS 3 + PostgreSQL 12 (at least not for small tables). I'll retest later to confirm it is an issue with PostgreSQL < 12

{{{ -- 217 rows SELECT * FROM geo.zcta5_all_2018 where st_intersects(the_geom, st_setsrid(ST_Expand(st_point(-72, 42),0.5,0.5), find_srid('geo', 'zcta5_all_2018', 'the_geom'))) }}}

Index Scan using ix_zcta5_all_2018_the_geom_gist on zcta5_all_2018  (cost=0.78..93.33 rows=3 width=99) (actual time=0.311..23.100 rows=217 loops=1)
  Index Cond: (the_geom && st_setsrid('0103000000010000000500000000000000002052C00000000000C0444000000000002052C000000000004045400000000000E051C000000000004045400000000000E051C00000000000C0444000000000002052C00000000000C04440'::geometry, find_srid('geo'::character varying, 'zcta5_all_2018'::character varying, 'the_geom'::character varying)))
  Filter: st_intersects(the_geom, st_setsrid('0103000000010000000500000000000000002052C00000000000C0444000000000002052C000000000004045400000000000E051C000000000004045400000000000E051C00000000000C0444000000000002052C00000000000C04440'::geometry, find_srid('geo'::character varying, 'zcta5_all_2018'::character varying, 'the_geom'::character varying)))
Planning Time: 0.485 ms
Execution Time: 23.128 ms

My ST_Contains query

{{{ -- returns 1 record SELECT * FROM geo.zcta5_all_2018 WHERE st_contains(the_geom, st_setsrid(st_point(-72, 42), find_srid('geo', 'zcta5_all_2018', 'the_geom'))) ; }}}

Bitmap Heap Scan on zcta5_all_2018  (cost=5.03..966.21 rows=3 width=99) (actual time=0.301..0.302 rows=1 loops=1)
  Filter: st_contains(the_geom, st_setsrid('010100000000000000000052C00000000000004540'::geometry, find_srid('geo'::character varying, 'zcta5_all_2018'::character varying, 'the_geom'::character varying)))
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on ix_zcta5_all_2018_the_geom_gist  (cost=0.00..5.02 rows=33 width=0) (actual time=0.179..0.180 rows=1 loops=1)
        Index Cond: (the_geom ~ st_setsrid('010100000000000000000052C00000000000004540'::geometry, find_srid('geo'::character varying, 'zcta5_all_2018'::character varying, 'the_geom'::character varying)))
Planning Time: 0.349 ms
Execution Time: 0.322 ms

This is on a database running:

POSTGIS="3.0.2 3.0.2" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.4, released 2020/01/08" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" RASTER PostgreSQL 12.4, compiled by Visual C++ build 1914, 64-bit

[pramsey]

Given that this problem is (a) gone in current versions and (b) involves a _hidden_function() and (c) has a workaround, I propose to close as wontfix.