[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 pramsey):

 Our demo SQL has gotten a little spread out. Here's a complete set of SQL
 that demonstrates the condition:
 {{{
 -- Create data table and index. Analyze.
 DROP TABLE IF EXISTS e;
 CREATE TABLE e AS
   SELECT row_number() OVER() As gid,
     ST_SetSRID( ST_Point(x,y) ,4326) As geom,
     x as longitude, y as latitude
   FROM generate_series(-90,90) As y, generate_series(-179,179) As x;
 CREATE INDEX idx_e_geom_gist ON e USING gist(geom);
 ANALYZE e;

 -- Start with a high cost ST_SetSRID
 ALTER FUNCTION ST_SetSRID(geometry, integer) COST 1;

 -- [A] Query plan hits
 EXPLAIN SELECT gid
 FROM  e
 WHERE   ST_DWithin(e.geom ,
 ST_SetSRID(ST_Point( 151.27544336, -33.87718472), 4326), 1);

 -- [B] Query plan hits index
 EXPLAIN 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);

 -- Set ST_SetSRID cost to a high value
 ALTER FUNCTION ST_SetSRID(geometry, integer) COST 100;

 -- [B] Query plan now misses index!!
 EXPLAIN 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);

 -- Can we fix it by changing the ST_DWithin function into
 -- undecorated (no strict, no immutable) version?
 CREATE OR REPLACE FUNCTION ST_DWithin(geom1 geometry, geom2 geometry,
 float8)
 RETURNS boolean
 AS 'SELECT $1 OPERATOR(&&) ST_Expand($2,$3) AND $2 OPERATOR(&&)
 ST_Expand($1,$3) AND _ST_DWithin($1, $2, $3)'
 LANGUAGE 'sql';

 -- [B] Query plan still misses index!!
 EXPLAIN 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);
 }}}

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