DROP TABLE IF EXISTS t1,t2,t3,t4 CASCADE;

CREATE TEMP TABLE t1 (x numeric);
CREATE TEMP TABLE t2 (x numeric);
CREATE TEMP TABLE t3 (x numeric);
CREATE TEMP TABLE t4 (x numeric);

INSERT INTO t1 (x) (SELECT (z % 11111)+1 FROM generate_series(1,1E6) AS z);
INSERT INTO t2 (x) (SELECT (z % 11111) FROM generate_series(1,1E3) AS z);
INSERT INTO t3 (x) (SELECT (z % 11111)+1 FROM generate_series(1,1E3) AS z);
INSERT INTO t4 (x) (SELECT z FROM generate_series(1,1E0) AS z);

VACUUM ANALYZE t1,t2,t3,t4;

SET from_collapse_limit = 4;
SET join_collapse_limit = 4;

EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF)
SELECT * FROM t1 JOIN t2
  ON (EXISTS (SELECT t3.x FROM t3 WHERE t2.x=t3.x))
  JOIN t4 ON (t1.x=t4.x);

SET from_collapse_limit = 3;
SET join_collapse_limit = 3;

EXPLAIN (ANALYZE, COSTS ON, BUFFERS OFF)
SELECT * FROM t1 JOIN t2
  ON (EXISTS (SELECT t3.x FROM t3 WHERE t2.x=t3.x))
  JOIN t4 ON (t1.x=t4.x);

/*
 Hash Join  (cost=46.02..19358.39 rows=90000 width=15) (actual time=4.497..346.555 rows=89910.00 loops=1)
   Hash Cond: (t3.x = t2.x)
   ->  Nested Loop  (cost=18.52..19329.65 rows=90 width=15) (actual time=4.167..305.242 rows=90000.00 loops=1)
         ->  HashAggregate  (cost=17.50..27.50 rows=1000 width=5) (actual time=0.515..0.666 rows=1000.00 loops=1)
               Group Key: t3.x
               Batches: 1  Memory Usage: 121kB
               ->  Seq Scan on t3  (cost=0.00..15.00 rows=1000 width=5) (actual time=0.003..0.062 rows=1000.00 loops=1)
         ->  Materialize  (cost=1.02..18177.37 rows=90 width=10) (actual time=0.004..0.291 rows=90.00 loops=1000)
               Storage: Memory  Maximum Storage: 21kB
               ->  Hash Join  (cost=1.02..18176.92 rows=90 width=10) (actual time=3.648..287.408 rows=90.00 loops=1)
                     Hash Cond: (t1.x = t4.x)
                     ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=5) (actual time=0.016..82.522 rows=1000000.00 loops=1)
                     ->  Hash  (cost=1.01..1.01 rows=1 width=5) (actual time=0.003..0.003 rows=1.00 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                           ->  Seq Scan on t4  (cost=0.00..1.01 rows=1 width=5) (actual time=0.002..0.002 rows=1.00 loops=1)
   ->  Hash  (cost=15.00..15.00 rows=1000 width=5) (actual time=0.325..0.325 rows=1000.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 45kB
         ->  Seq Scan on t2  (cost=0.00..15.00 rows=1000 width=5) (actual time=0.004..0.070 rows=1000.00 loops=1)
 Planning Time: 0.471 ms
 Execution Time: 348.556 ms

 Hash Join  (cost=28.52..16265384.78 rows=90001 width=15) (actual time=3737.688..336906.897 rows=89910.00 loops=1)
   Hash Cond: (t1.x = t4.x)
   ->  Nested Loop  (cost=27.50..12514483.75 rows=1000000000 width=10) (actual time=0.405..173948.870 rows=999000000.00 loops=1)
         ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=5) (actual time=0.022..103.805 rows=1000000.00 loops=1)
         ->  Materialize  (cost=27.50..61.25 rows=1000 width=5) (actual time=0.000..0.038 rows=999.00 loops=1000000)
               Storage: Memory  Maximum Storage: 56kB
               ->  Hash Semi Join  (cost=27.50..56.25 rows=1000 width=5) (actual time=0.377..0.947 rows=999.00 loops=1)
                     Hash Cond: (t2.x = t3.x)
                     ->  Seq Scan on t2  (cost=0.00..15.00 rows=1000 width=5) (actual time=0.022..0.105 rows=1000.00 loops=1)
                     ->  Hash  (cost=15.00..15.00 rows=1000 width=5) (actual time=0.350..0.350 rows=1000.00 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 45kB
                           ->  Seq Scan on t3  (cost=0.00..15.00 rows=1000 width=5) (actual time=0.019..0.095 rows=1000.00 loops=1)
   ->  Hash  (cost=1.01..1.01 rows=1 width=5) (actual time=0.044..0.044 rows=1.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on t4  (cost=0.00..1.01 rows=1 width=5) (actual time=0.037..0.038 rows=1.00 loops=1)
 Planning Time: 0.443 ms
 Execution Time: 336909.850 ms
*/

