[postgis-tickets] [PostGIS] #4802: Spatial query with find_srid() not using the spatial index

[PostGIS]

#4802: Spatial query with find_srid() not using the spatial index
-------------------------+---------------------------
  Reporter:  Paco Calvo  |      Owner:  pramsey
      Type:  defect      |     Status:  new
  Priority:  low         |  Milestone:  PostGIS 3.0.4
 Component:  postgis     |    Version:  3.0.x
Resolution:              |   Keywords:
-------------------------+---------------------------
Changes (by robe):

 * priority:  medium => low
 * milestone:  PostGIS 3.1.0 => PostGIS 3.0.4


Comment:

 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
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4802#comment:5>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.