BUG #19838: pg_plan_advice: generated GATHER advice fails when supplied back

The following bug has been logged on the website:

Bug reference: 19838
Logged by: Atish Prakash
Email address: 125atishprak(at)gmail(dot)com
PostgreSQL version: 19rc1
Operating system: Linux x86_64 (kernel 7.0.0, glibc 2.43)
Description:

contrib/pg_plan_advice/README says generated advice is "round-trip safe":
fed
back into a future planning cycle, it "should successfully enforce the same
planning decision that led to it being generated". In the cases below, with
no
DDL or settings change between the two steps, supplied advice is reported as
"matched, failed" and the plan changes. Query results are not affected.

Expected: every item reports "matched" and the plan is unchanged.

Actual, case 1, SELECT DISTINCT t2 FROM p t1 JOIN p t2 ON t1.a = t2.a WHERE
t1.c:

Step 1 has HashAggregate over Hash Join, with a Gather under each join
input.
It generates GATHER(t1 t2).
Step 2 has HashAggregate over Gather over HashAggregate over Parallel
Hash Join.
It reports:
GATHER(t1) /* matched, failed */
GATHER(t2) /* matched, failed */
and generates GATHER((t1 t2)).

Case 2 shows the same plan change on a different shape: GATHER on a single
relation, with DISTINCT on an output that is not a whole row.

Case 3 is different and may have a different cause. It needs enable_hashjoin
and
enable_mergejoin off, so the original plan is already "Disabled: true". The
FULL
JOIN inputs are swapped on replay, and JOIN_ORDER and MERGE_JOIN_PLAIN
report
"matched, failed". If advice is not meant to round-trip for disabled plans,
please treat this case as expected behaviour.

The same queries round-trip correctly with default settings. In case 1,
these
variants also round-trip correctly: SELECT DISTINCT t2.a, unpartitioned
tables,
one partitioned and one plain table, and count(*) or GROUP BY in place of
DISTINCT.

I have not found the cause. In cases 1 and 2 the replayed plan moves Gather
above
a partial HashAggregate, so my guess is that the advice does not rule out
that
path. I have no patch. I found this with a SQL fuzzer.

Versions tested, with identical output:
- 19rc1 (REL_19_STABLE at 0b1eb8b917)
- master at 72b9498df9, plus my unrelated uncommitted patch to ruleutils.c
Platform: Linux 7.0.0-42-generic x86_64, glibc 2.43, Intel i9-13900HX, 14
GiB RAM
Client and server: psql and postgres from the same build, Unix socket, psql
-X
Build: clang 21.1.8, CFLAGS=-O1 -g, --enable-cassert --enable-debug
--without-icu --without-readline --without-libxml --without-openssl
Non-default server settings: fsync=off, shared_buffers=48MB, lc_messages=C
Install: built from git with no other deviations from the instructions

Reproducer, run with psql -X -f in a new database:

LOAD 'pg_plan_advice';

\echo ===== Case 1: GATHER over a join, whole-row DISTINCT
BEGIN;
SET LOCAL parallel_setup_cost = 0;
SET LOCAL parallel_tuple_cost = 0;
SET LOCAL min_parallel_table_scan_size = 0;
SET LOCAL min_parallel_index_scan_size = 0;
CREATE TABLE p (a int, c bool) PARTITION BY RANGE (a);
CREATE TABLE p0 PARTITION OF p FOR VALUES FROM (MINVALUE) TO (3);
CREATE TABLE pd PARTITION OF p DEFAULT;
ANALYZE p;
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT DISTINCT t2 FROM p t1 JOIN p t2 ON t1.a = t2.a WHERE t1.c;
SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(t1 t2) HASH_JOIN(t2)
SEQ_SCAN(t1/public.p0 t1/public.pd t2/public.p0 t2/public.pd)
PARTITIONWISE(t1 t2) GATHER(t1 t2) NO_GATHER(t1/public.p0 t1/public.pd
t2/public.p0 t2/public.pd)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT DISTINCT t2 FROM p t1 JOIN p t2 ON t1.a = t2.a WHERE t1.c;
ROLLBACK;