-- SubLinks pull-up disabled:

/*
 Nested Loop  (cost=1.02..36254.65 rows=45000 width=15) (actual time=3.645..281.419 rows=89910.00 loops=1)
   ->  Seq Scan on t2  (cost=0.00..17515.00 rows=500 width=5) (actual time=0.411..1.098 rows=999.00 loops=1)
         Filter: (ANY (x = (hashed SubPlan exists_2).col1))
         Rows Removed by Filter: 1
         SubPlan exists_2
           ->  Seq Scan on t3  (cost=0.00..15.00 rows=1000 width=5) (actual time=0.002..0.054 rows=1000.00 loops=1)
   ->  Materialize  (cost=1.02..18177.37 rows=90 width=10) (actual time=0.003..0.270 rows=90.00 loops=999)
         Storage: Memory  Maximum Storage: 21kB
         ->  Hash Join  (cost=1.02..18176.92 rows=90 width=10) (actual time=3.230..267.362 rows=90.00 loops=1)
               Hash Cond: (t1.x = t4.x)
               ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=5) (actual time=0.015..76.879 rows=1000000.00 loops=1)
               ->  Hash  (cost=1.01..1.01 rows=1 width=5) (actual time=0.003..0.004 rows=1.00 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on t4  (cost=0.00..1.01 rows=1 width=5) (actual time=0.001..0.002 rows=1.00 loops=1)
 Planning Time: 0.646 ms
 Execution Time: 283.214 ms
(16 rows)

SET
SET
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1.02..36254.65 rows=45000 width=15) (actual time=3.897..278.805 rows=89910.00 loops=1)
   ->  Seq Scan on t2  (cost=0.00..17515.00 rows=500 width=5) (actual time=0.428..1.092 rows=999.00 loops=1)
         Filter: (ANY (x = (hashed SubPlan exists_2).col1))
         Rows Removed by Filter: 1
         SubPlan exists_2
           ->  Seq Scan on t3  (cost=0.00..15.00 rows=1000 width=5) (actual time=0.016..0.075 rows=1000.00 loops=1)
   ->  Materialize  (cost=1.02..18177.37 rows=90 width=10) (actual time=0.004..0.268 rows=90.00 loops=999)
         Storage: Memory  Maximum Storage: 21kB
         ->  Hash Join  (cost=1.02..18176.92 rows=90 width=10) (actual time=3.466..264.849 rows=90.00 loops=1)
               Hash Cond: (t1.x = t4.x)
               ->  Seq Scan on t1  (cost=0.00..14425.00 rows=1000000 width=5) (actual time=0.014..76.247 rows=1000000.00 loops=1)
               ->  Hash  (cost=1.01..1.01 rows=1 width=5) (actual time=0.007..0.008 rows=1.00 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Seq Scan on t4  (cost=0.00..1.01 rows=1 width=5) (actual time=0.006..0.006 rows=1.00 loops=1)
 Planning Time: 0.232 ms
 Execution Time: 280.580 ms
*/