[postgis-tickets] [PostGIS] #4269: Inconsistent result for ST_Intersects depending on the plan

[PostGIS]

#4269: Inconsistent result for ST_Intersects depending on the plan
-------------------------+---------------------------
  Reporter:  Algunenano  |      Owner:  pramsey
      Type:  defect      |     Status:  new
  Priority:  critical    |  Milestone:  PostGIS 2.4.7
 Component:  postgis     |    Version:  2.5.x
Resolution:              |   Keywords:
-------------------------+---------------------------

Comment (by Algunenano):

 Plan with `hashjoin` on:
 {{{
                                                            QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------------------
  Subquery Scan on _a  (cost=0.17..0.51 rows=1 width=9) (actual
 time=0.610..2.006 rows=6 loops=1)
    ->  Hash Join  (cost=0.17..0.25 rows=1 width=72) (actual
 time=0.135..0.191 rows=6 loops=1)
          Hash Cond: (b.blockid = a.blockid)
          Join Filter: (a.cid <> b.cid)
          Rows Removed by Join Filter: 3
          CTE data
            ->  Append  (cost=0.00..0.08 rows=3 width=44) (actual
 time=0.008..0.021 rows=3 loops=1)
                  ->  Result  (cost=0.00..0.01 rows=1 width=44) (actual
 time=0.006..0.007 rows=1 loops=1)
                  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02
 rows=1 width=44) (actual time=0.005..0.006 rows=1 loops=1)
                        ->  Result  (cost=0.00..0.01 rows=1 width=40)
 (actual time=0.002..0.003 rows=1 loops=1)
                  ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02
 rows=1 width=44) (actual time=0.003..0.004 rows=1 loops=1)
                        ->  Result  (cost=0.00..0.01 rows=1 width=40)
 (actual time=0.001..0.002 rows=1 loops=1)
          ->  CTE Scan on data b  (cost=0.00..0.06 rows=3 width=44) (actual
 time=0.023..0.032 rows=3 loops=1)
          ->  Hash  (cost=0.08..0.08 rows=1 width=44) (actual
 time=0.071..0.071 rows=3 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                ->  CTE Scan on data a  (cost=0.00..0.08 rows=1 width=44)
 (actual time=0.012..0.040 rows=3 loops=1)
                      Filter: ((blockid)::integer = 720)
  Planning Time: 2.196 ms
  Execution Time: 2.412 ms
 (19 rows)
 }}}

 Plan with hashjoin off:
 {{{
 crap=# set enable_hashjoin=off;
 SET
 crap=# explain analyze Select ST_Intersects(ag, bg), acid, bcid FROM
 (
 WITH data AS (
 SELECT 1 as cid,720::bigint as blockid, ST_GeomFromText('POLYGON Z
 ((122427.759 489209.498 14.5066901703871,122395.957 489222.943
 14.3997094723791,122399.649 489231.676 4.98556790825181,122431.452
 489218.231 5.09216481886026,122427.759 489209.498 14.5066901703871))')
 geom
 UNION ALL
 SELECT 2, 720, ST_GeomFromText('POLYGON Z ((122427.027 489216.053
 5.97863674861823,122419.873 489219.077 6.03923989893278,122421.6
 489223.163 1.83027232084998,122428.754 489220.138
 1.77054008712955,122427.027 489216.053 5.97863674861823))')
 UNION ALL
 SELECT 3, 720, ST_GeomFromText('POLYGON Z ((122422.267 489220.741
 0,122424.396 489219.847 0,122424.396 489219.847 30,122422.267 489220.741
 30,122422.267 489220.741 0))')
 )
 SELECT
 a.cid as acid, b.cid as bcid,
 a.geom as ag, b.geom as bg
 FROM data a, data b
 WHERE a.cid != b.cid
 AND a.blockid = b.blockid
 AND a.blockid::int = 720
 ) _A;
                                                            QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------------------
  Subquery Scan on _a  (cost=0.08..0.52 rows=1 width=9) (actual
 time=0.726..2.140 rows=6 loops=1)
    ->  Nested Loop  (cost=0.08..0.26 rows=1 width=72) (actual
 time=0.058..0.139 rows=6 loops=1)
          Join Filter: ((a.cid <> b.cid) AND (a.blockid = b.blockid))
          Rows Removed by Join Filter: 3
          CTE data
            ->  Append  (cost=0.00..0.08 rows=3 width=44) (actual
 time=0.008..0.026 rows=3 loops=1)
                  ->  Result  (cost=0.00..0.01 rows=1 width=44) (actual
 time=0.006..0.007 rows=1 loops=1)
                  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02
 rows=1 width=44) (actual time=0.005..0.006 rows=1 loops=1)
                        ->  Result  (cost=0.00..0.01 rows=1 width=40)
 (actual time=0.002..0.003 rows=1 loops=1)
                  ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02
 rows=1 width=44) (actual time=0.005..0.007 rows=1 loops=1)
                        ->  Result  (cost=0.00..0.01 rows=1 width=40)
 (actual time=0.003..0.004 rows=1 loops=1)
          ->  CTE Scan on data a  (cost=0.00..0.08 rows=1 width=44) (actual
 time=0.029..0.039 rows=3 loops=1)
                Filter: ((blockid)::integer = 720)
          ->  CTE Scan on data b  (cost=0.00..0.06 rows=3 width=44) (actual
 time=0.002..0.017 rows=3 loops=3)
  Planning Time: 2.194 ms
  Execution Time: 2.492 ms
 (16 rows)
 }}}

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