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

 OK, our plan fails when we hit an examination of the costs of the
 parameters to the function being inlined. Our high-cost ST_SetSRID kicks
 things out, so we don't get in-lined.

 https://github.com/postgres/postgres/blob/ae20b23a9e7029f31ee902da08a464d968319f56/src/backend/optimizer/util/clauses.c#L4581-L4584

 This seems to be generically true, as an example can be constructed in
 PgSQL native terms:

 {{{
 -- Create data table and index. Analyze.
 DROP TABLE IF EXISTS boxen;
 CREATE TABLE boxen AS
   SELECT row_number() OVER() As gid,
   box(point(x, y),point(x+1, y+1)) AS b, x, y
   FROM generate_series(-100,100) As y, generate_series(-100,100) As x;
 CREATE INDEX idx_b_geom_gist ON boxen USING gist(b);
 ANALYZE boxen;

 -- inlined function
 -- when set 'STRICT' it breaks index access
 -- 'IMMUTABLE' doesn't seem to bother it
 CREATE OR REPLACE FUNCTION good_box(box, box)
 RETURNS boolean
 AS 'SELECT $1 OPERATOR(&&) $2 AND length(lseg(point($1),point($2))) < 3'
 LANGUAGE 'sql';

 -- Start with a low cost circle()
 ALTER FUNCTION circle(point, double precision) COST 1;

 -- [A] Query plan hits index
 EXPLAIN SELECT gid
 FROM boxen
 WHERE good_box(
     boxen.b,
     box(circle(point(20.5, 20.5), 2))
     );

 -- [B] Query plan hits index
 EXPLAIN SELECT gid
 FROM boxen,
     (SELECT x, y FROM boxen WHERE x < 0 and y < 0) AS c
 WHERE good_box(
     boxen.b,
     box(circle(point(c.x, c.y), 2))
     );

 -- Increase cost of circle
 ALTER FUNCTION circle(point, double precision) COST 100;

 -- [B] Query plan does not hit index
 EXPLAIN SELECT gid
 FROM boxen,
     (SELECT x, y FROM boxen WHERE x < 0 and y < 0) AS c
 WHERE good_box(
     boxen.b,
     box(circle(point(c.x, c.y), 2))
     );
 }}}

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