Hello,
The attached patch adds negative scan- and join-method advice to
pg_plan_advice. These tags are the complement of the existing
positive ones:
NO_SEQ_SCAN, NO_INDEX_SCAN, NO_INDEX_ONLY_SCAN,
NO_BITMAP_HEAP_SCAN, NO_TID_SCAN
NO_HASH_JOIN, NO_NESTED_LOOP (+ _PLAIN/_MATERIALIZE/_MEMOIZE),
NO_MERGE_JOIN (+ _PLAIN/_MATERIALIZE)
Each NO_ tag forbids the corresponding strategy for the named
relation, so the planner is free to choose any other one that is
eligible. In many cases this is more convenient than listing all the
methods you want, when in fact you only need to exclude a single one.
Some points about the design:
- The NO_ join tags keep the same join-order constraint as their
positive counterparts. NO_HASH_JOIN(x) means "use hash join or
merge join with x on the inner side", so x cannot become the
driving relation.
- Several NO_ tags for the same relation can be combined without
problem. A NO_ tag and a positive tag for the same method are in
conflict (both are marked as conflicting and neither is applied); a
NO_ tag and a positive tag for different methods are compatible.
- The scan NO_ tags use simple_target_list (no sublists), and the
join NO_ tags use generic_target_list, exactly as their positive
counterparts do.
Regression tests (no_scan, no_join) and documentation are included.
The patch applies on master and is pgindent-clean.
I would be glad to receive any feedback.
Thank you,
Florin
From 76628bd8aee1b8977e416f45162aca3795e75ba9 Mon Sep 17 00:00:00 2001
From: Florin Irion <[email protected]>
Date: Thu, 18 Jun 2026 14:28:50 +0200
Subject: [PATCH v1] 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 d016723794d..2dfe36fef4c 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
EXTRA_INSTALL = contrib/tsm_system_time
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
