contrib/pg_plan_advice/expected/join_order.out - master - githubmirror/postgres

contrib/pg_plan_advice/expected/join_order.out
500 строк16 KB
Robert Haas
Add pg_plan_advice contrib module.
12 мар 2026, 20:00
12 мар 2026, 20:005883ff3
LOAD 'pg_plan_advice';
SET max_parallel_workers_per_gather = 0;
CREATE TABLE jo_dim1 (id integer primary key, dim1 text, val1 int)
	WITH (autovacuum_enabled = false);
INSERT INTO jo_dim1 (id, dim1, val1)
	SELECT g, 'some filler text ' || g, (g % 3) + 1
	  FROM generate_series(1,100) g;
VACUUM ANALYZE jo_dim1;
CREATE TABLE jo_dim2 (id integer primary key, dim2 text, val2 int)
	WITH (autovacuum_enabled = false);
INSERT INTO jo_dim2 (id, dim2, val2)
	SELECT g, 'some filler text ' || g, (g % 53) + 1
	  FROM generate_series(1,1000) g;
VACUUM ANALYZE jo_dim2;
CREATE TABLE jo_fact (
	id int primary key,
	dim1_id integer not null references jo_dim1 (id),
	dim2_id integer not null references jo_dim2 (id)
) WITH (autovacuum_enabled = false);
INSERT INTO jo_fact
	SELECT g, (g%100)+1, (g%100)+1 FROM generate_series(1,100000) g;
VACUUM ANALYZE jo_fact;
-- We expect to join to d2 first and then d1, since the condition on d2
-- is more selective.
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                QUERY PLAN                
------------------------------------------
 Hash Join
   Hash Cond: (f.dim1_id = d1.id)
   ->  Hash Join
         Hash Cond: (f.dim2_id = d2.id)
         ->  Seq Scan on jo_fact f
         ->  Hash
               ->  Seq Scan on jo_dim2 d2
                     Filter: (val2 = 1)
   ->  Hash
         ->  Seq Scan on jo_dim1 d1
               Filter: (val1 = 1)
 Generated Plan Advice:
   JOIN_ORDER(f d2 d1)
   HASH_JOIN(d2 d1)
   SEQ_SCAN(f d2 d1)
   NO_GATHER(f d1 d2)
(16 rows)

