[postgis-tickets] [PostGIS] #3675: Relationship functions not using an index in some cases

[PostGIS]

#3675: Relationship functions not using an index in some cases
----------------------+---------------------------
  Reporter:  robe     |      Owner:  robe
      Type:  defect   |     Status:  closed
  Priority:  blocker  |  Milestone:  PostGIS 2.3.2
 Component:  postgis  |    Version:  2.3.x
Resolution:  fixed    |   Keywords:
----------------------+---------------------------

Comment (by robe):

 I reconfirmed with 2.4.1 and changing ST_SetSRID cost from 1 to 100 makes
 my bad case not use an index and switching it back to COST 1 makes it use
 an index.

 This is using:


 {{{
 PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit POSTGIS="2.4.1
 r16012" PGSQL="96" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.3, 15
 August 2016" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.7.8"
 LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER
 }}}

 Also ran same test on:


 {{{
 PostgreSQL 10.0 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by
 MSYS2 project) 4.9.2, 64-bit POSTGIS="2.4.0 r15853" PGSQL="100"
 GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.2, 08 September 2015"
 GDAL="GDAL 2.2.0, released 2017/04/28" LIBXML="2.9.4" LIBJSON="0.12.1"
 RASTER
 }}}


 and got same behavior


 {{{
 SELECT gid
 FROM  e, (SELECT longitude, latitude FROM e WHERE longitude = -1 and
 latitude = -1) As c
 WHERE   ST_DWithin(e.geom ,
 ST_SetSRID(ST_Point( c.longitude, c.latitude), 4326), 1);
 }}}


 Plan with ST_SetSRID COST 1 yields


 {{{

 Nested Loop
   ->  Seq Scan on e e_1
         Filter: ((longitude = '-1'::integer) AND (latitude =
 '-1'::integer))
   ->  Bitmap Heap Scan on e
         Recheck Cond: (geom &&
 st_expand(st_setsrid(st_point((e_1.longitude)::double precision,
 (e_1.latitude)::double precision), 4326), '1'::double precision))
         Filter: ((st_setsrid(st_point((e_1.longitude)::double precision,
 (e_1.latitude)::double precision), 4326) && st_expand(geom, '1'::double
 precision)) AND _st_dwithin(geom,
 st_setsrid(st_point((e_1.longitude)::double precision,
 (e_1.latitude)::double precision), 4326), '1'::double precision))
         ->  Bitmap Index Scan on idx_e_geom_gist
               Index Cond: (geom &&
 st_expand(st_setsrid(st_point((e_1.longitude)::double precision,
 (e_1.latitude)::double precision), 4326), '1'::double precision))

 }}}


 then changing:


 {{{
 CREATE OR REPLACE FUNCTION st_setsrid(
     geometry,
     integer)
   RETURNS geometry AS
 '$libdir/postgis-2.4', 'LWGEOM_set_srid'
   LANGUAGE c IMMUTABLE STRICT PARALLEL SAFE
   COST 100;
 }}}

 Changes the plan to this


 {{{
 Nested Loop
   Join Filter: st_dwithin(e.geom,
 st_setsrid(st_point((e_1.longitude)::double precision,
 (e_1.latitude)::double precision), 4326), '1'::double precision)
   ->  Seq Scan on e e_1
         Filter: ((longitude = '-1'::integer) AND (latitude =
 '-1'::integer))
   ->  Seq Scan on e

 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3675#comment:15>
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.