upstream/postgresql
1
0
Fork 0
mirror of https://github.com/postgres/postgres.git synced 2026-05-28 04:35:45 -04:00
Code Issues Projects Releases Packages Wiki Activity Actions
postgresql/contrib/pg_plan_advice/expected/partitionwise.out
Robert Haas 5883ff30b0 Add pg_plan_advice contrib module. 
Provide a facility that (1) can be used to stabilize certain plan choices
so that the planner cannot reverse course without authorization and
(2) can be used by knowledgeable users to insist on plan choices contrary
to what the planner believes best. In both cases, terrible outcomes are
possible: users should think twice and perhaps three times before
constraining the planner's ability to do as it thinks best; nevertheless,
there are problems that are much more easily solved with these facilities
than without them.

This patch takes the approach of analyzing a finished plan to produce
textual output, which we call "plan advice", that describes key
decisions made during plan; if that plan advice is provided during
future planning cycles, it will force those key decisions to be made in
the same way.  Not all planner decisions can be controlled using advice;
for example, decisions about how to perform aggregation are currently
out of scope, as is choice of sort order. Plan advice can also be edited
by the user, or even written from scratch in simple cases, making it
possible to generate outcomes that the planner would not have produced.
Partial advice can be provided to control some planner outcomes but not
others.

Currently, plan advice is focused only on specific outcomes, such as
the choice to use a sequential scan for a particular relation, and not
on estimates that might contribute to those outcomes, such as a
possibly-incorrect selectivity estimate. While it would be useful to
users to be able to provide plan advice that affects selectivity
estimates or other aspects of costing, that is out of scope for this
commit.

Reviewed-by: Lukas Fittl <lukas@fittl.com>
Reviewed-by: Jakub Wartak <jakub.wartak@enterprisedb.com>
Reviewed-by: Greg Burd <greg@burd.me>
Reviewed-by: Jacob Champion <jacob.champion@enterprisedb.com>
Reviewed-by: Haibo Yan <tristan.yim@gmail.com>
Reviewed-by: Dian Fay <di@nmfay.com>
Reviewed-by: Ajay Pal <ajay.pal.k@gmail.com>
Reviewed-by: John Naylor <johncnaylorls@gmail.com>
Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com>
Discussion: http://postgr.es/m/CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com
2026-03-12 13:00:43 -04:00

426 lines
18 KiB
Text
Raw Permalink Blame History

