mirror of
https://github.com/postgres/postgres.git
synced 2026-10-23 13:29:09 +03:00
63920eb099
Up until now, pg_plan_advice has had a feature that allows an advice target to mention a partition name but omit the partition schema; that is, something like SEQ_SCAN(foo/bar) forces a sequential scan on every child of table foo whose partition name is bar, regardless of the schema in which bar appears. However, that feature turns out to have a nasty design flaw: while advice enforcement handles this case just fine, the advice feedback code doesn't know about it and will mark such advice as "matched, failed" even when everything worked perfectly. Unfortunately, there seems to be no simple code fix for this problem. Since the release of PostgreSQL 19 is imminent, take the conservative course and revert this feature of pg_plan_advice. In other words, require the partition schema whenever the partition name is present. You must now write e.g. SEQ_SCAN(foo/public.bar) rather than just SEQ_SCAN(foo/bar). This doesn't affect any cases where automatically generated advice is supplied, since generated advice has always included the partition schema anyway. Manually written advice will have to conform to the new, stricter rule. For a later release, we can consider whether to again relax this restriction in some way, but now is not the time to design new things. Reported-by: Noah Misch <noah@leadboat.com> Backpatch-through: 19 Discussion: https://postgr.es/m/CA+TgmoYmXy-jiP5qDhqNEiYFEBzQsArO6O2d9E8szNZqi1bePQ@mail.gmail.com
101 lines
3.8 KiB
SQL
101 lines
3.8 KiB
SQL
LOAD 'pg_plan_advice';
|
|
|
|
-- An empty string is allowed. Empty target lists are allowed for most advice
|
|
-- tags, but not for JOIN_ORDER. "Supplied Plan Advice" should be omitted in
|
|
-- text format when there is no actual advice, but not in non-text format.
|
|
SET pg_plan_advice.advice = '';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN()';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'NESTED_LOOP_PLAIN()';
|
|
EXPLAIN (COSTS OFF, FORMAT JSON) SELECT 1;
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER()';
|
|
|
|
-- Test assorted variations in capitalization, whitespace, and which parts of
|
|
-- the relation identifier are included. These should all work.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'seq_scan(x@y)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_scan(x#2)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x#1_0)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = ' SEQ_SCAN ( x / y . z ) ';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("x"#2/"y"."z"@"t")';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
|
|
-- Syntax errors.
|
|
SET pg_plan_advice.advice = 'SEQUENTIAL_SCAN(x)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("")';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN("a"';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(#';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x#1_0_)';
|
|
SET pg_plan_advice.advice = '()';
|
|
SET pg_plan_advice.advice = '123';
|
|
|
|
-- Out of range values.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x#99999999999_99)';
|
|
|
|
-- Partition schema is missing.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(x/y)';
|
|
|
|
-- Tags like SEQ_SCAN and NO_GATHER don't allow sublists at all; other tags,
|
|
-- except for JOIN_ORDER, allow at most one level of sublist. Hence, these
|
|
-- examples should error out.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN((x))';
|
|
SET pg_plan_advice.advice = 'DO_NOT_SCAN((x))';
|
|
SET pg_plan_advice.advice = 'GATHER(((x)))';
|
|
|
|
-- Legal comments.
|
|
SET pg_plan_advice.advice = '/**/';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'HASH_JOIN(_)/***/';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = '/* comment */ HASH_JOIN(/*x*/y)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = '/* comment */ HASH_JOIN(y//*x*/z.zz)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
|
|
-- Unterminated comments.
|
|
SET pg_plan_advice.advice = '/*';
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER("fOO") /* oops';
|
|
|
|
-- Nested comments are not supported, so the first of these is legal and
|
|
-- the second is not.
|
|
SET pg_plan_advice.advice = '/*/*/';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = '/*/* stuff */*/';
|
|
|
|
-- Foreign join requires multiple relation identifiers.
|
|
SET pg_plan_advice.advice = 'FOREIGN_JOIN(a)';
|
|
SET pg_plan_advice.advice = 'FOREIGN_JOIN((a))';
|
|
SET pg_plan_advice.advice = 'FOREIGN_JOIN(())';
|
|
|
|
-- Join order requires at least one relation identifier.
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER(())';
|
|
SET pg_plan_advice.advice = 'JOIN_ORDER(a ({}))';
|
|
|
|
-- Tag keywords used as alias names work fine, because the 'identifier'
|
|
-- nonterminal accepts all token types.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(hash_join)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(seq_scan)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(gather)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(join_order)';
|
|
EXPLAIN (COSTS OFF) SELECT 1;
|
|
|
|
-- Tag keywords used as partition names or plan names should also work,
|
|
-- since pgpa_identifier_string() can generate these from real partition
|
|
-- and subquery names.
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t/public.hash_join)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t/hash_join.foo)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t@hash_join)';
|
|
SET pg_plan_advice.advice = 'SEQ_SCAN(t@seq_scan)';
|