trivial-example.sql

DROP TABLE IF EXISTS t,tn;

SET max_parallel_workers_per_gather = 0;
SET join_collapse_limit = 1;

CREATE TEMP TABLE t (x integer);
INSERT INTO t SELECT 1 FROM generate_series(1,1E4) AS x;
CREATE TEMP TABLE tn (x integer);
INSERT INTO tn SELECT 2 FROM generate_series(1,1E4) AS x;
CREATE INDEX ON tn (x);

EXPLAIN (COSTS OFF)
SELECT * FROM t t0 LEFT JOIN t t1 LEFT JOIN t t2 ON (t1.x=t2.x) ON (t0.x=t1.x)
WHERE EXISTS (SELECT 1 FROM tn WHERE tn.x = t0.x);

EXPLAIN (COSTS OFF)
SELECT * FROM t t0 LEFT JOIN t t1  LEFT JOIN t t2 ON (t1.x=t2.x) ON (t0.x=t1.x)
WHERE EXISTS (SELECT 1 FROM tn WHERE tn.x = t0.x OFFSET 0);

/*
 Nested Loop Left Join
   Join Filter: (t0.x = t1.x)
   ->  Hash Join
         Hash Cond: (t0.x = tn.x)
         ->  Seq Scan on t t0
         ->  Hash
               ->  HashAggregate
                     Group Key: tn.x
                     ->  Seq Scan on tn
   ->  Hash Left Join
         Hash Cond: (t1.x = t2.x)
         ->  Seq Scan on t t1
         ->  Hash
               ->  Seq Scan on t t2

 Hash Right Join
   Hash Cond: (t1.x = t0.x)
   ->  Hash Left Join
         Hash Cond: (t1.x = t2.x)
         ->  Seq Scan on t t1
         ->  Hash
               ->  Seq Scan on t t2
   ->  Hash
         ->  Seq Scan on t t0
               Filter: EXISTS(SubPlan 1)
               SubPlan 1
                 ->  Seq Scan on tn
                       Filter: (x = t0.x)
*/