mirror of
https://github.com/postgres/postgres.git
synced 2026-10-23 13:29:09 +03:00
38475f23c8
Commit0d4391b265taught the planner to store information about the RTIs of SubqueryScan, Append, and MergeAppend node elided from the plan using a new ElidedNode data structure; an hour later,7358abcc60taught to save the RTIs of an Append node into another Append node using a new child_append_relid_sets field. Regrettably, despite the fact that both commits were written by me and the fact that both were part of the same patch set, the latter commit failed to deal with the case where an Append node first acquires child_append_relid_sets != NIL and then gets elided. I believe that I thought that was impossible, but the included test case shows that it isn't. The proper fix involves updating the definition of ElidedNode to carry child_append_relid_sets, so this commit does that, breaking ABI compatibility. That's fine for the master branch, but it's a bit unfortunate to be doing do that so close to the planned ship date for v19. However, shipping v19 without fixing this appears (at least to me) to be even more unfortunate. Backpatch-through: 19 Discussion: http://postgr.es/m/CA+TgmoYYHC4i-Gpzc9-JYHqih6pOK+6LTYFiNR-PioXG1nKkOQ@mail.gmail.com
130 lines
5.0 KiB
PL/PgSQL
130 lines
5.0 KiB
PL/PgSQL
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;
|
|
|
|
CREATE TABLE mllpt (a int not null, b int not null)
|
|
PARTITION BY LIST (a);
|
|
CREATE TABLE mllpt_a1 PARTITION OF mllpt FOR VALUES IN (1)
|
|
PARTITION BY LIST (b);
|
|
CREATE TABLE mllpt_a1_b1 PARTITION OF mllpt_a1 FOR VALUES IN (1)
|
|
WITH (autovacuum_enabled = false);
|
|
CREATE TABLE mllpt_a1_b2 PARTITION OF mllpt_a1 FOR VALUES IN (2)
|
|
WITH (autovacuum_enabled = false);
|
|
CREATE TABLE mllpt_a1_b3 PARTITION OF mllpt_a1 FOR VALUES IN (3)
|
|
WITH (autovacuum_enabled = false);
|
|
CREATE TABLE mllpt_a2 PARTITION OF mllpt FOR VALUES IN (2)
|
|
PARTITION BY LIST (b);
|
|
CREATE TABLE mllpt_a2_b1 PARTITION OF mllpt_a2 FOR VALUES IN (1)
|
|
WITH (autovacuum_enabled = false);
|
|
CREATE TABLE mllpt_a2_b2 PARTITION OF mllpt_a2 FOR VALUES IN (2)
|
|
WITH (autovacuum_enabled = false);
|
|
CREATE TABLE mllpt_a2_b3 PARTITION OF mllpt_a2 FOR VALUES IN (3)
|
|
WITH (autovacuum_enabled = false);
|
|
|
|
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;
|
|
|
|
-- 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;
|
|
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;
|
|
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;
|
|
COMMIT;
|
|
|
|
-- Test use of join order for the partitionwise join case.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'PARTITIONWISE((pt1 pt2)) JOIN_ORDER({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;
|
|
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;
|
|
COMMIT;
|
|
|
|
-- Force join order for a particular branch of the partitionwise join.
|
|
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;
|
|
COMMIT;
|
|
|
|
-- We should get PARTITIONWISE advice for all unpruned partition tables.
|
|
EXPLAIN (PLAN_ADVICE, COSTS OFF)
|
|
SELECT * FROM mllpt WHERE a = 1 UNION ALL SELECT * FROM mllpt;
|
|
|
|
-- Same, but prune down to a single leaf so the Append is elided.
|
|
EXPLAIN (PLAN_ADVICE, COSTS OFF)
|
|
SELECT * FROM mllpt WHERE a = 1 AND b = 1;
|