From: Florin Irion <irionr@gmail.com>
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
Cc: robertmhaas@gmail.com
Subject: Re: pg_plan_advice: add NO_ scan and join method tags
Date: Mon, 27 Jul 2026 18:45:08 +0200
Message-ID: <9ba4c7da-06ee-4a5a-9479-9b1076cfc675@gmail.com> (raw)
In-Reply-To: <87qzl5pxuz.fsf@gmail.com>
References: <123bbb65-ae76-4112-b33d-864aca745b7b@gmail.com>
<87qzl5pxuz.fsf@gmail.com>
Hello,
On 14/07/2026 14:19, Jonathan Gonzalez V. wrote:
> I was trying to measure the test coverage of this patch and I found that
> the meson configuration wasn't updated, I think you're missing something
> like this:
>
> --- 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',
Thanks for catching the meson.build gap — confirmed, no_join/no_scan
were added to the Makefile's REGRESS list but never mirrored into
meson.build. Fixed.
>
> After the tests run only two cases were not tested NO_MERGE_JOIN and
> NO_NESTED_LOOP, but these supposed to be tested, well I'll dig more on
> this one a second pass of the review.
On the coverage gap for NO_MERGE_JOIN/NO_NESTED_LOOP: I instrumented
both switch cases in pgpa_walker.c with log probes and reran no_join.sql
— both fire. Concretely: NO_NESTED_LOOP(f) (sql/no_join.sql:38) and the
stacked NO_HASH_JOIN(f) NO_NESTED_LOOP(f) case (line 52) both hit the
PGPA_TAG_NO_NESTED_LOOP case; NO_MERGE_JOIN(d) (line 45) hits
PGPA_TAG_NO_MERGE_JOIN. So ISTM the underlying advice-tag handling is
exercised.
>
> During the check, this came to my attention:
>
> + case PGPA_TAG_NO_INDEX_ONLY_SCAN:
> + return PGS_INDEXONLYSCAN | PGS_CONSIDER_INDEXONLY;
>
> Reading the comments I got this:
>
> When PGS_CONSIDER_INDEXONLY is
> unset, we don't even consider index-only scans, and any such scans that
> would have been generated become index scans instead. On the other hand,
> unsetting PGS_INDEXSCAN or PGS_INDEXONLYSCAN causes generated paths of the
> corresponding types to be marked as disabled.
>
> The message is a bit confusing but, I think that
> `PGS_INDEXONLY` should be the only option here? Otherwise, my
> understanding is that it will disable also PGS_INDEXSCAN, which is in
> the next line to be disable.
On PGS_CONSIDER_INDEXONLY: I think this traces back to the pathnodes.h
comment:
▎ When PGS_CONSIDER_INDEXONLY is unset, we don't even consider
index-only scans, and any such scans that would have been generated
become index scans instead. On the other hand, unsetting PGS_INDEXSCAN
or PGS_INDEXONLYSCAN causes generated paths of the corresponding types
to be marked as disabled.
That comment is actually describing the behavior we want.
NO_INDEX_ONLY_SCAN's mask clears PGS_INDEXONLYSCAN |
PGS_CONSIDER_INDEXONLY, leaving PGS_INDEXSCAN untouched. Per the
comment, clearing PGS_CONSIDER_INDEXONLY doesn't disable a path — it
changes what check_index_only() builds in the first place, converting
the would-be Index-Only Scan into a regular Index Scan path. Since we
never touch PGS_INDEXSCAN, that converted path stays enabled. Drop
PGS_CONSIDER_INDEXONLY from the mask (your suggestion) and
check_index_only() still returns true, so the planner builds an actual
(disabled) Index-Only Scan with no regular Index Scan alternative for
that index at all.
I confirmed this with the mask reduced to just PGS_INDEXONLYSCAN, the
no_scan test that expects NO_INDEX_ONLY_SCAN to fall back to a plain
Index Scan instead produced a Bitmap Heap Scan. So ISTM the current code
is correct.
attaching v3 with the meson.build change and rebased on current master.
Cheers,
Florin
---
www.enterprisedb.com
From 58479e9d7a0eca0aae74ce32a09bec1029ac5e5e Mon Sep 17 00:00:00 2001
From: Florin Irion <florin.irion@enterprisedb.com>
Date: Thu, 18 Jun 2026 14:28:50 +0200
Subject: [PATCH v3] 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/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 | 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 +++++++++
14 files changed, 1127 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/Makefileindex 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.outnew file mode 100644index 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.outnew file mode 100644index 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/meson.build b/contrib/pg_plan_advice/meson.buildindex 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.cindex 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.hindex 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.yindex 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.cindex 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.lindex 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.cindex 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.cindex 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.sqlnew file mode 100644index 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.sqlnew file mode 100644index 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.sgmlindex 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
Attachments:
[text/plain] v3-0001-pg_plan_advice-Add-NO_SCAN-and-NO_JOIN_METHOD-tag.patch (54.4K, ../9ba4c7da-06ee-4a5a-9479-9b1076cfc675@gmail.com/2-v3-0001-pg_plan_advice-Add-NO_SCAN-and-NO_JOIN_METHOD-tag.patch)
download | inline diff:
From 58479e9d7a0eca0aae74ce32a09bec1029ac5e5e Mon Sep 17 00:00:00 2001
From: Florin Irion <florin.irion@enterprisedb.com>
Date: Thu, 18 Jun 2026 14:28:50 +0200
Subject: [PATCH v3] 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/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 | 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 +++++++++
14 files changed, 1127 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/Makefileindex 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.outnew file mode 100644index 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.outnew file mode 100644index 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/meson.build b/contrib/pg_plan_advice/meson.buildindex 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.cindex 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.hindex 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.yindex 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.cindex 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.lindex 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.cindex 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.cindex 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.sqlnew file mode 100644index 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.sqlnew file mode 100644index 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.sgmlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: irionr@gmail.com, jonathan.abdiel@gmail.com, pgsql-hackers@lists.postgresql.org, robertmhaas@gmail.com
Subject: Re: pg_plan_advice: add NO_ scan and join method tags
In-Reply-To: <9ba4c7da-06ee-4a5a-9479-9b1076cfc675@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox