repro.sql

application/sql

Download

CREATE TABLE mem_semi_inner_a (x int);
CREATE TABLE mem_semi_inner_b (x int, y int);
CREATE TABLE mem_semi_inner_c (x int, y int);
INSERT INTO mem_semi_inner_a (x)
  (SELECT value%2 FROM generate_series(1,10) AS value);
INSERT INTO mem_semi_inner_b (x,y)
  (SELECT value%5, -value%5-1 FROM generate_series(1,10) AS value);
INSERT INTO mem_semi_inner_c (x,y)
  (SELECT value%50, value FROM generate_series(1,100) AS value);
CREATE INDEX ON mem_semi_inner_b(x);
CREATE INDEX ON mem_semi_inner_c(x);
VACUUM ANALYZE mem_semi_inner_a,mem_semi_inner_b,mem_semi_inner_c;

-- Force NestLoop and IndexScan. Hope, the Memoize node win the cost
-- competition on the inner c table scan.
SET enable_hashjoin = 'off';
SET enable_mergejoin = 'off';
SET enable_seqscan = 'off';

-- The test needs the Memoize node over the "Scan c" operator.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT a.x, b.x FROM mem_semi_inner_a a
  LEFT JOIN mem_semi_inner_b b ON (a.x=b.x)
WHERE NOT EXISTS (
  SELECT 1 FROM mem_semi_inner_c c WHERE c.x=a.x AND c.y=b.y);

-- The test needs the Memoize node over the "Scan c" operator.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT a.x, b.x FROM mem_semi_inner_a a
  LEFT JOIN mem_semi_inner_b b ON (a.x=b.x)
WHERE EXISTS (
  SELECT 1 FROM mem_semi_inner_c c WHERE c.x=a.x AND c.y=b.y);
DROP TABLE mem_semi_inner_a,mem_semi_inner_b,mem_semi_inner_c;