mirror of
https://github.com/postgres/postgres.git
synced 2026-03-23 02:43:22 -04:00
5883ff30b0
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
195 lines
8.8 KiB
PL/PgSQL
195 lines
8.8 KiB
PL/PgSQL
LOAD 'pg_plan_advice';
|
|
SET max_parallel_workers_per_gather = 0;
|
|
SET seq_page_cost = 0.1;
|
|
SET random_page_cost = 0.1;
|
|
SET cpu_tuple_cost = 0;
|
|
SET cpu_index_tuple_cost = 0;
|
|
|
|
CREATE TABLE scan_table (a int primary key, b text)
|
|
WITH (autovacuum_enabled = false);
|
|
INSERT INTO scan_table
|
|
SELECT g, 'some text ' || g FROM generate_series(1, 100000) g;
|
|
CREATE INDEX scan_table_b ON scan_table USING brin (b);
|
|
VACUUM ANALYZE scan_table;
|
|
|
|
-- Sequential scan
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table;
|
|
|
|
-- Index scan
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
|
|
-- Index-only scan
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
|
|
-- Bitmap heap scan
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
|
|
WHERE b > 'some text 8';
|
|
|
|
-- TID scan
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid = '(0,1)';
|
|
|
|
-- TID range scan
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
|
|
WHERE ctid > '(1,1)' AND ctid < '(2,1)';
|
|
|
|
-- Try forcing each of our test queries to use the scan type they
|
|
-- wanted to use anyway. This should succeed.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
|
|
WHERE b > 'some text 8';
|
|
SET LOCAL pg_plan_advice.advice = 'TID_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid = '(0,1)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
|
|
WHERE ctid > '(1,1)' AND ctid < '(2,1)';
|
|
COMMIT;
|
|
|
|
-- Try to force a full scan of the table to use some other scan type. All
|
|
-- of these will fail. An index scan or bitmap heap scan could potentially
|
|
-- generate the correct answer, but the planner does not even consider these
|
|
-- possibilities due to the lack of a WHERE clause.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table;
|
|
SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table;
|
|
SET LOCAL pg_plan_advice.advice = 'TID_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table;
|
|
COMMIT;
|
|
|
|
-- Try again to force index use. This should now succeed for the INDEX_SCAN
|
|
-- and BITMAP_HEAP_SCAN, but the INDEX_ONLY_SCAN can't be forced because the
|
|
-- query fetches columns not included in the index.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a > 0;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a > 0;
|
|
SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a > 0;
|
|
COMMIT;
|
|
|
|
-- We can force a primary key lookup to use a sequential scan, but we
|
|
-- can't force it to use an index-only scan (due to the column list)
|
|
-- or a TID scan (due to the absence of a TID qual).
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'TID_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
COMMIT;
|
|
|
|
-- We can forcibly downgrade an index-only scan to an index scan, but we can't
|
|
-- force the use of an index that the planner thinks is inapplicable.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table public.scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_b)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
COMMIT;
|
|
|
|
-- We can force the use of a sequential scan in place of a bitmap heap scan,
|
|
-- but a plain index scan on a BRIN index is not possible.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
|
|
WHERE b > 'some text 8';
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_b)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
COMMIT;
|
|
|
|
-- We can force the use of a sequential scan rather than a TID scan or
|
|
-- TID range scan.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(scan_table)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE ctid = '(0,1)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table
|
|
WHERE ctid > '(1,1)' AND ctid < '(2,1)';
|
|
COMMIT;
|
|
|
|
-- Test more complex scenarios with index scans.
|
|
BEGIN;
|
|
-- Should still work if we mention the schema.
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table public.scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
-- But not if we mention the wrong schema.
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table cilbup.scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
-- It's OK to repeat the same advice.
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey scan_table scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
-- But it doesn't work if the index target is even notionally different.
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table scan_table_pkey scan_table public.scan_table_pkey)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT * FROM scan_table WHERE a = 1;
|
|
COMMIT;
|
|
|
|
-- Test assorted incorrect advice.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(nothing)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(nothing whatsoever)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_SCAN(scan_table bogus)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(nothing whatsoever)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
SET LOCAL pg_plan_advice.advice = 'INDEX_ONLY_SCAN(scan_table bogus)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE) SELECT a FROM scan_table WHERE a = 1;
|
|
COMMIT;
|
|
|
|
-- Test our ability to refer to multiple instances of the same alias.
|
|
BEGIN;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x
|
|
LEFT JOIN scan_table s ON g = s.a;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x
|
|
LEFT JOIN scan_table s ON g = s.a;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s#2)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x
|
|
LEFT JOIN scan_table s ON g = s.a;
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s) SEQ_SCAN(s#2)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (generate_series(1,10) g LEFT JOIN scan_table s ON g = s.a) x
|
|
LEFT JOIN scan_table s ON g = s.a;
|
|
COMMIT;
|
|
|
|
-- Test our ability to refer to scans within a subquery.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0);
|
|
BEGIN;
|
|
-- Should not match.
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0);
|
|
-- Should match first query only.
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s@x)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0);
|
|
-- Should match second query only.
|
|
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(s@unnamed_subquery)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0) x;
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM (SELECT * FROM scan_table s WHERE a = 1 OFFSET 0);
|
|
COMMIT;
|