mirror of
https://github.com/postgres/postgres.git
synced 2026-10-23 13:29:09 +03:00
f71a9969d5
When a table is partitioned, the previous code did not emit NO_GATHER advice for the parent table, on the theory that doing this for the child tables was good enough. However, it isn't. I believe what happened here is that, during development, I was planning to enforce NO_GATHER by clearing the consider_parallel flag for the children, which would have been good enough to ensure that parallelism could not be used at any higher level of the plan tree. Eventually, I switched course, but failed to realize that this code needed to be updated as a result. Do that, and add a regression test. Backpatch-through: 19 Discussion: http://postgr.es/m/CA+TgmoYYHC4i-Gpzc9-JYHqih6pOK+6LTYFiNR-PioXG1nKkOQ@mail.gmail.com
113 lines
4.4 KiB
PL/PgSQL
113 lines
4.4 KiB
PL/PgSQL
LOAD 'pg_plan_advice';
|
|
SET max_parallel_workers_per_gather = 1;
|
|
SET parallel_setup_cost = 0;
|
|
SET parallel_tuple_cost = 0;
|
|
SET min_parallel_table_scan_size = 0;
|
|
SET debug_parallel_query = off;
|
|
|
|
CREATE TABLE gt_dim (id serial primary key, dim text)
|
|
WITH (autovacuum_enabled = false);
|
|
INSERT INTO gt_dim (dim) SELECT random()::text FROM generate_series(1,100) g;
|
|
VACUUM ANALYZE gt_dim;
|
|
|
|
CREATE TABLE gt_fact (
|
|
id int not null,
|
|
dim_id integer not null references gt_dim (id)
|
|
) WITH (autovacuum_enabled = false);
|
|
INSERT INTO gt_fact
|
|
SELECT g, (g%3)+1 FROM generate_series(1,100000) g;
|
|
VACUUM ANALYZE gt_fact;
|
|
|
|
CREATE TABLE gt_part (id int not null, val int not null)
|
|
PARTITION BY RANGE (id);
|
|
CREATE TABLE gt_part1 PARTITION OF gt_part FOR VALUES FROM (0) TO (1000)
|
|
WITH (autovacuum_enabled = false);
|
|
CREATE TABLE gt_part2 PARTITION OF gt_part FOR VALUES FROM (1000) TO (2000)
|
|
WITH (autovacuum_enabled = false);
|
|
INSERT INTO gt_part SELECT g, g FROM generate_series(0, 1999) g;
|
|
VACUUM ANALYZE gt_part;
|
|
|
|
-- By default, we expect Gather Merge with a parallel hash join.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
|
|
-- Force Gather or Gather Merge of both relations together.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'gather_merge((f d))';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather((f d))';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
COMMIT;
|
|
|
|
-- Force a separate Gather or Gather Merge operation for each relation.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'gather_merge(f d)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather(f d)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather((d d/d.d))';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
COMMIT;
|
|
|
|
-- Force a Gather or Gather Merge on one relation but no parallelism on other.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'gather_merge(f) no_gather(d)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather_merge(d) no_gather(f)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather(f) no_gather(d)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather(d) no_gather(f)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
COMMIT;
|
|
|
|
-- Force no Gather or Gather Merge use at all.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'no_gather(f d)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
COMMIT;
|
|
|
|
-- Can't force Gather Merge without the ORDER BY clause, but just Gather is OK.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'gather_merge((f d))';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id;
|
|
SET LOCAL pg_plan_advice.advice = 'gather((f d))';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id;
|
|
COMMIT;
|
|
|
|
-- Test conflicting advice.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'gather((f d)) no_gather(f)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id ORDER BY d.id;
|
|
COMMIT;
|
|
|
|
-- Test interaction of NO_GATHER with partitioned tables. By default, we
|
|
-- expect Gather over a Parallel Append, but no_gather(gt_part) should
|
|
-- suppress it, even if we prune down to a single partition such that the
|
|
-- Append itself doesn't appear in the plan. Previously, we had a bug where
|
|
-- NO_GATHER for the parent was omitted from the generated output even when
|
|
-- NO_GATHER was supplied, so this also serves to verify that this is no
|
|
-- longer happening.
|
|
BEGIN;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_part WHERE val = 42;
|
|
SET LOCAL pg_plan_advice.advice = 'no_gather(gt_part)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_part WHERE val = 42;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM gt_part WHERE id < 500 AND val = 42;
|
|
COMMIT;
|