\echo ===== Case 2: GATHER on one relation, DISTINCT on a non-whole-row
output
BEGIN;
SET LOCAL parallel_setup_cost = 0;
SET LOCAL parallel_tuple_cost = 0;
SET LOCAL min_parallel_table_scan_size = 0;
SET LOCAL min_parallel_index_scan_size = 0;
CREATE TABLE tb0 (a int4, b int8, c date, e0 numeric) PARTITION BY RANGE
(a);
CREATE TABLE tb0_p0 PARTITION OF tb0 FOR VALUES FROM (MINVALUE) TO (3);
CREATE TABLE tb0_p1 PARTITION OF tb0 FOR VALUES FROM (3) TO (10);
CREATE TABLE tb0_d PARTITION OF tb0 DEFAULT;
ANALYZE tb0;
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT DISTINCT o3 FROM unnest('{{1,2},{3,4}}'::int4[]) JOIN (SELECT CASE
WHEN NULL THEN 1::int8 ELSE NULL END o3, NULL, 'abc' FROM tb0 t2 WHERE
NULLIF(t2.c, t2.c) >= '2024-02-29'::date) ON NULL RIGHT JOIN tb0 ON NULL;
SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(tb0 {unnest t2})
NESTED_LOOP_PLAIN((unnest t2)) SEQ_SCAN(tb0/public.tb0_p0 tb0/public.tb0_p1
tb0/public.tb0_d) PARTITIONWISE(tb0) GATHER(tb0) NO_GATHER(unnest t2
tb0/public.tb0_p0 tb0/public.tb0_p1 tb0/public.tb0_d)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT DISTINCT o3 FROM unnest('{{1,2},{3,4}}'::int4[]) JOIN (SELECT CASE
WHEN NULL THEN 1::int8 ELSE NULL END o3, NULL, 'abc' FROM tb0 t2 WHERE
NULLIF(t2.c, t2.c) >= '2024-02-29'::date) ON NULL RIGHT JOIN tb0 ON NULL;
ROLLBACK;

\echo ===== Case 3: JOIN_ORDER on a FULL JOIN when every usable join method
is disabled
BEGIN;
SET LOCAL enable_hashjoin = off;
SET LOCAL enable_mergejoin = off;
CREATE TABLE a (x int PRIMARY KEY, y int GENERATED ALWAYS AS (x + 1)
STORED);
CREATE TABLE b (x int, y int GENERATED ALWAYS AS (x + 1) STORED) PARTITION
BY LIST (x);
CREATE TABLE b0 PARTITION OF b FOR VALUES IN (0, 1, 2, NULL);
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT FROM a t1 CROSS JOIN b t2 FULL JOIN b t3 ON t1.x = t3.y;
SET LOCAL pg_plan_advice.advice = 'JOIN_ORDER(t1 t2 t3) MERGE_JOIN_PLAIN(t3)
NESTED_LOOP_MATERIALIZE(t2) SEQ_SCAN(t2/public.b0 t3/public.b0)
INDEX_ONLY_SCAN(t1 public.a_pkey) PARTITIONWISE(t2 t3) NO_GATHER(t1 t2 t3
t2/public.b0 t3/public.b0)';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT FROM a t1 CROSS JOIN b t2 FULL JOIN b t3 ON t1.x = t3.y;
ROLLBACK;

Full output:

