demo.sql

application/sql

Download

DROP TABLE IF EXISTS t1,t2 CASCADE;
SET enable_mergejoin = 'off';
SET enable_hashjoin = 'off';
SET enable_seqscan = 'off';
SET max_parallel_workers_per_gather = 0;

CREATE TABLE t1 (x integer, y numeric DEFAULT 0);
INSERT INTO t1 (x) (SELECT gs % 2 FROM generate_series(1,1E6) AS gs);

CREATE TABLE t2 (x integer, y numeric DEFAULT 0);
INSERT INTO t2 (x) (SELECT gs FROM generate_series(1,1E5) AS gs);
CREATE INDEX ON t2(x);
VACUUM ANALYZE t1,t2;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY ON)
SELECT * FROM t1 LEFT JOIN (
  SELECT t2.* FROM t2 JOIN (VALUES (1,1), (2,2)) AS q2(x,y) ON (t2.y=q2.y)) q
ON (t1.x = q.x);

/*
 Nested Loop Left Join (actual rows=1000000.00 loops=1)
   ->  Seq Scan on t1 (actual rows=1000000.00 loops=1)
         Disabled: true
   ->  Nested Loop (actual rows=0.00 loops=1000000)
         Join Filter: (t2.y = ("*VALUES*".column2)::numeric)
         Rows Removed by Join Filter: 1
         ->  Index Scan using t2_x_idx on t2 (actual rows=0.50 loops=1000000)
               Index Cond: (x = t1.x)
         ->  Values Scan on "*VALUES*" (actual rows=2.00 loops=500000)
 Planning Time: 0.311 ms
 Execution Time: 7217.024 ms
 */