Hello, Adding v2 as it v1 doesn't apply anymore.
Cheers, Florin www.enterprisedb.com
From 14fa35f2336a6d296603a3a5af2b0b205af532d6 Mon Sep 17 00:00:00 2001 From: Florin Irion <[email protected]> Date: Thu, 18 Jun 2026 14:28:50 +0200 Subject: [PATCH v2] pg_plan_advice: Add NO_SCAN and NO_JOIN_METHOD tags MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Add NO_SEQ_SCAN, NO_INDEX_SCAN, NO_INDEX_ONLY_SCAN, NO_BITMAP_HEAP_SCAN, and NO_TID_SCAN as negative scan-method directives; and NO_HASH_JOIN, NO_NESTED_LOOP (plus _PLAIN, _MEMOIZE, _MATERIALIZE variants), and NO_MERGE_JOIN (plus _PLAIN, _MATERIALIZE variants) as negative join-method directives. Each NO_ tag is the logical complement of its positive counterpart. Scan NO_ tags use simple_target_list (sublists invalid); join NO_ tags use generic_target_list (sublists allowed). Because of this, both the scanner and the parser only need to handle the NO_ scan tags explicitly: the scanner classifies them as TOK_TAG_SIMPLE and the parser resolves their names in the TOK_TAG_SIMPLE action. The NO_ join tags fall through to the default TOK_TAG_GENERIC branch, where the tag is resolved generically by pgpa_parse_advice_tag, so they need no scanner or parser change. Conflict detection marks both a positive and its NO_ counterpart as conflicting when they target the same relation and method. NO_ join tags carry the same join-order constraint as their positive counterparts: NO_HASH_JOIN(x) means x may not appear on the outer (driving) side — equivalently, it means "use hash join or merge join with x as inner". The constraint is semantically intrinsic, not incidental. --- contrib/pg_plan_advice/Makefile | 4 +- contrib/pg_plan_advice/expected/no_join.out | 288 ++++++++++++++++++++ contrib/pg_plan_advice/expected/no_scan.out | 197 +++++++++++++ contrib/pg_plan_advice/pgpa_ast.c | 52 ++++ contrib/pg_plan_advice/pgpa_ast.h | 13 + contrib/pg_plan_advice/pgpa_parser.y | 10 + contrib/pg_plan_advice/pgpa_planner.c | 184 +++++++++++-- contrib/pg_plan_advice/pgpa_scanner.l | 7 +- contrib/pg_plan_advice/pgpa_trove.c | 13 + contrib/pg_plan_advice/pgpa_walker.c | 71 +++++ contrib/pg_plan_advice/sql/no_join.sql | 92 +++++++ contrib/pg_plan_advice/sql/no_scan.sql | 88 ++++++ doc/src/sgml/pgplanadvice.sgml | 129 +++++++++ 13 files changed, 1125 insertions(+), 23 deletions(-) create mode 100644 contrib/pg_plan_advice/expected/no_join.out create mode 100644 contrib/pg_plan_advice/expected/no_scan.out create mode 100644 contrib/pg_plan_advice/sql/no_join.sql create mode 100644 contrib/pg_plan_advice/sql/no_scan.sql diff --git a/contrib/pg_plan_advice/Makefile b/contrib/pg_plan_advice/Makefile index c844846dd54..9db535739d0 100644 --- a/contrib/pg_plan_advice/Makefile +++ b/contrib/pg_plan_advice/Makefile @@ -19,8 +19,8 @@ HEADERS_pg_plan_advice = pg_plan_advice.h PGFILEDESC = "pg_plan_advice - help the planner get the right plan" -REGRESS = alternatives gather join_order join_strategy partitionwise \ - prepared scan semijoin syntax +REGRESS = alternatives gather join_order join_strategy no_join no_scan \ + partitionwise prepared scan semijoin syntax TAP_TESTS = 1 diff --git a/contrib/pg_plan_advice/expected/no_join.out b/contrib/pg_plan_advice/expected/no_join.out new file mode 100644 index 00000000000..5c3f3fdb772 --- /dev/null +++ b/contrib/pg_plan_advice/expected/no_join.out @@ -0,0 +1,288 @@ +LOAD 'pg_plan_advice'; +SET max_parallel_workers_per_gather = 0; +CREATE TABLE no_join_dim (id serial primary key, dim text) + WITH (autovacuum_enabled = false); +INSERT INTO no_join_dim (dim) SELECT random()::text FROM generate_series(1,100) g; +VACUUM ANALYZE no_join_dim; +CREATE TABLE no_join_fact ( + id int primary key, + dim_id integer not null references no_join_dim (id) +) WITH (autovacuum_enabled = false); +INSERT INTO no_join_fact + SELECT g, (g%3)+1 FROM generate_series(1,100000) g; +CREATE INDEX no_join_fact_dim_id ON no_join_fact (dim_id); +VACUUM ANALYZE no_join_fact; +-- Baseline: hash join with d on the inner side. +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +--------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(10 rows) + +-- NO_HASH_JOIN(d): d stays inner, planner falls back to another method. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------------------------------- + Nested Loop + -> Seq Scan on no_join_fact f + -> Memoize + Cache Key: f.dim_id + Cache Mode: logical + -> Index Scan using no_join_dim_pkey on no_join_dim d + Index Cond: (id = f.dim_id) + Supplied Plan Advice: + NO_HASH_JOIN(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + NESTED_LOOP_MEMOIZE(d) + SEQ_SCAN(f) + INDEX_SCAN(d public.no_join_dim_pkey) + NO_GATHER(f d) +(15 rows) + +COMMIT; +-- NO_HASH_JOIN(f): the join-order constraint forces f to the inner side. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------------------------------------- + Merge Join + Merge Cond: (d.id = f.dim_id) + -> Index Scan using no_join_dim_pkey on no_join_dim d + -> Index Scan using no_join_fact_dim_id on no_join_fact f + Supplied Plan Advice: + NO_HASH_JOIN(f) /* matched */ + Generated Plan Advice: + JOIN_ORDER(d f) + MERGE_JOIN_PLAIN(f) + INDEX_SCAN(d public.no_join_dim_pkey f public.no_join_fact_dim_id) + NO_GATHER(f d) +(11 rows) + +COMMIT; +-- NO_NESTED_LOOP(f): f forced inner, all NL variants forbidden -> merge join. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------------------------------------- + Merge Join + Merge Cond: (d.id = f.dim_id) + -> Index Scan using no_join_dim_pkey on no_join_dim d + -> Index Scan using no_join_fact_dim_id on no_join_fact f + Supplied Plan Advice: + NO_NESTED_LOOP(f) /* matched */ + Generated Plan Advice: + JOIN_ORDER(d f) + MERGE_JOIN_PLAIN(f) + INDEX_SCAN(d public.no_join_dim_pkey f public.no_join_fact_dim_id) + NO_GATHER(f d) +(11 rows) + +COMMIT; +-- NO_MERGE_JOIN(d): d already inner, hash join still allowed -> plan unchanged. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_MERGE_JOIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +--------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Supplied Plan Advice: + NO_MERGE_JOIN(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(12 rows) + +COMMIT; +-- Stacking: NO_HASH_JOIN(f) + NO_NESTED_LOOP(f) leaves only merge join. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(f) NO_NESTED_LOOP(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------------------------------------- + Merge Join + Merge Cond: (d.id = f.dim_id) + -> Index Scan using no_join_dim_pkey on no_join_dim d + -> Index Scan using no_join_fact_dim_id on no_join_fact f + Supplied Plan Advice: + NO_HASH_JOIN(f) /* matched */ + NO_NESTED_LOOP(f) /* matched */ + Generated Plan Advice: + JOIN_ORDER(d f) + MERGE_JOIN_PLAIN(f) + INDEX_SCAN(d public.no_join_dim_pkey f public.no_join_fact_dim_id) + NO_GATHER(f d) +(12 rows) + +COMMIT; +-- Specific NO_ variants targeting d, already the inner side: forbidding any one +-- NL or merge variant leaves hash join available, so the plan is unchanged. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP_PLAIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +----------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Supplied Plan Advice: + NO_NESTED_LOOP_PLAIN(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(12 rows) + +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP_MEMOIZE(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +------------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Supplied Plan Advice: + NO_NESTED_LOOP_MEMOIZE(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(12 rows) + +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP_MATERIALIZE(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +----------------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Supplied Plan Advice: + NO_NESTED_LOOP_MATERIALIZE(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(12 rows) + +SET LOCAL pg_plan_advice.advice = 'NO_MERGE_JOIN_PLAIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Supplied Plan Advice: + NO_MERGE_JOIN_PLAIN(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(12 rows) + +SET LOCAL pg_plan_advice.advice = 'NO_MERGE_JOIN_MATERIALIZE(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------------- + Hash Join + Hash Cond: (f.dim_id = d.id) + -> Seq Scan on no_join_fact f + -> Hash + -> Seq Scan on no_join_dim d + Supplied Plan Advice: + NO_MERGE_JOIN_MATERIALIZE(d) /* matched */ + Generated Plan Advice: + JOIN_ORDER(f d) + HASH_JOIN(d) + SEQ_SCAN(f d) + NO_GATHER(f d) +(12 rows) + +COMMIT; +-- Conflict: same method required and forbidden; both marked conflicting. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'HASH_JOIN(f) NO_HASH_JOIN(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +---------------------------------------------------------------------- + Merge Join + Merge Cond: (d.id = f.dim_id) + -> Index Scan using no_join_dim_pkey on no_join_dim d + -> Index Scan using no_join_fact_dim_id on no_join_fact f + Supplied Plan Advice: + HASH_JOIN(f) /* matched, conflicting, failed */ + NO_HASH_JOIN(f) /* matched, conflicting */ + Generated Plan Advice: + JOIN_ORDER(d f) + MERGE_JOIN_PLAIN(f) + INDEX_SCAN(d public.no_join_dim_pkey f public.no_join_fact_dim_id) + NO_GATHER(f d) +(12 rows) + +COMMIT; +-- No conflict: NO_HASH_JOIN(f) and a positive tag for a different method. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(f) NESTED_LOOP_PLAIN(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + QUERY PLAN +-------------------------------------------------------------- + Nested Loop + -> Seq Scan on no_join_dim d + -> Index Scan using no_join_fact_dim_id on no_join_fact f + Index Cond: (dim_id = d.id) + Supplied Plan Advice: + NO_HASH_JOIN(f) /* matched */ + NESTED_LOOP_PLAIN(f) /* matched */ + Generated Plan Advice: + JOIN_ORDER(d f) + NESTED_LOOP_PLAIN(f) + SEQ_SCAN(d) + INDEX_SCAN(f public.no_join_fact_dim_id) + NO_GATHER(f d) +(13 rows) + +COMMIT; +DROP TABLE no_join_fact; +DROP TABLE no_join_dim; diff --git a/contrib/pg_plan_advice/expected/no_scan.out b/contrib/pg_plan_advice/expected/no_scan.out new file mode 100644 index 00000000000..08f2a21b650 --- /dev/null +++ b/contrib/pg_plan_advice/expected/no_scan.out @@ -0,0 +1,197 @@ +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 no_scan_table (a int primary key, b text) + WITH (autovacuum_enabled = false); +INSERT INTO no_scan_table + SELECT g, 'some text ' || g FROM generate_series(1, 100000) g; +VACUUM ANALYZE no_scan_table; +-- Baselines without advice: seq scan for a full read, index scan for a lookup. +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table; + QUERY PLAN +---------------------------- + Seq Scan on no_scan_table + Generated Plan Advice: + SEQ_SCAN(no_scan_table) + NO_GATHER(no_scan_table) +(4 rows) + +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; + QUERY PLAN +------------------------------------------------------- + Index Scan using no_scan_table_pkey on no_scan_table + Index Cond: (a = 1) + Generated Plan Advice: + INDEX_SCAN(no_scan_table public.no_scan_table_pkey) + NO_GATHER(no_scan_table) +(5 rows) + +-- NO_SEQ_SCAN with no indexable qual: no alternative, so the advice fails. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table; + QUERY PLAN +---------------------------------------------------- + Seq Scan on no_scan_table + Disabled: true + Supplied Plan Advice: + NO_SEQ_SCAN(no_scan_table) /* matched, failed */ + Generated Plan Advice: + SEQ_SCAN(no_scan_table) + NO_GATHER(no_scan_table) +(7 rows) + +COMMIT; +-- NO_SEQ_SCAN with an indexable qual: falls back to index scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; + QUERY PLAN +------------------------------------------------------- + Index Scan using no_scan_table_pkey on no_scan_table + Index Cond: (a = 1) + Supplied Plan Advice: + NO_SEQ_SCAN(no_scan_table) /* matched */ + Generated Plan Advice: + INDEX_SCAN(no_scan_table public.no_scan_table_pkey) + NO_GATHER(no_scan_table) +(7 rows) + +COMMIT; +-- NO_INDEX_SCAN: falls back to bitmap heap scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_INDEX_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; + QUERY PLAN +----------------------------------------------- + Bitmap Heap Scan on no_scan_table + Recheck Cond: (a = 1) + -> Bitmap Index Scan on no_scan_table_pkey + Index Cond: (a = 1) + Supplied Plan Advice: + NO_INDEX_SCAN(no_scan_table) /* matched */ + Generated Plan Advice: + BITMAP_HEAP_SCAN(no_scan_table) + NO_GATHER(no_scan_table) +(9 rows) + +COMMIT; +-- NO_INDEX_ONLY_SCAN: falls back to a plain index scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_INDEX_ONLY_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT a FROM no_scan_table WHERE a = 1; + QUERY PLAN +------------------------------------------------------- + Index Scan using no_scan_table_pkey on no_scan_table + Index Cond: (a = 1) + Supplied Plan Advice: + NO_INDEX_ONLY_SCAN(no_scan_table) /* matched */ + Generated Plan Advice: + INDEX_SCAN(no_scan_table public.no_scan_table_pkey) + NO_GATHER(no_scan_table) +(7 rows) + +COMMIT; +-- NO_BITMAP_HEAP_SCAN: with only a BRIN index available, falls back to seq scan. +CREATE INDEX no_scan_table_b ON no_scan_table USING brin (b); +VACUUM ANALYZE no_scan_table; +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_BITMAP_HEAP_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE b > 'some text 8'; + QUERY PLAN +---------------------------------------------------- + Seq Scan on no_scan_table + Filter: (b > 'some text 8'::text) + Supplied Plan Advice: + NO_BITMAP_HEAP_SCAN(no_scan_table) /* matched */ + Generated Plan Advice: + SEQ_SCAN(no_scan_table) + NO_GATHER(no_scan_table) +(7 rows) + +COMMIT; +-- NO_TID_SCAN: falls back to seq scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_TID_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE ctid = '(0,1)'; + QUERY PLAN +-------------------------------------------- + Seq Scan on no_scan_table + Filter: (ctid = '(0,1)'::tid) + Supplied Plan Advice: + NO_TID_SCAN(no_scan_table) /* matched */ + Generated Plan Advice: + SEQ_SCAN(no_scan_table) + NO_GATHER(no_scan_table) +(7 rows) + +COMMIT; +-- Multiple NO_ scan tags stack without conflict. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table) NO_BITMAP_HEAP_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; + QUERY PLAN +------------------------------------------------------- + Index Scan using no_scan_table_pkey on no_scan_table + Index Cond: (a = 1) + Supplied Plan Advice: + NO_SEQ_SCAN(no_scan_table) /* matched */ + NO_BITMAP_HEAP_SCAN(no_scan_table) /* matched */ + Generated Plan Advice: + INDEX_SCAN(no_scan_table public.no_scan_table_pkey) + NO_GATHER(no_scan_table) +(8 rows) + +COMMIT; +-- Conflict: same method required and forbidden; both conflicting, neither applied. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(no_scan_table) NO_SEQ_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table; + QUERY PLAN +----------------------------------------------------------------- + Seq Scan on no_scan_table + Supplied Plan Advice: + SEQ_SCAN(no_scan_table) /* matched, conflicting */ + NO_SEQ_SCAN(no_scan_table) /* matched, conflicting, failed */ + Generated Plan Advice: + SEQ_SCAN(no_scan_table) + NO_GATHER(no_scan_table) +(7 rows) + +COMMIT; +-- No conflict: NO_SEQ_SCAN and a positive INDEX_SCAN are compatible. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table) INDEX_SCAN(no_scan_table no_scan_table_pkey)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; + QUERY PLAN +-------------------------------------------------------------- + Index Scan using no_scan_table_pkey on no_scan_table + Index Cond: (a = 1) + Supplied Plan Advice: + NO_SEQ_SCAN(no_scan_table) /* matched */ + INDEX_SCAN(no_scan_table no_scan_table_pkey) /* matched */ + Generated Plan Advice: + INDEX_SCAN(no_scan_table public.no_scan_table_pkey) + NO_GATHER(no_scan_table) +(8 rows) + +COMMIT; +-- Scan tags forbid sublists (simple_target_list): this is a syntax error. +SET pg_plan_advice.advice = 'NO_SEQ_SCAN((no_scan_table no_scan_table))'; +ERROR: invalid value for parameter "pg_plan_advice.advice": "NO_SEQ_SCAN((no_scan_table no_scan_table))" +DETAIL: Could not parse advice: syntax error at or near "(" +DROP TABLE no_scan_table; diff --git a/contrib/pg_plan_advice/pgpa_ast.c b/contrib/pg_plan_advice/pgpa_ast.c index 01db8d24cd0..02025b61dd3 100644 --- a/contrib/pg_plan_advice/pgpa_ast.c +++ b/contrib/pg_plan_advice/pgpa_ast.c @@ -58,8 +58,34 @@ pgpa_cstring_advice_tag(pgpa_advice_tag_type advice_tag) return "NESTED_LOOP_MEMOIZE"; case PGPA_TAG_NESTED_LOOP_PLAIN: return "NESTED_LOOP_PLAIN"; + case PGPA_TAG_NO_BITMAP_HEAP_SCAN: + return "NO_BITMAP_HEAP_SCAN"; case PGPA_TAG_NO_GATHER: return "NO_GATHER"; + case PGPA_TAG_NO_HASH_JOIN: + return "NO_HASH_JOIN"; + case PGPA_TAG_NO_INDEX_ONLY_SCAN: + return "NO_INDEX_ONLY_SCAN"; + case PGPA_TAG_NO_INDEX_SCAN: + return "NO_INDEX_SCAN"; + case PGPA_TAG_NO_MERGE_JOIN: + return "NO_MERGE_JOIN"; + case PGPA_TAG_NO_MERGE_JOIN_MATERIALIZE: + return "NO_MERGE_JOIN_MATERIALIZE"; + case PGPA_TAG_NO_MERGE_JOIN_PLAIN: + return "NO_MERGE_JOIN_PLAIN"; + case PGPA_TAG_NO_NESTED_LOOP: + return "NO_NESTED_LOOP"; + case PGPA_TAG_NO_NESTED_LOOP_MATERIALIZE: + return "NO_NESTED_LOOP_MATERIALIZE"; + case PGPA_TAG_NO_NESTED_LOOP_MEMOIZE: + return "NO_NESTED_LOOP_MEMOIZE"; + case PGPA_TAG_NO_NESTED_LOOP_PLAIN: + return "NO_NESTED_LOOP_PLAIN"; + case PGPA_TAG_NO_SEQ_SCAN: + return "NO_SEQ_SCAN"; + case PGPA_TAG_NO_TID_SCAN: + return "NO_TID_SCAN"; case PGPA_TAG_PARTITIONWISE: return "PARTITIONWISE"; case PGPA_TAG_SEMIJOIN_NON_UNIQUE: @@ -135,8 +161,34 @@ pgpa_parse_advice_tag(const char *tag, bool *fail) return PGPA_TAG_NESTED_LOOP_MEMOIZE; if (strcmp(tag, "nested_loop_plain") == 0) return PGPA_TAG_NESTED_LOOP_PLAIN; + if (strcmp(tag, "no_bitmap_heap_scan") == 0) + return PGPA_TAG_NO_BITMAP_HEAP_SCAN; if (strcmp(tag, "no_gather") == 0) return PGPA_TAG_NO_GATHER; + if (strcmp(tag, "no_hash_join") == 0) + return PGPA_TAG_NO_HASH_JOIN; + if (strcmp(tag, "no_index_only_scan") == 0) + return PGPA_TAG_NO_INDEX_ONLY_SCAN; + if (strcmp(tag, "no_index_scan") == 0) + return PGPA_TAG_NO_INDEX_SCAN; + if (strcmp(tag, "no_merge_join") == 0) + return PGPA_TAG_NO_MERGE_JOIN; + if (strcmp(tag, "no_merge_join_materialize") == 0) + return PGPA_TAG_NO_MERGE_JOIN_MATERIALIZE; + if (strcmp(tag, "no_merge_join_plain") == 0) + return PGPA_TAG_NO_MERGE_JOIN_PLAIN; + if (strcmp(tag, "no_nested_loop") == 0) + return PGPA_TAG_NO_NESTED_LOOP; + if (strcmp(tag, "no_nested_loop_materialize") == 0) + return PGPA_TAG_NO_NESTED_LOOP_MATERIALIZE; + if (strcmp(tag, "no_nested_loop_memoize") == 0) + return PGPA_TAG_NO_NESTED_LOOP_MEMOIZE; + if (strcmp(tag, "no_nested_loop_plain") == 0) + return PGPA_TAG_NO_NESTED_LOOP_PLAIN; + if (strcmp(tag, "no_seq_scan") == 0) + return PGPA_TAG_NO_SEQ_SCAN; + if (strcmp(tag, "no_tid_scan") == 0) + return PGPA_TAG_NO_TID_SCAN; break; case 'p': if (strcmp(tag, "partitionwise") == 0) diff --git a/contrib/pg_plan_advice/pgpa_ast.h b/contrib/pg_plan_advice/pgpa_ast.h index 4bd6ffa5e3a..c18fc164abc 100644 --- a/contrib/pg_plan_advice/pgpa_ast.h +++ b/contrib/pg_plan_advice/pgpa_ast.h @@ -93,7 +93,20 @@ typedef enum pgpa_advice_tag_type PGPA_TAG_NESTED_LOOP_MATERIALIZE, PGPA_TAG_NESTED_LOOP_MEMOIZE, PGPA_TAG_NESTED_LOOP_PLAIN, + PGPA_TAG_NO_BITMAP_HEAP_SCAN, PGPA_TAG_NO_GATHER, + PGPA_TAG_NO_HASH_JOIN, + PGPA_TAG_NO_INDEX_ONLY_SCAN, + PGPA_TAG_NO_INDEX_SCAN, + PGPA_TAG_NO_MERGE_JOIN, + PGPA_TAG_NO_MERGE_JOIN_MATERIALIZE, + PGPA_TAG_NO_MERGE_JOIN_PLAIN, + PGPA_TAG_NO_NESTED_LOOP, + PGPA_TAG_NO_NESTED_LOOP_MATERIALIZE, + PGPA_TAG_NO_NESTED_LOOP_MEMOIZE, + PGPA_TAG_NO_NESTED_LOOP_PLAIN, + PGPA_TAG_NO_SEQ_SCAN, + PGPA_TAG_NO_TID_SCAN, PGPA_TAG_PARTITIONWISE, PGPA_TAG_SEMIJOIN_NON_UNIQUE, PGPA_TAG_SEMIJOIN_UNIQUE, diff --git a/contrib/pg_plan_advice/pgpa_parser.y b/contrib/pg_plan_advice/pgpa_parser.y index 5811a6e5e56..38684bef727 100644 --- a/contrib/pg_plan_advice/pgpa_parser.y +++ b/contrib/pg_plan_advice/pgpa_parser.y @@ -108,8 +108,18 @@ advice_item: TOK_TAG_JOIN_ORDER '(' join_order_target_list ')' $$->tag = PGPA_TAG_BITMAP_HEAP_SCAN; else if (strcmp($1, "do_not_scan") == 0) $$->tag = PGPA_TAG_DO_NOT_SCAN; + else if (strcmp($1, "no_bitmap_heap_scan") == 0) + $$->tag = PGPA_TAG_NO_BITMAP_HEAP_SCAN; else if (strcmp($1, "no_gather") == 0) $$->tag = PGPA_TAG_NO_GATHER; + else if (strcmp($1, "no_index_only_scan") == 0) + $$->tag = PGPA_TAG_NO_INDEX_ONLY_SCAN; + else if (strcmp($1, "no_index_scan") == 0) + $$->tag = PGPA_TAG_NO_INDEX_SCAN; + else if (strcmp($1, "no_seq_scan") == 0) + $$->tag = PGPA_TAG_NO_SEQ_SCAN; + else if (strcmp($1, "no_tid_scan") == 0) + $$->tag = PGPA_TAG_NO_TID_SCAN; else if (strcmp($1, "seq_scan") == 0) $$->tag = PGPA_TAG_SEQ_SCAN; else if (strcmp($1, "tid_scan") == 0) diff --git a/contrib/pg_plan_advice/pgpa_planner.c b/contrib/pg_plan_advice/pgpa_planner.c index b3329b793aa..a29500e0e1d 100644 --- a/contrib/pg_plan_advice/pgpa_planner.c +++ b/contrib/pg_plan_advice/pgpa_planner.c @@ -155,6 +155,9 @@ static bool pgpa_semijoin_permits_join(int outer_count, int inner_count, bool outer_is_nullable, bool *restrict_method); +static uint64 pgpa_no_join_mask_from_advice_tag(pgpa_advice_tag_type tag); +static uint64 pgpa_no_scan_mask_from_advice_tag(pgpa_advice_tag_type tag); + static List *pgpa_planner_append_feedback(List *list, pgpa_trove *trove, pgpa_trove_lookup_type type, pgpa_identifier *rt_identifiers, @@ -947,8 +950,12 @@ pgpa_planner_apply_join_path_advice(JoinType jointype, uint64 *pgs_mask_p, Bitmapset *jo_deny_indexes = NULL; Bitmapset *jo_deny_rel_indexes = NULL; Bitmapset *jm_indexes = NULL; - bool jm_conflict = false; + bool jm_pos_conflict = false; /* positive tags disagree */ + bool jm_pos_neg_conflict = false; /* a positive method is + * forbidden */ uint64 join_mask = 0; + Bitmapset *no_jm_indexes = NULL; + uint64 no_join_mask = 0; Bitmapset *sj_permit_indexes = NULL; Bitmapset *sj_deny_indexes = NULL; @@ -1043,12 +1050,43 @@ pgpa_planner_apply_join_path_advice(JoinType jointype, uint64 *pgs_mask_p, { jm_indexes = bms_add_member(jm_indexes, i); if (join_mask != 0 && join_mask != my_join_mask) - jm_conflict = true; + jm_pos_conflict = true; join_mask = my_join_mask; } continue; } + /* Handle NO_ join method advice. */ + { + uint64 my_no_join_mask; + + my_no_join_mask = pgpa_no_join_mask_from_advice_tag(entry->tag); + if (my_no_join_mask != 0) + { + bool permit; + bool restrict_method; + + /* + * NO_ join tags impose the same join-order constraint as + * positive ones: the target must be the inner rel. Reuse + * pgpa_join_method_permits_join to enforce it. + */ + permit = pgpa_join_method_permits_join(pjs->outer_count, + pjs->inner_count, + pjs->rids, + entry, + &restrict_method); + if (!permit) + jo_deny_indexes = bms_add_member(jo_deny_indexes, i); + else if (restrict_method) + { + no_jm_indexes = bms_add_member(no_jm_indexes, i); + no_join_mask |= my_no_join_mask; + } + continue; + } + } + /* Handle semijoin uniqueness advice. */ if (entry->tag == PGPA_TAG_SEMIJOIN_UNIQUE || entry->tag == PGPA_TAG_SEMIJOIN_NON_UNIQUE) @@ -1118,13 +1156,26 @@ pgpa_planner_apply_join_path_advice(JoinType jointype, uint64 *pgs_mask_p, } /* - * If more than one join method specification is relevant here and they - * differ, mark them all as conflicting. + * If more than one positive join method specification is relevant here + * and they differ, mark them all as conflicting. */ - if (jm_conflict) + if (jm_pos_conflict) pgpa_trove_set_flags(pjs->join_entries, jm_indexes, PGPA_FB_CONFLICTING); + /* + * Detect positive-vs-negative join method conflict: a method both + * required by a positive tag and forbidden by a NO_ tag. + */ + if ((join_mask & no_join_mask) != 0) + { + jm_pos_neg_conflict = true; + pgpa_trove_set_flags(pjs->join_entries, jm_indexes, + PGPA_FB_CONFLICTING); + pgpa_trove_set_flags(pjs->join_entries, no_jm_indexes, + PGPA_FB_CONFLICTING); + } + /* If semijoin advice says both yes and no, mark it all as conflicting. */ if (sj_permit_indexes != NULL && sj_deny_indexes != NULL) { @@ -1143,18 +1194,18 @@ pgpa_planner_apply_join_path_advice(JoinType jointype, uint64 *pgs_mask_p, if ((jo_deny_indexes != NULL || jo_deny_rel_indexes != NULL) && jo_permit_indexes == NULL) *pgs_mask_p &= ~PGS_JOIN_ANY; - if (join_mask != 0 && !jm_conflict) + if (join_mask != 0 && !jm_pos_conflict && !jm_pos_neg_conflict) *pgs_mask_p &= ~(PGS_JOIN_ANY & ~join_mask); + if (no_join_mask != 0 && !jm_pos_neg_conflict) + *pgs_mask_p &= ~no_join_mask; if (sj_deny_indexes != NULL && sj_permit_indexes == NULL) *pgs_mask_p &= ~PGS_JOIN_ANY; } /* - * Translate an advice tag into a path generation strategy mask. + * Translate a positive join advice tag into a strategy mask to enforce. * - * This function can be called with tag types that don't represent join - * strategies. In such cases, we just return 0, which can't be confused with - * a valid mask. + * Returns 0 for tags that don't represent (positive) join strategies. */ static uint64 pgpa_join_strategy_mask_from_advice_tag(pgpa_advice_tag_type tag) @@ -1180,6 +1231,37 @@ pgpa_join_strategy_mask_from_advice_tag(pgpa_advice_tag_type tag) } } +/* + * Translate a NO_ join advice tag into a strategy mask to forbid. + * + * Returns 0 for tags that aren't NO_ join tags. + */ +static uint64 +pgpa_no_join_mask_from_advice_tag(pgpa_advice_tag_type tag) +{ + switch (tag) + { + case PGPA_TAG_NO_HASH_JOIN: + return PGS_HASHJOIN; + case PGPA_TAG_NO_MERGE_JOIN: + return PGS_MERGEJOIN_ANY; + case PGPA_TAG_NO_MERGE_JOIN_MATERIALIZE: + return PGS_MERGEJOIN_MATERIALIZE; + case PGPA_TAG_NO_MERGE_JOIN_PLAIN: + return PGS_MERGEJOIN_PLAIN; + case PGPA_TAG_NO_NESTED_LOOP: + return PGS_NESTLOOP_ANY; + case PGPA_TAG_NO_NESTED_LOOP_MATERIALIZE: + return PGS_NESTLOOP_MATERIALIZE; + case PGPA_TAG_NO_NESTED_LOOP_MEMOIZE: + return PGS_NESTLOOP_MEMOIZE; + case PGPA_TAG_NO_NESTED_LOOP_PLAIN: + return PGS_NESTLOOP_PLAIN; + default: + return 0; + } +} + /* * Does a certain item of join order advice permit a certain join? * @@ -1619,6 +1701,31 @@ pgpa_semijoin_permits_join(int outer_count, int inner_count, return false; } +/* + * Translate a NO_ scan advice tag into a strategy mask to forbid. + * + * Returns 0 for tags that aren't NO_ scan tags. + */ +static uint64 +pgpa_no_scan_mask_from_advice_tag(pgpa_advice_tag_type tag) +{ + switch (tag) + { + case PGPA_TAG_NO_BITMAP_HEAP_SCAN: + return PGS_BITMAPSCAN; + case PGPA_TAG_NO_INDEX_ONLY_SCAN: + return PGS_INDEXONLYSCAN | PGS_CONSIDER_INDEXONLY; + case PGPA_TAG_NO_INDEX_SCAN: + return PGS_INDEXSCAN; + case PGPA_TAG_NO_SEQ_SCAN: + return PGS_SEQSCAN; + case PGPA_TAG_NO_TID_SCAN: + return PGS_TIDSCAN; + default: + return 0; + } +} + /* * Apply scan advice to a RelOptInfo. */ @@ -1637,19 +1744,36 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel, int i = -1; pgpa_trove_entry *scan_entry = NULL; int flags; - bool scan_type_conflict = false; + bool scan_pos_conflict = false; /* positive tags disagree */ + bool scan_pos_neg_conflict = false; /* a positive method is + * forbidden */ Bitmapset *scan_type_indexes = NULL; Bitmapset *scan_type_rel_indexes = NULL; + Bitmapset *no_scan_indexes = NULL; uint64 gather_mask = 0; uint64 scan_type = all_scan_mask; /* sentinel: no advice yet */ + uint64 no_scan_mask = 0; /* Scrutinize available scan advice. */ while ((i = bms_next_member(scan_indexes, i)) >= 0) { pgpa_trove_entry *my_entry = &scan_entries[i]; uint64 my_scan_type = all_scan_mask; + uint64 my_no_scan_mask; + + /* + * NO_ scan tags just accumulate a set of forbidden methods; handle + * them separately from the positive scan-type logic below. + */ + my_no_scan_mask = pgpa_no_scan_mask_from_advice_tag(my_entry->tag); + if (my_no_scan_mask != 0) + { + no_scan_mask |= my_no_scan_mask; + no_scan_indexes = bms_add_member(no_scan_indexes, i); + continue; + } - /* Translate our advice tags to a scan strategy advice value. */ + /* Translate positive advice tags to a scan strategy value. */ if (my_entry->tag == PGPA_TAG_DO_NOT_SCAN) my_scan_type = 0; else if (my_entry->tag == PGPA_TAG_BITMAP_HEAP_SCAN) @@ -1690,19 +1814,27 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel, if (my_scan_type != all_scan_mask) { if (scan_type != all_scan_mask && scan_type != my_scan_type) - scan_type_conflict = true; - if (!scan_type_conflict && scan_entry != NULL && + scan_pos_conflict = true; + if (!scan_pos_conflict && scan_entry != NULL && my_entry->target->itarget != NULL && scan_entry->target->itarget != NULL && !pgpa_index_targets_equal(scan_entry->target->itarget, my_entry->target->itarget)) - scan_type_conflict = true; + scan_pos_conflict = true; scan_entry = my_entry; scan_type = my_scan_type; scan_type_indexes = bms_add_member(scan_type_indexes, i); } } + /* + * Detect positive-vs-negative scan conflict: a positive scan type that is + * simultaneously forbidden by a NO_ tag. Two NO_ tags for different + * methods are never in conflict with each other. + */ + if (scan_type != all_scan_mask && (scan_type & no_scan_mask) != 0) + scan_pos_neg_conflict = true; + /* Scrutinize available gather-related and partitionwise advice. */ i = -1; while ((i = bms_next_member(rel_indexes, i)) >= 0) @@ -1725,7 +1857,7 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel, const uint64 my_scan_type = PGS_APPEND | PGS_MERGE_APPEND; if (scan_type != all_scan_mask && scan_type != my_scan_type) - scan_type_conflict = true; + scan_pos_conflict = true; scan_entry = my_entry; scan_type = my_scan_type; scan_type_rel_indexes = @@ -1778,7 +1910,7 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel, } /* Enforce choice of index. */ - if (scan_entry != NULL && !scan_type_conflict && + if (scan_entry != NULL && !scan_pos_conflict && (scan_entry->tag == PGPA_TAG_INDEX_SCAN || scan_entry->tag == PGPA_TAG_INDEX_ONLY_SCAN)) { @@ -1822,14 +1954,24 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel, /* * Mark all the scan method entries as fully matched; and if they specify - * different things, mark them all as conflicting. + * different things, mark them all as conflicting. Also mark them as + * conflicting if any NO_ tag forbids the requested scan method. */ flags = PGPA_FB_MATCH_PARTIAL | PGPA_FB_MATCH_FULL; - if (scan_type_conflict) + if (scan_pos_conflict || scan_pos_neg_conflict) flags |= PGPA_FB_CONFLICTING; pgpa_trove_set_flags(scan_entries, scan_type_indexes, flags); pgpa_trove_set_flags(rel_entries, scan_type_rel_indexes, flags); + /* + * Mark NO_ scan entries as fully matched. Also mark them as conflicting + * when a positive tag requests the same scan method. + */ + flags = PGPA_FB_MATCH_PARTIAL | PGPA_FB_MATCH_FULL; + if (scan_pos_neg_conflict) + flags |= PGPA_FB_CONFLICTING; + pgpa_trove_set_flags(scan_entries, no_scan_indexes, flags); + /* * Mark every Gather-related piece of advice as partially matched. Mark * the ones that included this relation as a target by itself as fully @@ -1847,8 +1989,10 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel, * Only clear bits here, so that we still respect the enable_* GUCs. Do * nothing in cases where the advice on a single topic conflicts. */ - if (scan_type != all_scan_mask && !scan_type_conflict) + if (scan_type != all_scan_mask && !scan_pos_conflict && !scan_pos_neg_conflict) rel->pgs_mask &= ~(all_scan_mask & ~scan_type); + if (no_scan_mask != 0 && !scan_pos_neg_conflict) + rel->pgs_mask &= ~(all_scan_mask & no_scan_mask); if (gather_mask != 0 && !gather_conflict) { uint64 all_gather_mask; diff --git a/contrib/pg_plan_advice/pgpa_scanner.l b/contrib/pg_plan_advice/pgpa_scanner.l index e6d60f57e1e..fc8929e31dd 100644 --- a/contrib/pg_plan_advice/pgpa_scanner.l +++ b/contrib/pg_plan_advice/pgpa_scanner.l @@ -129,7 +129,12 @@ xcinside [^*/]+ tag == PGPA_TAG_TID_SCAN || tag == PGPA_TAG_BITMAP_HEAP_SCAN || tag == PGPA_TAG_NO_GATHER || - tag == PGPA_TAG_DO_NOT_SCAN) + tag == PGPA_TAG_DO_NOT_SCAN || + tag == PGPA_TAG_NO_SEQ_SCAN || + tag == PGPA_TAG_NO_TID_SCAN || + tag == PGPA_TAG_NO_BITMAP_HEAP_SCAN || + tag == PGPA_TAG_NO_INDEX_SCAN || + tag == PGPA_TAG_NO_INDEX_ONLY_SCAN) return TOK_TAG_SIMPLE; else return TOK_TAG_GENERIC; diff --git a/contrib/pg_plan_advice/pgpa_trove.c b/contrib/pg_plan_advice/pgpa_trove.c index ca69f3bd3df..abe10363d3e 100644 --- a/contrib/pg_plan_advice/pgpa_trove.c +++ b/contrib/pg_plan_advice/pgpa_trove.c @@ -166,6 +166,11 @@ pgpa_build_trove(List *advice_items) case PGPA_TAG_DO_NOT_SCAN: case PGPA_TAG_INDEX_ONLY_SCAN: case PGPA_TAG_INDEX_SCAN: + case PGPA_TAG_NO_BITMAP_HEAP_SCAN: + case PGPA_TAG_NO_INDEX_ONLY_SCAN: + case PGPA_TAG_NO_INDEX_SCAN: + case PGPA_TAG_NO_SEQ_SCAN: + case PGPA_TAG_NO_TID_SCAN: case PGPA_TAG_SEQ_SCAN: case PGPA_TAG_TID_SCAN: @@ -192,6 +197,14 @@ pgpa_build_trove(List *advice_items) case PGPA_TAG_NESTED_LOOP_MATERIALIZE: case PGPA_TAG_NESTED_LOOP_MEMOIZE: case PGPA_TAG_NESTED_LOOP_PLAIN: + case PGPA_TAG_NO_HASH_JOIN: + case PGPA_TAG_NO_MERGE_JOIN: + case PGPA_TAG_NO_MERGE_JOIN_MATERIALIZE: + case PGPA_TAG_NO_MERGE_JOIN_PLAIN: + case PGPA_TAG_NO_NESTED_LOOP: + case PGPA_TAG_NO_NESTED_LOOP_MATERIALIZE: + case PGPA_TAG_NO_NESTED_LOOP_MEMOIZE: + case PGPA_TAG_NO_NESTED_LOOP_PLAIN: case PGPA_TAG_SEMIJOIN_NON_UNIQUE: case PGPA_TAG_SEMIJOIN_UNIQUE: diff --git a/contrib/pg_plan_advice/pgpa_walker.c b/contrib/pg_plan_advice/pgpa_walker.c index e49361ae266..89052e90707 100644 --- a/contrib/pg_plan_advice/pgpa_walker.c +++ b/contrib/pg_plan_advice/pgpa_walker.c @@ -894,6 +894,77 @@ pgpa_walker_would_advise(pgpa_plan_walker_context *walker, relids); case PGPA_TAG_NO_GATHER: return pgpa_walker_contains_no_gather(walker, relids); + + /* + * For NO_ scan tags, the advice is satisfied when the plan does + * NOT use the forbidden scan strategy. + */ + case PGPA_TAG_NO_SEQ_SCAN: + return pgpa_walker_find_scan(walker, + PGPA_SCAN_SEQ, + relids) == NULL; + case PGPA_TAG_NO_BITMAP_HEAP_SCAN: + return pgpa_walker_find_scan(walker, + PGPA_SCAN_BITMAP_HEAP, + relids) == NULL; + case PGPA_TAG_NO_INDEX_SCAN: + return pgpa_walker_find_scan(walker, + PGPA_SCAN_INDEX, + relids) == NULL; + case PGPA_TAG_NO_INDEX_ONLY_SCAN: + return pgpa_walker_find_scan(walker, + PGPA_SCAN_INDEX_ONLY, + relids) == NULL; + case PGPA_TAG_NO_TID_SCAN: + return pgpa_walker_find_scan(walker, + PGPA_SCAN_TID, + relids) == NULL; + + /* + * For NO_ join tags, the advice is satisfied when the plan does + * NOT use any of the forbidden join strategies. + */ + case PGPA_TAG_NO_HASH_JOIN: + return !pgpa_walker_contains_join(walker, + JSTRAT_HASH_JOIN, + relids); + case PGPA_TAG_NO_MERGE_JOIN: + return !pgpa_walker_contains_join(walker, + JSTRAT_MERGE_JOIN_PLAIN, + relids) && + !pgpa_walker_contains_join(walker, + JSTRAT_MERGE_JOIN_MATERIALIZE, + relids); + case PGPA_TAG_NO_MERGE_JOIN_MATERIALIZE: + return !pgpa_walker_contains_join(walker, + JSTRAT_MERGE_JOIN_MATERIALIZE, + relids); + case PGPA_TAG_NO_MERGE_JOIN_PLAIN: + return !pgpa_walker_contains_join(walker, + JSTRAT_MERGE_JOIN_PLAIN, + relids); + case PGPA_TAG_NO_NESTED_LOOP: + return !pgpa_walker_contains_join(walker, + JSTRAT_NESTED_LOOP_PLAIN, + relids) && + !pgpa_walker_contains_join(walker, + JSTRAT_NESTED_LOOP_MATERIALIZE, + relids) && + !pgpa_walker_contains_join(walker, + JSTRAT_NESTED_LOOP_MEMOIZE, + relids); + case PGPA_TAG_NO_NESTED_LOOP_MATERIALIZE: + return !pgpa_walker_contains_join(walker, + JSTRAT_NESTED_LOOP_MATERIALIZE, + relids); + case PGPA_TAG_NO_NESTED_LOOP_MEMOIZE: + return !pgpa_walker_contains_join(walker, + JSTRAT_NESTED_LOOP_MEMOIZE, + relids); + case PGPA_TAG_NO_NESTED_LOOP_PLAIN: + return !pgpa_walker_contains_join(walker, + JSTRAT_NESTED_LOOP_PLAIN, + relids); } /* should not get here */ diff --git a/contrib/pg_plan_advice/sql/no_join.sql b/contrib/pg_plan_advice/sql/no_join.sql new file mode 100644 index 00000000000..19a9acc99fe --- /dev/null +++ b/contrib/pg_plan_advice/sql/no_join.sql @@ -0,0 +1,92 @@ +LOAD 'pg_plan_advice'; +SET max_parallel_workers_per_gather = 0; + +CREATE TABLE no_join_dim (id serial primary key, dim text) + WITH (autovacuum_enabled = false); +INSERT INTO no_join_dim (dim) SELECT random()::text FROM generate_series(1,100) g; +VACUUM ANALYZE no_join_dim; + +CREATE TABLE no_join_fact ( + id int primary key, + dim_id integer not null references no_join_dim (id) +) WITH (autovacuum_enabled = false); +INSERT INTO no_join_fact + SELECT g, (g%3)+1 FROM generate_series(1,100000) g; +CREATE INDEX no_join_fact_dim_id ON no_join_fact (dim_id); +VACUUM ANALYZE no_join_fact; + +-- Baseline: hash join with d on the inner side. +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; + +-- NO_HASH_JOIN(d): d stays inner, planner falls back to another method. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- NO_HASH_JOIN(f): the join-order constraint forces f to the inner side. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- NO_NESTED_LOOP(f): f forced inner, all NL variants forbidden -> merge join. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- NO_MERGE_JOIN(d): d already inner, hash join still allowed -> plan unchanged. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_MERGE_JOIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- Stacking: NO_HASH_JOIN(f) + NO_NESTED_LOOP(f) leaves only merge join. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(f) NO_NESTED_LOOP(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- Specific NO_ variants targeting d, already the inner side: forbidding any one +-- NL or merge variant leaves hash join available, so the plan is unchanged. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP_PLAIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP_MEMOIZE(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +SET LOCAL pg_plan_advice.advice = 'NO_NESTED_LOOP_MATERIALIZE(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +SET LOCAL pg_plan_advice.advice = 'NO_MERGE_JOIN_PLAIN(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +SET LOCAL pg_plan_advice.advice = 'NO_MERGE_JOIN_MATERIALIZE(d)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- Conflict: same method required and forbidden; both marked conflicting. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'HASH_JOIN(f) NO_HASH_JOIN(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +-- No conflict: NO_HASH_JOIN(f) and a positive tag for a different method. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_HASH_JOIN(f) NESTED_LOOP_PLAIN(f)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_join_fact f JOIN no_join_dim d ON f.dim_id = d.id; +COMMIT; + +DROP TABLE no_join_fact; +DROP TABLE no_join_dim; diff --git a/contrib/pg_plan_advice/sql/no_scan.sql b/contrib/pg_plan_advice/sql/no_scan.sql new file mode 100644 index 00000000000..b189ef73440 --- /dev/null +++ b/contrib/pg_plan_advice/sql/no_scan.sql @@ -0,0 +1,88 @@ +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 no_scan_table (a int primary key, b text) + WITH (autovacuum_enabled = false); +INSERT INTO no_scan_table + SELECT g, 'some text ' || g FROM generate_series(1, 100000) g; +VACUUM ANALYZE no_scan_table; + +-- Baselines without advice: seq scan for a full read, index scan for a lookup. +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; + +-- NO_SEQ_SCAN with no indexable qual: no alternative, so the advice fails. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table; +COMMIT; + +-- NO_SEQ_SCAN with an indexable qual: falls back to index scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; +COMMIT; + +-- NO_INDEX_SCAN: falls back to bitmap heap scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_INDEX_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; +COMMIT; + +-- NO_INDEX_ONLY_SCAN: falls back to a plain index scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_INDEX_ONLY_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT a FROM no_scan_table WHERE a = 1; +COMMIT; + +-- NO_BITMAP_HEAP_SCAN: with only a BRIN index available, falls back to seq scan. +CREATE INDEX no_scan_table_b ON no_scan_table USING brin (b); +VACUUM ANALYZE no_scan_table; +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_BITMAP_HEAP_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE b > 'some text 8'; +COMMIT; + +-- NO_TID_SCAN: falls back to seq scan. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_TID_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE ctid = '(0,1)'; +COMMIT; + +-- Multiple NO_ scan tags stack without conflict. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table) NO_BITMAP_HEAP_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; +COMMIT; + +-- Conflict: same method required and forbidden; both conflicting, neither applied. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(no_scan_table) NO_SEQ_SCAN(no_scan_table)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table; +COMMIT; + +-- No conflict: NO_SEQ_SCAN and a positive INDEX_SCAN are compatible. +BEGIN; +SET LOCAL pg_plan_advice.advice = 'NO_SEQ_SCAN(no_scan_table) INDEX_SCAN(no_scan_table no_scan_table_pkey)'; +EXPLAIN (COSTS OFF, PLAN_ADVICE) + SELECT * FROM no_scan_table WHERE a = 1; +COMMIT; + +-- Scan tags forbid sublists (simple_target_list): this is a syntax error. +SET pg_plan_advice.advice = 'NO_SEQ_SCAN((no_scan_table no_scan_table))'; + +DROP TABLE no_scan_table; diff --git a/doc/src/sgml/pgplanadvice.sgml b/doc/src/sgml/pgplanadvice.sgml index b1395ec951e..dff6594a54b 100644 --- a/doc/src/sgml/pgplanadvice.sgml +++ b/doc/src/sgml/pgplanadvice.sgml @@ -320,6 +320,58 @@ DO_NOT_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis> </sect3> + <sect3 id="pgplanadvice-no-scan-method"> + <title>Negative Scan Method Advice</title> + <synopsis> +NO_SEQ_SCAN(<replaceable>target</replaceable> [ ... ]) +NO_TID_SCAN(<replaceable>target</replaceable> [ ... ]) +NO_INDEX_SCAN(<replaceable>target</replaceable> [ ... ]) +NO_INDEX_ONLY_SCAN(<replaceable>target</replaceable> [ ... ]) +NO_BITMAP_HEAP_SCAN(<replaceable>target</replaceable> [ ... ])</synopsis> + + <para> + These tags are the negative counterparts of the positive scan method tags. + Each <literal>NO_</literal> tag forbids the corresponding scan method for + the specified relation or relations. The planner will choose any other + eligible scan method instead. + </para> + + <para> + For example, <literal>NO_SEQ_SCAN(t)</literal> tells the planner not to + use a <literal>Seq Scan</literal> on <literal>t</literal>. If the only + viable alternative is an index scan, the planner will use that. If no + alternative is available, the plan still uses the forbidden method and the + advice is reported as <literal>failed</literal>. + </para> + + <para> + Unlike the positive scan method tags, multiple negative scan method tags + for the same relation can be combined freely without conflict. For example, + <literal>NO_SEQ_SCAN(t) NO_BITMAP_HEAP_SCAN(t)</literal> is valid and + restricts the planner to use an index scan or TID scan on <literal>t</literal>. + </para> + + <para> + A negative tag and a positive tag specifying the same method are in + conflict with each other. For example, <literal>SEQ_SCAN(t) + NO_SEQ_SCAN(t)</literal> is a contradiction, and both pieces of advice will + be marked <literal>conflicting</literal>. However, a negative tag for one + method does not conflict with a positive tag for a different method. For + instance, <literal>NO_SEQ_SCAN(t) INDEX_SCAN(t my_index)</literal> is not a + conflict: the index scan is enforced and the prohibition on sequential scans + is also satisfied. + </para> + + <para> + Note that <literal>NO_SEQ_SCAN((x y))</literal> and similar forms with a + parenthesized sublist are not valid syntax for negative scan tags, because + scan methods apply to individual relations, not join products. Negative scan + tags use the same target syntax as their positive counterparts: a + flat list of relation identifiers. + </para> + + </sect3> + <sect3 id="pgplanadvice-join-order"> <title>Join Order Advice</title> <synopsis> @@ -433,6 +485,83 @@ join_method_name(<replaceable>join_method_item</replaceable> [ ... ]) </sect3> + <sect3 id="pgplanadvice-no-join-method"> + <title>Negative Join Method Advice</title> + <synopsis> +no_join_method_name(<replaceable>join_method_item</replaceable> [ ... ]) + +<phrase>where <replaceable>no_join_method_name</replaceable> is:</phrase> + +{ NO_HASH_JOIN | + NO_NESTED_LOOP | NO_NESTED_LOOP_PLAIN | NO_NESTED_LOOP_MATERIALIZE | NO_NESTED_LOOP_MEMOIZE | + NO_MERGE_JOIN | NO_MERGE_JOIN_PLAIN | NO_MERGE_JOIN_MATERIALIZE } + +<phrase>and <replaceable>join_method_item</replaceable> is:</phrase> + +{ <replaceable>advice_target</replaceable> | +( <replaceable>advice_target</replaceable> [ ... ] ) }</synopsis> + + <para> + These tags are the negative counterparts of the positive join method tags. + A negative join method tag specifies that the named join method (or family + of join methods) should <emphasis>not</emphasis> be used when the specified + relation or set of relations appears on the inner side of a join. The + planner will choose any other eligible join method instead. + </para> + + <para> + Both a coarser group form and a finer specific form are available for + nested-loop and merge-join. <literal>NO_NESTED_LOOP(t)</literal> disables + all three nested-loop variants (<literal>NESTED_LOOP_PLAIN</literal>, + <literal>NESTED_LOOP_MATERIALIZE</literal>, and + <literal>NESTED_LOOP_MEMOIZE</literal>), while + <literal>NO_NESTED_LOOP_PLAIN(t)</literal> disables only the plain variant, + leaving the other two available. Similarly, + <literal>NO_MERGE_JOIN(t)</literal> disables both merge-join variants, while + <literal>NO_MERGE_JOIN_PLAIN(t)</literal> and + <literal>NO_MERGE_JOIN_MATERIALIZE(t)</literal> target individual variants. + <literal>NO_HASH_JOIN</literal> has only one form since there is only one + hash-join variant. + </para> + + <para> + Like positive join method tags, negative join method tags accept + parenthesized sublists specifying a set of relations that should appear + together on the inner side. For example, + <literal>NO_HASH_JOIN((a b))</literal> means that the join product of + <literal>a</literal> and <literal>b</literal> should not appear together on + the inner side of a hash join. + </para> + + <para> + Negative join method tags impose the same join-order constraint as their + positive counterparts: the specified relation (or join product) cannot + become the driving table. For example, <literal>NO_HASH_JOIN(t)</literal> + implies that <literal>t</literal> cannot be the outermost relation in the + join, exactly as <literal>HASH_JOIN(t)</literal> would. Think of + <literal>NO_HASH_JOIN(t)</literal> as saying + <quote>use anything but a hash join, but keep <literal>t</literal> on the + inner side.</quote> + </para> + + <para> + Multiple negative join method tags for the same relation can be combined + freely. For example, <literal>NO_HASH_JOIN(t) NO_NESTED_LOOP(t)</literal> + restricts the planner to merge-join when <literal>t</literal> is on the + inner side. A negative tag for one method does not conflict with a positive + tag for a different method: <literal>NO_HASH_JOIN(t) + NESTED_LOOP_PLAIN(t)</literal> is not a conflict. + </para> + + <para> + A negative tag and a positive tag specifying the same join method are in + conflict. For example, <literal>HASH_JOIN(t) NO_HASH_JOIN(t)</literal> + contradicts itself, and both entries will be marked + <literal>conflicting</literal>. + </para> + + </sect3> + <sect3 id="pgplanadvice-partitionwise"> <title>Partitionwise Advice</title> <synopsis> -- 2.45.1
