reproduction.sql

DROP TABLE IF EXISTS t1,t2,t3 CASCADE;
CREATE TABLE t1 (x numeric, y numeric, z numeric);
INSERT INTO t1 (x,y,z) SELECT gs % 100, gs, gs % 100
 FROM generate_series (1,1E5) AS gs;
CREATE INDEX ON t1(x);
CREATE TABLE t2 (x numeric, y numeric, z numeric);
INSERT INTO t2 (x,y,z) SELECT gs, gs % 1000, gs % 100
  FROM generate_series (1,10000) AS gs;
CREATE INDEX ON t2(y);
CREATE TABLE t3 (x numeric, y numeric);
INSERT INTO t3 (x,y) SELECT gs%2, gs%3 FROM generate_series (1,10) AS gs;
VACUUM ANALYZE t1,t2,t3;
SET enable_mergejoin = 'off';
SET enable_hashjoin = 'off';
SET enable_material = 'off';
SET enable_seqscan = 'off';
SET enable_bitmapscan = 'off';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY OFF)
SELECT * FROM t3 LEFT JOIN LATERAL (
 SELECT t2.x FROM t2 WHERE NOT EXISTS (
   SELECT 1 FROM t1 WHERE t1.x = t3.x AND t1.z=t2.z) AND
   t2.y = t3.y) ON true;
/*
-- I see:
 Nested Loop Left Join  (cost=0.71..22278.56 rows=10 width=13) (actual rows=73.00 loops=1)
   ->  Seq Scan on t3  (cost=0.00..1.10 rows=10 width=8) (actual rows=10.00 loops=1)
         Disabled: true
   ->  Memoize  (cost=0.71..6020.88 rows=1 width=5) (actual rows=7.00 loops=10)
         Cache Key: t3.x, t3.y
         Cache Mode: binary
         Hits: 4  Misses: 6  Evictions: 0  Overflows: 0  Memory Usage: 2kB
         ->  Nested Loop Anti Join  (cost=0.70..6020.87 rows=1 width=5) (actual rows=6.67 loops=6)
               Join Filter: (t1.z = t2.z)
               Rows Removed by Join Filter: 6667
               ->  Index Scan using t2_y_idx on t2  (cost=0.29..44.10 rows=10 width=9) (actual rows=10.00 loops=6)
                     Index Cond: (y = t3.y)
               ->  Index Scan using t1_x_idx on t1  (cost=0.42..2257.26 rows=1000 width=4) (actual rows=667.00 loops=60)
                     Index Cond: (x = t3.x)
*/