LOAD
===== Case 1: GATHER over a join, whole-row DISTINCT
BEGIN
SET
SET
SET
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
ANALYZE
QUERY PLAN
------------------------------------------------------------------
HashAggregate
Group Key: ((t2.*)::p)
-> Hash Join
Hash Cond: (t1.a = t2.a)
-> Gather
Workers Planned: 2
-> Parallel Append
-> Seq Scan on p0 t1_1
Filter: c
-> Seq Scan on pd t1_2
Filter: c
-> Hash
-> Gather
Workers Planned: 2
-> Parallel Append
-> Seq Scan on p0 t2_1
-> Seq Scan on pd t2_2
Generated Plan Advice:
JOIN_ORDER(t1 t2)
HASH_JOIN(t2)
SEQ_SCAN(t1/public.p0 t1/public.pd t2/public.p0 t2/public.pd)
PARTITIONWISE(t1 t2)
GATHER(t1 t2)
NO_GATHER(t1/public.p0 t1/public.pd t2/public.p0 t2/public.pd)
(24 rows)

SET
QUERY PLAN
------------------------------------------------------------------
HashAggregate
Group Key: ((t2.*)::p)
-> Gather
Workers Planned: 2
-> HashAggregate
Group Key: ((t2.*)::p)
-> Parallel Hash Join
Hash Cond: (t1.a = t2.a)
-> Parallel Append
-> Seq Scan on p0 t1_1
Filter: c
-> Seq Scan on pd t1_2
Filter: c
-> Parallel Hash
-> Parallel Append
-> Seq Scan on p0 t2_1
-> Seq Scan on pd t2_2
Supplied Plan Advice:
SEQ_SCAN(t1/public.p0) /* matched */
SEQ_SCAN(t1/public.pd) /* matched */
SEQ_SCAN(t2/public.p0) /* matched */
SEQ_SCAN(t2/public.pd) /* matched */
JOIN_ORDER(t1 t2) /* matched */
HASH_JOIN(t2) /* matched */
PARTITIONWISE(t1) /* matched */
PARTITIONWISE(t2) /* matched */
GATHER(t1) /* matched, failed */
GATHER(t2) /* matched, failed */
NO_GATHER(t1/public.p0) /* matched */
NO_GATHER(t1/public.pd) /* matched */
NO_GATHER(t2/public.p0) /* matched */
NO_GATHER(t2/public.pd) /* matched */
Generated Plan Advice:
JOIN_ORDER(t1 t2)
HASH_JOIN(t2)
SEQ_SCAN(t1/public.p0 t1/public.pd t2/public.p0 t2/public.pd)
PARTITIONWISE(t1 t2)
GATHER((t1 t2))
NO_GATHER(t1/public.p0 t1/public.pd t2/public.p0 t2/public.pd)
(39 rows)

ROLLBACK
===== Case 2: GATHER on one relation, DISTINCT on a non-whole-row output
BEGIN
SET
SET
SET
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
ANALYZE
QUERY PLAN
-----------------------------------------------------------------------------
HashAggregate
Group Key: (NULL::bigint)
-> Nested Loop Left Join
Join Filter: NULL::boolean
-> Gather
Workers Planned: 2
-> Parallel Append
-> Seq Scan on tb0_p0 tb0_1
-> Seq Scan on tb0_p1 tb0_2
-> Seq Scan on tb0_d tb0_3
-> Result
Replaces: Join on unnest, t2
One-Time Filter: false
Generated Plan Advice:
JOIN_ORDER(tb0 {unnest t2})
NESTED_LOOP_PLAIN((unnest t2))
SEQ_SCAN(tb0/public.tb0_p0 tb0/public.tb0_p1 tb0/public.tb0_d)
PARTITIONWISE(tb0)
GATHER(tb0)
NO_GATHER(unnest t2 tb0/public.tb0_p0 tb0/public.tb0_p1 tb0/public.tb0_d)
(20 rows)