-- Force a few different join orders. Some of these are very inefficient,
-- but the planner considers them all viable.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'join_order(f d1 d2)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                QUERY PLAN                
------------------------------------------
 Hash Join
   Hash Cond: (f.dim2_id = d2.id)
   ->  Hash Join
         Hash Cond: (f.dim1_id = d1.id)
         ->  Seq Scan on jo_fact f
         ->  Hash
               ->  Seq Scan on jo_dim1 d1
                     Filter: (val1 = 1)
   ->  Hash
         ->  Seq Scan on jo_dim2 d2
               Filter: (val2 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(f d1 d2) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(f d1 d2)
   HASH_JOIN(d1 d2)
   SEQ_SCAN(f d1 d2)
   NO_GATHER(f d1 d2)
(18 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(f d2 d1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                QUERY PLAN                
------------------------------------------
 Hash Join
   Hash Cond: (f.dim1_id = d1.id)
   ->  Hash Join
         Hash Cond: (f.dim2_id = d2.id)
         ->  Seq Scan on jo_fact f
         ->  Hash
               ->  Seq Scan on jo_dim2 d2
                     Filter: (val2 = 1)
   ->  Hash
         ->  Seq Scan on jo_dim1 d1
               Filter: (val1 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(f d2 d1) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(f d2 d1)
   HASH_JOIN(d2 d1)
   SEQ_SCAN(f d2 d1)
   NO_GATHER(f d1 d2)
(18 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(d1 f d2)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
               QUERY PLAN                
-----------------------------------------
 Hash Join
   Hash Cond: (f.dim2_id = d2.id)
   ->  Hash Join
         Hash Cond: (d1.id = f.dim1_id)
         ->  Seq Scan on jo_dim1 d1
               Filter: (val1 = 1)
         ->  Hash
               ->  Seq Scan on jo_fact f
   ->  Hash
         ->  Seq Scan on jo_dim2 d2
               Filter: (val2 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(d1 f d2) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(d1 f d2)
   HASH_JOIN(f d2)
   SEQ_SCAN(d1 f d2)
   NO_GATHER(f d1 d2)
(18 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(f (d1 d2))';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((f.dim1_id = d1.id) AND (f.dim2_id = d2.id))
   ->  Seq Scan on jo_fact f
   ->  Hash
         ->  Nested Loop
               ->  Seq Scan on jo_dim1 d1
                     Filter: (val1 = 1)
               ->  Materialize
                     ->  Seq Scan on jo_dim2 d2
                           Filter: (val2 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(f (d1 d2)) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(f (d1 d2))
   NESTED_LOOP_MATERIALIZE(d2)
   HASH_JOIN((d1 d2))
   SEQ_SCAN(f d1 d2)
   NO_GATHER(f d1 d2)
(18 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(f {d1 d2})';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((f.dim1_id = d1.id) AND (f.dim2_id = d2.id))
   ->  Seq Scan on jo_fact f
   ->  Hash
         ->  Nested Loop
               ->  Seq Scan on jo_dim1 d1
                     Filter: (val1 = 1)
               ->  Materialize
                     ->  Seq Scan on jo_dim2 d2
                           Filter: (val2 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(f {d1 d2}) /* matched, failed */
 Generated Plan Advice:
   JOIN_ORDER(f (d1 d2))
   NESTED_LOOP_MATERIALIZE(d2)
   HASH_JOIN((d1 d2))
   SEQ_SCAN(f d1 d2)
   NO_GATHER(f d1 d2)
(18 rows)

COMMIT;
-- Force a join order by mentioning just a prefix of the join list.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'join_order(d2)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                   QUERY PLAN                   
------------------------------------------------
 Hash Join
   Hash Cond: (d2.id = f.dim2_id)
   ->  Seq Scan on jo_dim2 d2
         Filter: (val2 = 1)
   ->  Hash
         ->  Hash Join
               Hash Cond: (f.dim1_id = d1.id)
               ->  Seq Scan on jo_fact f
               ->  Hash
                     ->  Seq Scan on jo_dim1 d1
                           Filter: (val1 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(d2) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(d2 (f d1))
   HASH_JOIN(d1 (f d1))
   SEQ_SCAN(d2 f d1)
   NO_GATHER(f d1 d2)
(18 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(d2 d1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id))
   ->  Nested Loop
         ->  Seq Scan on jo_dim2 d2
               Filter: (val2 = 1)
         ->  Materialize
               ->  Seq Scan on jo_dim1 d1
                     Filter: (val1 = 1)
   ->  Hash
         ->  Seq Scan on jo_fact f
 Supplied Plan Advice:
   JOIN_ORDER(d2 d1) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(d2 d1 f)
   NESTED_LOOP_MATERIALIZE(d1)
   HASH_JOIN(f)
   SEQ_SCAN(d2 d1 f)
   NO_GATHER(f d1 d2)
(18 rows)

COMMIT;
-- jo_fact is not partitioned, but let's try pretending that it is and
-- verifying that the advice does not apply.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 d1 d2)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Disabled: true
   ->  Nested Loop
         Disabled: true
         ->  Seq Scan on jo_fact f
         ->  Index Scan using jo_dim1_pkey on jo_dim1 d1
               Index Cond: (id = f.dim1_id)
               Filter: (val1 = 1)
   ->  Index Scan using jo_dim2_pkey on jo_dim2 d2
         Index Cond: (id = f.dim2_id)
         Filter: (val2 = 1)
 Supplied Plan Advice:
   JOIN_ORDER(f/d1 d1 d2) /* partially matched */
 Generated Plan Advice:
   JOIN_ORDER(f d1 d2)
   NESTED_LOOP_PLAIN(d1 d2)
   SEQ_SCAN(f)
   INDEX_SCAN(d1 public.jo_dim1_pkey d2 public.jo_dim2_pkey)
   NO_GATHER(f d1 d2)
(19 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 (d1 d2))';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_fact f
	LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
	LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
	WHERE val1 = 1 AND val2 = 1;
                          QUERY PLAN                          
--------------------------------------------------------------
 Nested Loop
   Disabled: true
   Join Filter: ((d1.id = f.dim1_id) AND (d2.id = f.dim2_id))
   ->  Nested Loop
         ->  Seq Scan on jo_dim1 d1
               Filter: (val1 = 1)
         ->  Materialize
               ->  Seq Scan on jo_dim2 d2
                     Filter: (val2 = 1)
   ->  Seq Scan on jo_fact f
 Supplied Plan Advice:
   JOIN_ORDER(f/d1 (d1 d2)) /* partially matched */
 Generated Plan Advice:
   JOIN_ORDER(d1 d2 f)
   NESTED_LOOP_PLAIN(f)
   NESTED_LOOP_MATERIALIZE(d2)
   SEQ_SCAN(d1 d2 f)
   NO_GATHER(f d1 d2)
(18 rows)

COMMIT;
-- The unusual formulation of this query is intended to prevent the query
-- planner from reducing the FULL JOIN to some other join type, so that we
-- can test what happens with a join type that cannot be reordered.
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_dim1 d1
	INNER JOIN (jo_fact f FULL JOIN jo_dim2 d2 ON f.dim2_id + 0 = d2.id + 0)
	ON d1.id = f.dim1_id OR f.dim1_id IS NULL;
                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Join Filter: ((d1.id = f.dim1_id) OR (f.dim1_id IS NULL))
   ->  Merge Full Join
         Merge Cond: (((d2.id + 0)) = ((f.dim2_id + 0)))
         ->  Sort
               Sort Key: ((d2.id + 0))
               ->  Seq Scan on jo_dim2 d2
         ->  Sort
               Sort Key: ((f.dim2_id + 0))
               ->  Seq Scan on jo_fact f
   ->  Materialize
         ->  Seq Scan on jo_dim1 d1
 Generated Plan Advice:
   JOIN_ORDER(d2 f d1)
   MERGE_JOIN_PLAIN(f)
   NESTED_LOOP_MATERIALIZE(d1)
   SEQ_SCAN(d2 f d1)
   NO_GATHER(d1 f d2)
(18 rows)

-- We should not be able to force the planner to join f to d1 first, because
-- that is not a valid join order, but we should be able to force the planner
-- to make either d2 or f the driving table.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'join_order(f d1 d2)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_dim1 d1
	INNER JOIN (jo_fact f FULL JOIN jo_dim2 d2 ON f.dim2_id + 0 = d2.id + 0)
	ON d1.id = f.dim1_id OR f.dim1_id IS NULL;
                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Disabled: true
   Join Filter: ((d1.id = f.dim1_id) OR (f.dim1_id IS NULL))
   ->  Merge Full Join
         Disabled: true
         Merge Cond: (((d2.id + 0)) = ((f.dim2_id + 0)))
         ->  Sort
               Sort Key: ((d2.id + 0))
               ->  Seq Scan on jo_dim2 d2
         ->  Sort
               Sort Key: ((f.dim2_id + 0))
               ->  Seq Scan on jo_fact f
   ->  Seq Scan on jo_dim1 d1
 Supplied Plan Advice:
   JOIN_ORDER(f d1 d2) /* partially matched */
 Generated Plan Advice:
   JOIN_ORDER(d2 f d1)
   MERGE_JOIN_PLAIN(f)
   NESTED_LOOP_PLAIN(d1)
   SEQ_SCAN(d2 f d1)
   NO_GATHER(d1 f d2)
(21 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(f d2 d1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_dim1 d1
	INNER JOIN (jo_fact f FULL JOIN jo_dim2 d2 ON f.dim2_id + 0 = d2.id + 0)
	ON d1.id = f.dim1_id OR f.dim1_id IS NULL;
                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Join Filter: ((d1.id = f.dim1_id) OR (f.dim1_id IS NULL))
   ->  Merge Full Join
         Merge Cond: (((f.dim2_id + 0)) = ((d2.id + 0)))
         ->  Sort
               Sort Key: ((f.dim2_id + 0))
               ->  Seq Scan on jo_fact f
         ->  Sort
               Sort Key: ((d2.id + 0))
               ->  Seq Scan on jo_dim2 d2
   ->  Materialize
         ->  Seq Scan on jo_dim1 d1
 Supplied Plan Advice:
   JOIN_ORDER(f d2 d1) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(f d2 d1)
   MERGE_JOIN_PLAIN(d2)
   NESTED_LOOP_MATERIALIZE(d1)
   SEQ_SCAN(f d2 d1)
   NO_GATHER(d1 f d2)
(20 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(d2 f d1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_dim1 d1
	INNER JOIN (jo_fact f FULL JOIN jo_dim2 d2 ON f.dim2_id + 0 = d2.id + 0)
	ON d1.id = f.dim1_id OR f.dim1_id IS NULL;
                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Join Filter: ((d1.id = f.dim1_id) OR (f.dim1_id IS NULL))
   ->  Merge Full Join
         Merge Cond: (((d2.id + 0)) = ((f.dim2_id + 0)))
         ->  Sort
               Sort Key: ((d2.id + 0))
               ->  Seq Scan on jo_dim2 d2
         ->  Sort
               Sort Key: ((f.dim2_id + 0))
               ->  Seq Scan on jo_fact f
   ->  Materialize
         ->  Seq Scan on jo_dim1 d1
 Supplied Plan Advice:
   JOIN_ORDER(d2 f d1) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(d2 f d1)
   MERGE_JOIN_PLAIN(f)
   NESTED_LOOP_MATERIALIZE(d1)
   SEQ_SCAN(d2 f d1)
   NO_GATHER(d1 f d2)
(20 rows)

COMMIT;
-- Two incompatible join orders should conflict. In the second case,
-- the conflict is implicit: if d1 is on the inner side of a join of any
-- type, it cannot also be the driving table.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'join_order(f) join_order(d1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_dim1 d1
	INNER JOIN (jo_fact f FULL JOIN jo_dim2 d2 ON f.dim2_id + 0 = d2.id + 0)
	ON d1.id = f.dim1_id OR f.dim1_id IS NULL;
                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Join Filter: ((d1.id = f.dim1_id) OR (f.dim1_id IS NULL))
   ->  Merge Full Join
         Merge Cond: (((f.dim2_id + 0)) = ((d2.id + 0)))
         ->  Sort
               Sort Key: ((f.dim2_id + 0))
               ->  Seq Scan on jo_fact f
         ->  Sort
               Sort Key: ((d2.id + 0))
               ->  Seq Scan on jo_dim2 d2
   ->  Materialize
         ->  Seq Scan on jo_dim1 d1
 Supplied Plan Advice:
   JOIN_ORDER(f) /* matched, conflicting */
   JOIN_ORDER(d1) /* matched, conflicting, failed */
 Generated Plan Advice:
   JOIN_ORDER(f d2 d1)
   MERGE_JOIN_PLAIN(d2)
   NESTED_LOOP_MATERIALIZE(d1)
   SEQ_SCAN(f d2 d1)
   NO_GATHER(d1 f d2)
(21 rows)

SET LOCAL pg_plan_advice.advice = 'join_order(d1) hash_join(d1)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM jo_dim1 d1
	INNER JOIN (jo_fact f FULL JOIN jo_dim2 d2 ON f.dim2_id + 0 = d2.id + 0)
	ON d1.id = f.dim1_id OR f.dim1_id IS NULL;
                          QUERY PLAN                           
---------------------------------------------------------------
 Nested Loop
   Join Filter: ((d1.id = f.dim1_id) OR (f.dim1_id IS NULL))
   ->  Seq Scan on jo_dim1 d1
   ->  Materialize
         ->  Merge Full Join
               Merge Cond: (((d2.id + 0)) = ((f.dim2_id + 0)))
               ->  Sort
                     Sort Key: ((d2.id + 0))
                     ->  Seq Scan on jo_dim2 d2
               ->  Sort
                     Sort Key: ((f.dim2_id + 0))
                     ->  Seq Scan on jo_fact f
 Supplied Plan Advice:
   JOIN_ORDER(d1) /* matched, conflicting */
   HASH_JOIN(d1) /* matched, conflicting, failed */
 Generated Plan Advice:
   JOIN_ORDER(d1 (d2 f))
   MERGE_JOIN_PLAIN(f)
   NESTED_LOOP_MATERIALIZE((f d2))
   SEQ_SCAN(d1 d2 f)
   NO_GATHER(d1 f d2)
(21 rows)

COMMIT;