On Mon, Dec 8, 2025 at 5:18 PM Jacob Champion
<[email protected]> wrote:
> a) fuzz the parser first, because it's easy and we can get interesting inputs
> b) fuzz the AST utilities, seeded with "successful" corpus members from a)
> c) stare really hard at the corpus of b) and figure out how to
> usefully mutate a PlannedStmt with it
Got stuck a bit at (c). The first two fit very well with my preferred
fuzzer setup, where I mock the world and fuzz the heck out of a tiny
corner of it. But a "mutated plan" would 1) take a lot of time for me
to design and 2) probably be counterproductive if I start chasing
impossible plans.
So I've inverted it, so that the server calls libfuzzer midquery,
instead of libfuzzer driving the code under test. That gives me *real*
plans that I can then hit with a bunch of garbage advice -- but it's
more than an order of magnitude slower, unfortunately, so I have to
seed it with the output of a+b before it gets anywhere, and then I
cannot minimize the corpus (which fills up rapidly with unoptimized
inputs) because libfuzzer isn't driving. I feel like there is
considerable room for improvement here... but I could spend a bunch of
time finding it that is then not spent fuzzing.
--
The first thing found with the new architecture is this:
-- note that f is not a partitioned table
SET pg_plan_advice.advice = 'join_order(f/e (f d))';
EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM gt_fact f JOIN gt_dim d ON f.dim_id = d.id;
ERROR: cannot determine RTI for advice target
Test, and a quick guess at expected output, attached.
--Jacob
commit 0a3adf1387662f76a921510731097ebcf3964547
Author: Jacob Champion <[email protected]>
Date: Tue Jan 13 10:27:10 2026
review: try JOIN_ORDER with nonexistent partition
Fails with
ERROR: cannot determine RTI for advice target
diff --git a/contrib/pg_plan_advice/expected/join_order.out
b/contrib/pg_plan_advice/expected/join_order.out
index f3dd7810484..abec728ddc9 100644
--- a/contrib/pg_plan_advice/expected/join_order.out
+++ b/contrib/pg_plan_advice/expected/join_order.out
@@ -165,6 +165,69 @@ SELECT * FROM jo_fact f
NO_GATHER(f d1 d2)
(21 rows)
+COMMIT;
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 d1 d2)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+ LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop
+ Disabled: true
+ -> Nested Loop
+ Disabled: true
+ -> Seq Scan on jo_fact f
+ -> Index Scan using jo_dim1_pkey on jo_dim1 d1
+ Index Cond: (id = f.dim1_id)
+ Filter: (val1 = 1)
+ -> Index Scan using jo_dim2_pkey on jo_dim2 d2
+ Index Cond: (id = f.dim2_id)
+ Filter: (val2 = 1)
+ Supplied Plan Advice:
+ JOIN_ORDER(f/d1 d1 d2) /* partially matched */
+ Generated Plan Advice:
+ JOIN_ORDER(f d1 d2)
+ NESTED_LOOP_PLAIN(d1 d2)
+ SEQ_SCAN(f)
+ INDEX_SCAN(d1 public.jo_dim1_pkey d2 public.jo_dim2_pkey)
+ NO_GATHER(f d1 d2)
+(19 rows)
+
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 (d1 d2))';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+ LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+ QUERY PLAN
+-------------------------------------------------------------
+-- XXX: this is just a guess
+ Merge Join
+ Merge Cond: ((d2.id = f.dim2_id) AND (d1.id = f.dim1_id))
+ -> Sort
+ Sort Key: d2.id, d1.id
+ -> Nested Loop
+ -> Seq Scan on jo_dim1 d1
+ Filter: (val1 = 1)
+ -> Materialize
+ -> Seq Scan on jo_dim2 d2
+ Filter: (val2 = 1)
+ -> Sort
+ Sort Key: f.dim2_id, f.dim1_id
+ -> Seq Scan on jo_fact f
+ Supplied Plan Advice:
+ JOIN_ORDER(f/d1 (d1 d2)) /* partially matched */
+ Generated Plan Advice:
+ JOIN_ORDER(d1 d2 f)
+ MERGE_JOIN_PLAIN(f)
+ NESTED_LOOP_MATERIALIZE(d2)
+ SEQ_SCAN(d1 d2 f)
+ NO_GATHER(f d1 d2)
+(21 rows)
+
COMMIT;
-- The unusual formulation of this query is intended to prevent the query
-- planner from reducing the FULL JOIN to some other join type, so that we
diff --git a/contrib/pg_plan_advice/sql/join_order.sql
b/contrib/pg_plan_advice/sql/join_order.sql
index 5aa2fc62d34..48b8dfe6f74 100644
--- a/contrib/pg_plan_advice/sql/join_order.sql
+++ b/contrib/pg_plan_advice/sql/join_order.sql
@@ -60,6 +60,21 @@ SELECT * FROM jo_fact f
WHERE val1 = 1 AND val2 = 1;
COMMIT;
+BEGIN;
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 d1 d2)';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+ LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+SET LOCAL pg_plan_advice.advice = 'join_order(f/d1 (d1 d2))';
+EXPLAIN (COSTS OFF, PLAN_ADVICE)
+SELECT * FROM jo_fact f
+ LEFT JOIN jo_dim1 d1 ON f.dim1_id = d1.id
+ LEFT JOIN jo_dim2 d2 ON f.dim2_id = d2.id
+ WHERE val1 = 1 AND val2 = 1;
+COMMIT;
+
-- The unusual formulation of this query is intended to prevent the query
-- planner from reducing the FULL JOIN to some other join type, so that we
-- can test what happens with a join type that cannot be reordered.