SET
QUERY PLAN
-------------------------------------------------------------------
HashAggregate
Group Key: (NULL::bigint)
-> Gather
Workers Planned: 2
-> HashAggregate
Group Key: (NULL::bigint)
-> Nested Loop Left Join
Join Filter: NULL::boolean
-> Parallel Append
-> Seq Scan on tb0_p0 tb0_1
-> Seq Scan on tb0_p1 tb0_2
-> Seq Scan on tb0_d tb0_3
-> Result
Replaces: Join on unnest, t2
One-Time Filter: false
Supplied Plan Advice:
SEQ_SCAN(tb0/public.tb0_p0) /* matched */
SEQ_SCAN(tb0/public.tb0_p1) /* matched */
SEQ_SCAN(tb0/public.tb0_d) /* matched */
JOIN_ORDER(tb0 {unnest t2}) /* matched */
NESTED_LOOP_PLAIN((unnest t2)) /* matched */
PARTITIONWISE(tb0) /* matched */
GATHER(tb0) /* matched, failed */
NO_GATHER(unnest) /* matched, failed */
NO_GATHER(t2) /* matched, failed */
NO_GATHER(tb0/public.tb0_p0) /* matched */
NO_GATHER(tb0/public.tb0_p1) /* matched */
NO_GATHER(tb0/public.tb0_d) /* matched */
Generated Plan Advice:
JOIN_ORDER(tb0 {unnest t2})
NESTED_LOOP_PLAIN((unnest t2))
SEQ_SCAN(tb0/public.tb0_p0 tb0/public.tb0_p1 tb0/public.tb0_d)
PARTITIONWISE(tb0)
GATHER((unnest tb0 t2))
NO_GATHER(tb0/public.tb0_p0 tb0/public.tb0_p1 tb0/public.tb0_d)
(35 rows)

ROLLBACK
===== Case 3: JOIN_ORDER on a FULL JOIN when every usable join method is
disabled
BEGIN
SET
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
QUERY PLAN
--------------------------------------------------
Merge Full Join
Disabled: true
Merge Cond: (t1.x = t3.y)
-> Nested Loop
-> Index Only Scan using a_pkey on a t1
-> Materialize
-> Seq Scan on b0 t2
-> Sort
Sort Key: t3.y
-> Seq Scan on b0 t3
Generated Plan Advice:
JOIN_ORDER(t1 t2 t3)
MERGE_JOIN_PLAIN(t3)
NESTED_LOOP_MATERIALIZE(t2)
SEQ_SCAN(t2/public.b0 t3/public.b0)
INDEX_ONLY_SCAN(t1 public.a_pkey)
PARTITIONWISE(t2 t3)
NO_GATHER(t1 t2 t3 t2/public.b0 t3/public.b0)
(18 rows)

SET
QUERY PLAN
--------------------------------------------------------
Merge Full Join
Disabled: true
Merge Cond: (t3.y = t1.x)
-> Sort
Sort Key: t3.y
-> Seq Scan on b0 t3
-> Materialize
-> Nested Loop
-> Index Only Scan using a_pkey on a t1
-> Materialize
-> Seq Scan on b0 t2
Supplied Plan Advice:
SEQ_SCAN(t2/public.b0) /* matched */
SEQ_SCAN(t3/public.b0) /* matched */
INDEX_ONLY_SCAN(t1 public.a_pkey) /* matched */
JOIN_ORDER(t1 t2 t3) /* matched, failed */
MERGE_JOIN_PLAIN(t3) /* matched, failed */
NESTED_LOOP_MATERIALIZE(t2) /* matched */
PARTITIONWISE(t2) /* matched */
PARTITIONWISE(t3) /* matched */
NO_GATHER(t1) /* matched */
NO_GATHER(t2) /* matched */
NO_GATHER(t3) /* matched */
NO_GATHER(t2/public.b0) /* matched */
NO_GATHER(t3/public.b0) /* matched */
Generated Plan Advice:
JOIN_ORDER(t3 (t1 t2))
MERGE_JOIN_MATERIALIZE((t1 t2))
NESTED_LOOP_MATERIALIZE(t2)
SEQ_SCAN(t3/public.b0 t2/public.b0)
INDEX_ONLY_SCAN(t1 public.a_pkey)
PARTITIONWISE(t3 t2)
NO_GATHER(t1 t2 t3 t2/public.b0 t3/public.b0)
(33 rows)

ROLLBACK