LOAD 'pg_plan_advice';
SET max_parallel_workers_per_gather = 0;
SET enable_partitionwise_join = true;
CREATE TABLE pt1 (id integer primary key, dim1 text, val1 int)
PARTITION BY RANGE (id);
CREATE TABLE pt1a PARTITION OF pt1 FOR VALUES FROM (1) to (1001)
WITH (autovacuum_enabled = false);
CREATE TABLE pt1b PARTITION OF pt1 FOR VALUES FROM (1001) to (2001)
WITH (autovacuum_enabled = false);
CREATE TABLE pt1c PARTITION OF pt1 FOR VALUES FROM (2001) to (3001)
WITH (autovacuum_enabled = false);
INSERT INTO pt1 (id, dim1, val1)
SELECT g, 'some filler text ' || g, (g % 3) + 1
FROM generate_series(1,3000) g;
VACUUM ANALYZE pt1;
CREATE TABLE pt2 (id integer primary key, dim2 text, val2 int)
PARTITION BY RANGE (id);
CREATE TABLE pt2a PARTITION OF pt2 FOR VALUES FROM (1) to (1001)
WITH (autovacuum_enabled = false);
CREATE TABLE pt2b PARTITION OF pt2 FOR VALUES FROM (1001) to (2001)
WITH (autovacuum_enabled = false);
CREATE TABLE pt2c PARTITION OF pt2 FOR VALUES FROM (2001) to (3001)
WITH (autovacuum_enabled = false);
INSERT INTO pt2 (id, dim2, val2)
SELECT g, 'some other text ' || g, (g % 5) + 1
FROM generate_series(1,3000,2) g;
VACUUM ANALYZE pt2;
CREATE TABLE pt3 (id integer primary key, dim3 text, val3 int)
PARTITION BY RANGE (id);
CREATE TABLE pt3a PARTITION OF pt3 FOR VALUES FROM (1) to (1001)
WITH (autovacuum_enabled = false);
CREATE TABLE pt3b PARTITION OF pt3 FOR VALUES FROM (1001) to (2001)
WITH (autovacuum_enabled = false);
CREATE TABLE pt3c PARTITION OF pt3 FOR VALUES FROM (2001) to (3001)
WITH (autovacuum_enabled = false);
INSERT INTO pt3 (id, dim3, val3)
SELECT g, 'a third random text ' || g, (g % 7) + 1
FROM generate_series(1,3000,3) g;
VACUUM ANALYZE pt3;
CREATE TABLE ptmismatch (id integer primary key, dimm text, valm int)
PARTITION BY RANGE (id);
CREATE TABLE ptmismatcha PARTITION OF ptmismatch
FOR VALUES FROM (1) to (1501)
WITH (autovacuum_enabled = false);
CREATE TABLE ptmismatchb PARTITION OF ptmismatch
FOR VALUES FROM (1501) to (3001)
WITH (autovacuum_enabled = false);
INSERT INTO ptmismatch (id, dimm, valm)
SELECT g, 'yet another text ' || g, (g % 2) + 1
FROM generate_series(1,3000) g;
VACUUM ANALYZE ptmismatch;
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Append
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_1.id = pt3_1.id)
-> Seq Scan on pt2a pt2_1
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3a pt3_1
Filter: (val3 = 1)
-> Index Scan using pt1a_pkey on pt1a pt1_1
Index Cond: (id = pt2_1.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_2.id = pt3_2.id)
-> Seq Scan on pt2b pt2_2
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3b pt3_2
Filter: (val3 = 1)
-> Index Scan using pt1b_pkey on pt1b pt1_2
Index Cond: (id = pt2_2.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_3.id = pt3_3.id)
-> Seq Scan on pt2c pt2_3
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3c pt3_3
Filter: (val3 = 1)
-> Index Scan using pt1c_pkey on pt1c pt1_3
Index Cond: (id = pt2_3.id)
Filter: (val1 = 1)
Generated Plan Advice:
JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a pt1/public.pt1a)
JOIN_ORDER(pt2/public.pt2b pt3/public.pt3b pt1/public.pt1b)
JOIN_ORDER(pt2/public.pt2c pt3/public.pt3c pt1/public.pt1c)
NESTED_LOOP_PLAIN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
HASH_JOIN(pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
SEQ_SCAN(pt2/public.pt2a pt3/public.pt3a pt2/public.pt2b pt3/public.pt3b
pt2/public.pt2c pt3/public.pt3c)
INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
pt1/public.pt1c public.pt1c_pkey)
PARTITIONWISE((pt1 pt2 pt3))
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
(47 rows)
-- Suppress partitionwise join, or do it just partially.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE(pt1 pt2 pt3)';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Nested Loop
-> Hash Join
Hash Cond: (pt2.id = pt3.id)
-> Append
-> Seq Scan on pt2a pt2_1
Filter: (val2 = 1)
-> Seq Scan on pt2b pt2_2
Filter: (val2 = 1)
-> Seq Scan on pt2c pt2_3
Filter: (val2 = 1)
-> Hash
-> Append
-> Seq Scan on pt3a pt3_1
Filter: (val3 = 1)
-> Seq Scan on pt3b pt3_2
Filter: (val3 = 1)
-> Seq Scan on pt3c pt3_3
Filter: (val3 = 1)
-> Append
-> Index Scan using pt1a_pkey on pt1a pt1_1
Index Cond: (id = pt2.id)
Filter: (val1 = 1)
-> Index Scan using pt1b_pkey on pt1b pt1_2
Index Cond: (id = pt2.id)
Filter: (val1 = 1)
-> Index Scan using pt1c_pkey on pt1c pt1_3
Index Cond: (id = pt2.id)
Filter: (val1 = 1)
Supplied Plan Advice:
PARTITIONWISE(pt1) /* matched */
PARTITIONWISE(pt2) /* matched */
PARTITIONWISE(pt3) /* matched */
Generated Plan Advice:
JOIN_ORDER(pt2 pt3 pt1)
NESTED_LOOP_PLAIN(pt1)
HASH_JOIN(pt3)
SEQ_SCAN(pt2/public.pt2a pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a
pt3/public.pt3b pt3/public.pt3c)
INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
pt1/public.pt1c public.pt1c_pkey)
PARTITIONWISE(pt2 pt3 pt1)
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
(43 rows)
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 pt2) pt3)';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Hash Join
Hash Cond: (pt1.id = pt3.id)
-> Append
-> Hash Join
Hash Cond: (pt1_1.id = pt2_1.id)
-> Seq Scan on pt1a pt1_1
Filter: (val1 = 1)
-> Hash
-> Seq Scan on pt2a pt2_1
Filter: (val2 = 1)
-> Hash Join
Hash Cond: (pt1_2.id = pt2_2.id)
-> Seq Scan on pt1b pt1_2
Filter: (val1 = 1)
-> Hash
-> Seq Scan on pt2b pt2_2
Filter: (val2 = 1)
-> Hash Join
Hash Cond: (pt1_3.id = pt2_3.id)
-> Seq Scan on pt1c pt1_3
Filter: (val1 = 1)
-> Hash
-> Seq Scan on pt2c pt2_3
Filter: (val2 = 1)
-> Hash
-> Append
-> Seq Scan on pt3a pt3_1
Filter: (val3 = 1)
-> Seq Scan on pt3b pt3_2
Filter: (val3 = 1)
-> Seq Scan on pt3c pt3_3
Filter: (val3 = 1)
Supplied Plan Advice:
PARTITIONWISE((pt1 pt2)) /* matched */
PARTITIONWISE(pt3) /* matched */
Generated Plan Advice:
JOIN_ORDER(pt1/public.pt1a pt2/public.pt2a)
JOIN_ORDER(pt1/public.pt1b pt2/public.pt2b)
JOIN_ORDER(pt1/public.pt1c pt2/public.pt2c)
JOIN_ORDER({pt1 pt2} pt3)
HASH_JOIN(pt2/public.pt2a pt2/public.pt2b pt2/public.pt2c pt3)
SEQ_SCAN(pt1/public.pt1a pt2/public.pt2a pt1/public.pt1b pt2/public.pt2b
pt1/public.pt1c pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b
pt3/public.pt3c)
PARTITIONWISE((pt1 pt2) pt3)
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
(47 rows)
COMMIT;
-- Test conflicting advice.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 pt2) (pt1 pt3))';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Append
Disabled: true
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_1.id = pt3_1.id)
-> Seq Scan on pt2a pt2_1
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3a pt3_1
Filter: (val3 = 1)
-> Index Scan using pt1a_pkey on pt1a pt1_1
Index Cond: (id = pt2_1.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_2.id = pt3_2.id)
-> Seq Scan on pt2b pt2_2
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3b pt3_2
Filter: (val3 = 1)
-> Index Scan using pt1b_pkey on pt1b pt1_2
Index Cond: (id = pt2_2.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_3.id = pt3_3.id)
-> Seq Scan on pt2c pt2_3
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3c pt3_3
Filter: (val3 = 1)
-> Index Scan using pt1c_pkey on pt1c pt1_3
Index Cond: (id = pt2_3.id)
Filter: (val1 = 1)
Supplied Plan Advice:
PARTITIONWISE((pt1 pt2)) /* matched, conflicting, failed */
PARTITIONWISE((pt1 pt3)) /* matched, conflicting, failed */
Generated Plan Advice:
JOIN_ORDER(pt2/public.pt2a pt3/public.pt3a pt1/public.pt1a)
JOIN_ORDER(pt2/public.pt2b pt3/public.pt3b pt1/public.pt1b)
JOIN_ORDER(pt2/public.pt2c pt3/public.pt3c pt1/public.pt1c)
NESTED_LOOP_PLAIN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
HASH_JOIN(pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
SEQ_SCAN(pt2/public.pt2a pt3/public.pt3a pt2/public.pt2b pt3/public.pt3b
pt2/public.pt2c pt3/public.pt3c)
INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
pt1/public.pt1c public.pt1c_pkey)
PARTITIONWISE((pt1 pt2 pt3))
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
(51 rows)
COMMIT;
-- Can't force a partitionwise join with a mismatched table.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 ptmismatch))';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, ptmismatch WHERE pt1.id = ptmismatch.id;
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop
Disabled: true
-> Append
-> Seq Scan on pt1a pt1_1
-> Seq Scan on pt1b pt1_2
-> Seq Scan on pt1c pt1_3
-> Append
-> Index Scan using ptmismatcha_pkey on ptmismatcha ptmismatch_1
Index Cond: (id = pt1.id)
-> Index Scan using ptmismatchb_pkey on ptmismatchb ptmismatch_2
Index Cond: (id = pt1.id)
Supplied Plan Advice:
PARTITIONWISE((pt1 ptmismatch)) /* matched, failed */
Generated Plan Advice:
JOIN_ORDER(pt1 ptmismatch)
NESTED_LOOP_PLAIN(ptmismatch)
SEQ_SCAN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
INDEX_SCAN(ptmismatch/public.ptmismatcha public.ptmismatcha_pkey
ptmismatch/public.ptmismatchb public.ptmismatchb_pkey)
PARTITIONWISE(pt1 ptmismatch)
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c
ptmismatch/public.ptmismatcha ptmismatch/public.ptmismatchb)
(22 rows)
COMMIT;
-- Force join order for a particular branch of the partitionwise join with
-- and without mentioning the schema name.
BEGIN;
SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt3/public.pt3a pt2/public.pt2a pt1/public.pt1a)';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Append
-> Nested Loop
-> Hash Join
Hash Cond: (pt3_1.id = pt2_1.id)
-> Seq Scan on pt3a pt3_1
Filter: (val3 = 1)
-> Hash
-> Seq Scan on pt2a pt2_1
Filter: (val2 = 1)
-> Index Scan using pt1a_pkey on pt1a pt1_1
Index Cond: (id = pt2_1.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_2.id = pt3_2.id)
-> Seq Scan on pt2b pt2_2
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3b pt3_2
Filter: (val3 = 1)
-> Index Scan using pt1b_pkey on pt1b pt1_2
Index Cond: (id = pt2_2.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_3.id = pt3_3.id)
-> Seq Scan on pt2c pt2_3
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3c pt3_3
Filter: (val3 = 1)
-> Index Scan using pt1c_pkey on pt1c pt1_3
Index Cond: (id = pt2_3.id)
Filter: (val1 = 1)
Supplied Plan Advice:
JOIN_ORDER(pt3/public.pt3a pt2/public.pt2a pt1/public.pt1a) /* matched */
Generated Plan Advice:
JOIN_ORDER(pt3/public.pt3a pt2/public.pt2a pt1/public.pt1a)
JOIN_ORDER(pt2/public.pt2b pt3/public.pt3b pt1/public.pt1b)
JOIN_ORDER(pt2/public.pt2c pt3/public.pt3c pt1/public.pt1c)
NESTED_LOOP_PLAIN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
HASH_JOIN(pt2/public.pt2a pt3/public.pt3b pt3/public.pt3c)
SEQ_SCAN(pt3/public.pt3a pt2/public.pt2a pt2/public.pt2b pt3/public.pt3b
pt2/public.pt2c pt3/public.pt3c)
INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
pt1/public.pt1c public.pt1c_pkey)
PARTITIONWISE((pt1 pt2 pt3))
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
(49 rows)
SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(pt3/pt3a pt2/pt2a pt1/pt1a)';
EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT * FROM pt1, pt2, pt3 WHERE pt1.id = pt2.id AND pt2.id = pt3.id
AND val1 = 1 AND val2 = 1 AND val3 = 1;
QUERY PLAN
-------------------------------------------------------------------------------------
Append
-> Nested Loop
-> Hash Join
Hash Cond: (pt3_1.id = pt2_1.id)
-> Seq Scan on pt3a pt3_1
Filter: (val3 = 1)
-> Hash
-> Seq Scan on pt2a pt2_1
Filter: (val2 = 1)
-> Index Scan using pt1a_pkey on pt1a pt1_1
Index Cond: (id = pt2_1.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_2.id = pt3_2.id)
-> Seq Scan on pt2b pt2_2
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3b pt3_2
Filter: (val3 = 1)
-> Index Scan using pt1b_pkey on pt1b pt1_2
Index Cond: (id = pt2_2.id)
Filter: (val1 = 1)
-> Nested Loop
-> Hash Join
Hash Cond: (pt2_3.id = pt3_3.id)
-> Seq Scan on pt2c pt2_3
Filter: (val2 = 1)
-> Hash
-> Seq Scan on pt3c pt3_3
Filter: (val3 = 1)
-> Index Scan using pt1c_pkey on pt1c pt1_3
Index Cond: (id = pt2_3.id)
Filter: (val1 = 1)
Supplied Plan Advice:
JOIN_ORDER(pt3/pt3a pt2/pt2a pt1/pt1a) /* matched */
Generated Plan Advice:
JOIN_ORDER(pt3/public.pt3a pt2/public.pt2a pt1/public.pt1a)
JOIN_ORDER(pt2/public.pt2b pt3/public.pt3b pt1/public.pt1b)
JOIN_ORDER(pt2/public.pt2c pt3/public.pt3c pt1/public.pt1c)
NESTED_LOOP_PLAIN(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c)
HASH_JOIN(pt2/public.pt2a pt3/public.pt3b pt3/public.pt3c)
SEQ_SCAN(pt3/public.pt3a pt2/public.pt2a pt2/public.pt2b pt3/public.pt3b
pt2/public.pt2c pt3/public.pt3c)
INDEX_SCAN(pt1/public.pt1a public.pt1a_pkey pt1/public.pt1b public.pt1b_pkey
pt1/public.pt1c public.pt1c_pkey)
PARTITIONWISE((pt1 pt2 pt3))
NO_GATHER(pt1/public.pt1a pt1/public.pt1b pt1/public.pt1c pt2/public.pt2a
pt2/public.pt2b pt2/public.pt2c pt3/public.pt3a pt3/public.pt3b pt3/public.pt3c)
(49 rows)
COMMIT;
Reference in a new issue View git blame Copy permalink