Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPirF-00000000rTa-3ANR for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:35 +0000 Received: from localhost ([127.0.0.1]:57122 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPirF-000000049lg-40g5 for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:59566) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPPZf-00000001GWT-0hI8 for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:26:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPPZb-00000000bQG-3sgD for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:26:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=biDMviZ9sE4zbcTnKoy0QId+1qrIP8IzRpwDKLRHNn8=; b=FdrBO00BzvagiPalDqN4DwD5lI EokqkgPDBHafWENiANGJFKZkXX2RsSBld5IHqjXbHdtwX7QUjIem5QvG+MS9ozKMXpP3mlKqaRxuN 3lkawnYOpMMUo1dDKGw8LNNzVdqmRXNrtupNnmnomfC3KhLtCzUl1Itz9SPRwKxWUlFWiTwTDZC6d CcRpb5iQdHhny90w46/1RFy0QQ7wJbkBkrktKBZpFgFeobb9qvn/hORgAE5si8q2EnZoBzpR0naM4 rmVf4D/sVcwgd3rHIydah+Ku9QTfwkHk8cZeHIkj2I8Kgfv4k3zpDAw7dRfmbp+7xVgAlhnkxbSr2 yfbZ9/Ow==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPPZc-00000001dYQ-08sx for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:26:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPPZb-000000048Pq-1DGK for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:26:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19838: pg_plan_advice: generated GATHER advice fails when supplied back To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 125atishprak@gmail.com Reply-To: 125atishprak@gmail.com, pgsql-bugs@lists.postgresql.org Date: Fri, 06 Nov 2026 19:25:13 +0000 Message-ID: <19838-046a91493fc029d7@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19838 Logged by: Atish Prakash Email address: 125atishprak@gmail.com PostgreSQL version: 19rc1 Operating system: Linux x86_64 (kernel 7.0.0, glibc 2.43) Description: =20 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 =3D t2.a WHE= RE 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=3D-O1 -g, --enable-cassert --enable-debug --without-icu --without-readline --without-libxml --without-openssl Non-default server settings: fsync=3Doff, shared_buffers=3D48MB, lc_message= s=3DC 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 =3D=3D=3D=3D=3D Case 1: GATHER over a join, whole-row DISTINCT BEGIN; SET LOCAL parallel_setup_cost =3D 0; SET LOCAL parallel_tuple_cost =3D 0; SET LOCAL min_parallel_table_scan_size =3D 0; SET LOCAL min_parallel_index_scan_size =3D 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 =3D t2.a WHERE t1.c; SET LOCAL pg_plan_advice.advice =3D '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 =3D t2.a WHERE t1.c; ROLLBACK; \echo =3D=3D=3D=3D=3D Case 2: GATHER on one relation, DISTINCT on a non-who= le-row output BEGIN; SET LOCAL parallel_setup_cost =3D 0; SET LOCAL parallel_tuple_cost =3D 0; SET LOCAL min_parallel_table_scan_size =3D 0; SET LOCAL min_parallel_index_scan_size =3D 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) >=3D '2024-02-29'::date) ON NULL RIGHT JOIN tb0 ON NULL; SET LOCAL pg_plan_advice.advice =3D '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) >=3D '2024-02-29'::date) ON NULL RIGHT JOIN tb0 ON NULL; ROLLBACK; \echo =3D=3D=3D=3D=3D Case 3: JOIN_ORDER on a FULL JOIN when every usable j= oin method is disabled BEGIN; SET LOCAL enable_hashjoin =3D off; SET LOCAL enable_mergejoin =3D 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 =3D t3.y; SET LOCAL pg_plan_advice.advice =3D '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 =3D t3.y; ROLLBACK; Full output: LOAD =3D=3D=3D=3D=3D 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 =3D 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 =3D 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 =3D=3D=3D=3D=3D 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 =3D=3D=3D=3D=3D Case 3: JOIN_ORDER on a FULL JOIN when every usable join me= thod is disabled BEGIN SET SET CREATE TABLE CREATE TABLE CREATE TABLE QUERY PLAN -------------------------------------------------- Merge Full Join Disabled: true Merge Cond: (t1.x =3D 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 =3D 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