On 29/07/2026 09:12, Neil Chen wrote:
Hi Florin,
While testing v3, I found two behaviors that look unintended.
1. False conflict between BITMAP_HEAP_SCAN and NO_INDEX_ONLY_SCAN
This is separate from the earlier discussion about whether
NO_INDEX_ONLY_SCAN should clear PGS_CONSIDER_INDEXONLY. I agree that it
needs to do so for enforcement. The problem is that the same bit is also
used for conflict detection.
BITMAP_HEAP_SCAN uses:
|PGS_BITMAPSCAN | PGS_CONSIDER_INDEXONLY|
while NO_INDEX_ONLY_SCAN forbids:
|PGS_INDEXONLYSCAN | PGS_CONSIDER_INDEXONLY|
Therefore this check reports a conflict due only to the shared
PGS_CONSIDER_INDEXONLY bit:
|if (scan_type != all_scan_mask && (scan_type & no_scan_mask) != 0)
scan_pos_neg_conflict = true;|
For example:
|SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(no_scan_table)
NO_INDEX_ONLY_SCAN(no_scan_table)'; EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM no_scan_table WHERE a = 1;|
reports:
|BITMAP_HEAP_SCAN(no_scan_table) /* matched, conflicting, failed */
NO_INDEX_ONLY_SCAN(no_scan_table) /* matched, conflicting */|
These tags are semantically compatible: a Bitmap Heap Scan is not an Index
Only Scan. It seems that PGS_CONSIDER_INDEXONLY is an enforcement detail,
not a scan method for conflict purposes.
Would it make sense to use separate semantic and enforcement masks, with
only the former used for conflict detection?
2. A conflict cancels unrelated negative tags
A single scan_pos_neg_conflict or jm_pos_neg_conflict boolean is used for
all negative tags on the target. Once set, every negative tag is marked
conflicting and the entire no_scan_mask or no_join_mask is not applied.
For example:
|SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(no_scan_table)
NO_SEQ_SCAN(no_scan_table) NO_BITMAP_HEAP_SCAN(no_scan_table)';
produces:
|Bitmap Heap Scan on no_scan_table Recheck Cond: (b > 'some text
8'::text) -> Bitmap Index Scan on no_scan_table_b Index Cond: (b >
'some text 8'::text) Supplied Plan Advice: SEQ_SCAN(no_scan_table) /*
matched, conflicting, failed */ NO_SEQ_SCAN(no_scan_table) /* matched,
conflicting */ NO_BITMAP_HEAP_SCAN(no_scan_table) /* matched,
conflicting, failed */||
The SEQ_SCAN/NO_SEQ_SCAN pair conflicts, but NO_BITMAP_HEAP_SCAN does not.
Nevertheless, it is also marked conflicting and is not enforced, allowing
the final plan to use a Bitmap Heap Scan.
The join case behaves the same way:
|SET LOCAL pg_plan_advice.advice = 'MERGE_JOIN_PLAIN(d)
NO_MERGE_JOIN(d) NO_HASH_JOIN(d)';
produces:
|Hash Join Hash Cond: (f.dim_id = d.id <http://d.id>) -> Seq Scan on
no_join_fact f -> Hash -> Seq Scan on no_join_dim d Supplied Plan
Advice: MERGE_JOIN_PLAIN(d) /* matched, conflicting, failed */
NO_MERGE_JOIN(d) /* matched, conflicting */ NO_HASH_JOIN(d) /*
matched, conflicting, failed */||
NO_HASH_JOIN(d) is marked conflicting and left unenforced even though it
does not conflict with MERGE_JOIN_PLAIN(d), so the final plan can
still use
a Hash Join.
Would it be better to detect conflicts per method, mark only the involved
tags as conflicting, and continue applying the enforcement masks of
unrelated negative tags?
Regression tests for both combinations would be useful.
Hi,
Thank you for reviewing,all confirmed, and fixed in v4.
1. False conflict via PGS_CONSIDER_INDEXONLY: that's an enforcement
detail, not a scan method. Added pgpa_scan_semantic_mask(), which strips
PGS_CONSIDER_INDEXONLY before comparing tags for conflict purposes.
BITMAP_HEAP_SCAN(t) NO_INDEX_ONLY_SCAN(t) now comes back clean, no conflict.
2. Conflict cancelling unrelated NO_ tags: Replaced the single
all-or-nothing scan_pos_neg_conflict/jm_pos_neg_conflict gate with
per-tag conflict tracking. Each NO_ tag is now checked independently;
only the ones that actually clash get marked conflicting and skipped,
everything else still gets enforced. Verified with your examples —
NO_BITMAP_HEAP_SCAN and NO_HASH_JOIN(d) now apply despite an unrelated
conflict on a different method.
3. Also caught while testing: index-selection side effect on conflict.
Separately, found that the "enforce choice of index" block only checked
!scan_pos_conflict, not !scan_pos_neg_conflict. So INDEX_SCAN(t idx)
NO_INDEX_SCAN(t) — a genuine conflict, correctly left unenforced at the
mask level — was still silently disabling every other index on t,
steering the plan toward idx anyway. Fixed by adding the missing check.From 94fa7d6d742298cda36a4c91b86167af76fb36af Mon Sep 17 00:00:00 2001
From: Florin Irion <[email protected]>
Date: Thu, 18 Jun 2026 14:28:50 +0200
Subject: [PATCH v4] pg_plan_advice: Add NO_SCAN and NO_JOIN_METHOD tags
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,
including the join-order constraint: NO_HASH_JOIN(x) means x may
not appear on the outer (driving) side, exactly as HASH_JOIN(x)
would.
Scan NO_ tags use simple_target_list (sublists invalid); join NO_
tags use generic_target_list (sublists allowed). Only the NO_ scan
tags need scanner and parser changes as a result; the NO_ join
tags fall through to the existing generic tag resolution.
Conflict detection compares each tag's semantic scan/join method
rather than its enforcement mask, since some enforcement bits (e.g.
PGS_CONSIDER_INDEXONLY) are shared for unrelated reasons and would
otherwise cause unrelated tags to look like they conflict. Each NO_
tag is checked against the positive tag independently, so a
conflict on one method never suppresses enforcement of an unrelated
NO_ tag on the same relation or join, including the index-selection
side effect of INDEX_SCAN/INDEX_ONLY_SCAN advice.
---
contrib/pg_plan_advice/Makefile | 4 +-
contrib/pg_plan_advice/expected/no_join.out | 317 ++++++++++++++++++++
contrib/pg_plan_advice/expected/no_scan.out | 283 +++++++++++++++++
contrib/pg_plan_advice/meson.build | 2 +
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 | 266 ++++++++++++++--
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 | 101 +++++++
contrib/pg_plan_advice/sql/no_scan.sql | 132 ++++++++
doc/src/sgml/pgplanadvice.sgml | 129 ++++++++
14 files changed, 1377 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..c46b1ac54e9
--- /dev/null
+++ b/contrib/pg_plan_advice/expected/no_join.out
@@ -0,0 +1,317 @@
+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;
+-- A conflict on one method must not cancel enforcement of an unrelated NO_
+-- tag: MERGE_JOIN_PLAIN(d) conflicts with NO_MERGE_JOIN(d) here, but
+-- NO_HASH_JOIN(d) does not, so it still applies.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'MERGE_JOIN_PLAIN(d) NO_MERGE_JOIN(d)
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:
+ MERGE_JOIN_PLAIN(d) /* matched, conflicting, failed */
+ NO_MERGE_JOIN(d) /* matched, conflicting */
+ 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)
+(17 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..84436fcd6b2
--- /dev/null
+++ b/contrib/pg_plan_advice/expected/no_scan.out
@@ -0,0 +1,283 @@
+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;
+-- No conflict: BITMAP_HEAP_SCAN and NO_INDEX_ONLY_SCAN only share the
+-- PGS_CONSIDER_INDEXONLY enforcement bit, not an actual scan method.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(no_scan_table)
NO_INDEX_ONLY_SCAN(no_scan_table)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_table WHERE b > 'some text 8';
+ QUERY PLAN
+---------------------------------------------------
+ Bitmap Heap Scan on no_scan_table
+ Recheck Cond: (b > 'some text 8'::text)
+ -> Bitmap Index Scan on no_scan_table_b
+ Index Cond: (b > 'some text 8'::text)
+ Supplied Plan Advice:
+ BITMAP_HEAP_SCAN(no_scan_table) /* matched */
+ NO_INDEX_ONLY_SCAN(no_scan_table) /* matched */
+ Generated Plan Advice:
+ BITMAP_HEAP_SCAN(no_scan_table)
+ NO_GATHER(no_scan_table)
+(10 rows)
+
+COMMIT;
+-- A conflict on one method must not cancel enforcement of an unrelated NO_
+-- tag: NO_SEQ_SCAN conflicts with SEQ_SCAN here, but NO_BITMAP_HEAP_SCAN
+-- does not, so it still applies.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(no_scan_table)
NO_SEQ_SCAN(no_scan_table) 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:
+ SEQ_SCAN(no_scan_table) /* matched, conflicting */
+ NO_SEQ_SCAN(no_scan_table) /* matched, conflicting, failed */
+ NO_BITMAP_HEAP_SCAN(no_scan_table) /* matched */
+ Generated Plan Advice:
+ SEQ_SCAN(no_scan_table)
+ NO_GATHER(no_scan_table)
+(9 rows)
+
+COMMIT;
+-- A conflicting INDEX_SCAN request must not have the side effect of
+-- disabling every other index: with two equivalent indexes, an
+-- INDEX_SCAN/NO_INDEX_SCAN conflict on the relation must leave the
+-- planner's natural index choice alone.
+CREATE TABLE no_scan_idx_conflict (a int)
+ WITH (autovacuum_enabled = false);
+INSERT INTO no_scan_idx_conflict SELECT g FROM generate_series(1, 100000) g;
+CREATE INDEX no_scan_idx_conflict_a1 ON no_scan_idx_conflict (a);
+CREATE INDEX no_scan_idx_conflict_a2 ON no_scan_idx_conflict (a);
+VACUUM ANALYZE no_scan_idx_conflict;
+-- Baseline: which index does the planner naturally pick?
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_idx_conflict ORDER BY a LIMIT 5;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Limit
+ -> Index Only Scan using no_scan_idx_conflict_a2 on no_scan_idx_conflict
+ Generated Plan Advice:
+ INDEX_ONLY_SCAN(no_scan_idx_conflict public.no_scan_idx_conflict_a2)
+ NO_GATHER(no_scan_idx_conflict)
+(5 rows)
+
+-- INDEX_SCAN names the other index, and NO_INDEX_SCAN forbids index scans
+-- entirely on the same relation, so the two conflict; neither should have
+-- any effect, and in particular the un-named index must not be disabled.
+BEGIN;
+SET LOCAL pg_plan_advice.advice =
+ 'INDEX_SCAN(no_scan_idx_conflict no_scan_idx_conflict_a1)
NO_INDEX_SCAN(no_scan_idx_conflict)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_idx_conflict ORDER BY a LIMIT 5;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Limit
+ -> Index Only Scan using no_scan_idx_conflict_a2 on no_scan_idx_conflict
+ Supplied Plan Advice:
+ INDEX_SCAN(no_scan_idx_conflict no_scan_idx_conflict_a1) /* matched,
conflicting, failed */
+ NO_INDEX_SCAN(no_scan_idx_conflict) /* matched, conflicting */
+ Generated Plan Advice:
+ INDEX_ONLY_SCAN(no_scan_idx_conflict public.no_scan_idx_conflict_a2)
+ NO_GATHER(no_scan_idx_conflict)
+(8 rows)
+
+COMMIT;
+DROP TABLE no_scan_idx_conflict;
+-- 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/meson.build
b/contrib/pg_plan_advice/meson.build
index bbab676be31..ce7eaf631f5 100644
--- a/contrib/pg_plan_advice/meson.build
+++ b/contrib/pg_plan_advice/meson.build
@@ -57,6 +57,8 @@ tests += {
'gather',
'join_order',
'join_strategy',
+ 'no_join',
+ 'no_scan',
'partitionwise',
'prepared',
'scan',
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..61cc2fb9a45 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;
+ Bitmapset *no_jm_conflict_indexes = NULL;
Bitmapset *sj_permit_indexes = NULL;
Bitmapset *sj_deny_indexes = NULL;
@@ -1043,12 +1050,40 @@ 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);
+ continue;
+ }
+ }
+
/* Handle semijoin uniqueness advice. */
if (entry->tag == PGPA_TAG_SEMIJOIN_UNIQUE ||
entry->tag == PGPA_TAG_SEMIJOIN_NON_UNIQUE)
@@ -1118,13 +1153,47 @@ 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. Check each
NO_
+ * tag independently, so that a clash on one method doesn't cause an
+ * unrelated NO_ tag for a different method to be treated as conflicting
+ * or left unenforced.
+ */
+ if (join_mask != 0)
+ {
+ int j = -1;
+
+ while ((j = bms_next_member(no_jm_indexes, j)) >= 0)
+ {
+ uint64 my_no_join_mask;
+
+ my_no_join_mask =
+
pgpa_no_join_mask_from_advice_tag(pjs->join_entries[j].tag);
+ if ((join_mask & my_no_join_mask) != 0)
+ {
+ jm_pos_neg_conflict = true;
+ no_jm_conflict_indexes =
+ bms_add_member(no_jm_conflict_indexes,
j);
+ }
+ }
+
+ if (no_jm_conflict_indexes != NULL)
+ {
+ pgpa_trove_set_flags(pjs->join_entries, jm_indexes,
+
PGPA_FB_CONFLICTING);
+ pgpa_trove_set_flags(pjs->join_entries,
no_jm_conflict_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 +1212,30 @@ 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_jm_indexes != NULL)
+ {
+ uint64 enforce_no_join_mask = 0;
+ int j = -1;
+
+ while ((j = bms_next_member(no_jm_indexes, j)) >= 0)
+ {
+ if (bms_is_member(j, no_jm_conflict_indexes))
+ continue;
+ enforce_no_join_mask |=
+
pgpa_no_join_mask_from_advice_tag(pjs->join_entries[j].tag);
+ }
+ *pgs_mask_p &= ~enforce_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 +1261,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 +1731,50 @@ 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;
+ }
+}
+
+/*
+ * Strip enforcement-only bits from a scan-method mask, leaving only the
+ * bit(s) that actually identify a scan method.
+ *
+ * PGS_CONSIDER_INDEXONLY is included in the enforcement mask for both
+ * BITMAP_HEAP_SCAN (to work around the historical quirk described in
+ * pgpa_planner_apply_scan_advice) and INDEX_ONLY_SCAN/NO_INDEX_ONLY_SCAN (to
+ * make a would-be Index Only Scan degrade to, or be excluded in favor of, a
+ * plain Index Scan). It doesn't, by itself, identify any scan method, so it
+ * must be excluded before comparing two tags to see whether they specify the
+ * same or conflicting scan methods; otherwise, tags for genuinely unrelated
+ * methods that merely happen to share this bit would look like they conflict.
+ */
+static uint64
+pgpa_scan_semantic_mask(uint64 mask)
+{
+ return mask & ~PGS_CONSIDER_INDEXONLY;
+}
+
/*
* Apply scan advice to a RelOptInfo.
*/
@@ -1637,9 +1793,13 @@ 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;
+ Bitmapset *no_scan_conflict_indexes = NULL;
uint64 gather_mask = 0;
uint64 scan_type = all_scan_mask; /* sentinel: no advice
yet */
@@ -1648,8 +1808,20 @@ pgpa_planner_apply_scan_advice(RelOptInfo *rel,
{
pgpa_trove_entry *my_entry = &scan_entries[i];
uint64 my_scan_type = all_scan_mask;
+ uint64 my_no_scan_mask;
- /* Translate our advice tags to a scan strategy advice value. */
+ /*
+ * 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_indexes = bms_add_member(no_scan_indexes, i);
+ continue;
+ }
+
+ /* 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 +1862,47 @@ 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. Compare semantic masks (i.e.
+ * excluding enforcement-only bits such as PGS_CONSIDER_INDEXONLY), and
+ * check each NO_ tag independently, so that a clash on one method
doesn't
+ * cause an unrelated NO_ tag for a different method to be treated as
+ * conflicting or left unenforced.
+ */
+ if (scan_type != all_scan_mask)
+ {
+ uint64 scan_type_semantic =
pgpa_scan_semantic_mask(scan_type);
+ int j = -1;
+
+ while ((j = bms_next_member(no_scan_indexes, j)) >= 0)
+ {
+ uint64 my_no_scan_semantic;
+
+ my_no_scan_semantic =
+
pgpa_scan_semantic_mask(pgpa_no_scan_mask_from_advice_tag(scan_entries[j].tag));
+ if ((scan_type_semantic & my_no_scan_semantic) != 0)
+ {
+ scan_pos_neg_conflict = true;
+ no_scan_conflict_indexes =
+
bms_add_member(no_scan_conflict_indexes, j);
+ }
+ }
+ }
+
/* Scrutinize available gather-related and partitionwise advice. */
i = -1;
while ((i = bms_next_member(rel_indexes, i)) >= 0)
@@ -1725,7 +1925,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 +1978,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_pos_neg_conflict
&&
(scan_entry->tag == PGPA_TAG_INDEX_SCAN ||
scan_entry->tag == PGPA_TAG_INDEX_ONLY_SCAN))
{
@@ -1822,14 +2022,26 @@ 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. Only the specific NO_ tags
+ * whose method actually conflicts with the requested positive scan
+ * method are marked conflicting; unrelated NO_ tags for the same
+ * relation are unaffected.
+ */
+ pgpa_trove_set_flags(scan_entries, no_scan_indexes,
+ PGPA_FB_MATCH_PARTIAL |
PGPA_FB_MATCH_FULL);
+ pgpa_trove_set_flags(scan_entries, no_scan_conflict_indexes,
+ PGPA_FB_CONFLICTING);
+
/*
* 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 +2059,22 @@ 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_indexes != NULL)
+ {
+ uint64 enforce_no_scan_mask = 0;
+ int j = -1;
+
+ while ((j = bms_next_member(no_scan_indexes, j)) >= 0)
+ {
+ if (bms_is_member(j, no_scan_conflict_indexes))
+ continue;
+ enforce_no_scan_mask |=
+
pgpa_no_scan_mask_from_advice_tag(scan_entries[j].tag);
+ }
+ rel->pgs_mask &= ~(all_scan_mask & enforce_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 15d67cd4896..c10d5edf537 100644
--- a/contrib/pg_plan_advice/pgpa_scanner.l
+++ b/contrib/pg_plan_advice/pgpa_scanner.l
@@ -130,7 +130,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 7b299440d4d..08e0883fac1 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..518820b2d31
--- /dev/null
+++ b/contrib/pg_plan_advice/sql/no_join.sql
@@ -0,0 +1,101 @@
+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;
+
+-- A conflict on one method must not cancel enforcement of an unrelated NO_
+-- tag: MERGE_JOIN_PLAIN(d) conflicts with NO_MERGE_JOIN(d) here, but
+-- NO_HASH_JOIN(d) does not, so it still applies.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'MERGE_JOIN_PLAIN(d) NO_MERGE_JOIN(d)
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;
+
+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..a383723ac2c
--- /dev/null
+++ b/contrib/pg_plan_advice/sql/no_scan.sql
@@ -0,0 +1,132 @@
+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;
+
+-- No conflict: BITMAP_HEAP_SCAN and NO_INDEX_ONLY_SCAN only share the
+-- PGS_CONSIDER_INDEXONLY enforcement bit, not an actual scan method.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'BITMAP_HEAP_SCAN(no_scan_table)
NO_INDEX_ONLY_SCAN(no_scan_table)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_table WHERE b > 'some text 8';
+COMMIT;
+
+-- A conflict on one method must not cancel enforcement of an unrelated NO_
+-- tag: NO_SEQ_SCAN conflicts with SEQ_SCAN here, but NO_BITMAP_HEAP_SCAN
+-- does not, so it still applies.
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(no_scan_table)
NO_SEQ_SCAN(no_scan_table) NO_BITMAP_HEAP_SCAN(no_scan_table)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_table WHERE b > 'some text 8';
+COMMIT;
+
+-- A conflicting INDEX_SCAN request must not have the side effect of
+-- disabling every other index: with two equivalent indexes, an
+-- INDEX_SCAN/NO_INDEX_SCAN conflict on the relation must leave the
+-- planner's natural index choice alone.
+CREATE TABLE no_scan_idx_conflict (a int)
+ WITH (autovacuum_enabled = false);
+INSERT INTO no_scan_idx_conflict SELECT g FROM generate_series(1, 100000) g;
+CREATE INDEX no_scan_idx_conflict_a1 ON no_scan_idx_conflict (a);
+CREATE INDEX no_scan_idx_conflict_a2 ON no_scan_idx_conflict (a);
+VACUUM ANALYZE no_scan_idx_conflict;
+
+-- Baseline: which index does the planner naturally pick?
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_idx_conflict ORDER BY a LIMIT 5;
+
+-- INDEX_SCAN names the other index, and NO_INDEX_SCAN forbids index scans
+-- entirely on the same relation, so the two conflict; neither should have
+-- any effect, and in particular the un-named index must not be disabled.
+BEGIN;
+SET LOCAL pg_plan_advice.advice =
+ 'INDEX_SCAN(no_scan_idx_conflict no_scan_idx_conflict_a1)
NO_INDEX_SCAN(no_scan_idx_conflict)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+ SELECT * FROM no_scan_idx_conflict ORDER BY a LIMIT 5;
+COMMIT;
+
+DROP TABLE no_scan_idx_conflict;
+
+-- 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
