Re: pg_plan_advice

On Thu, Jan 8, 2026 at 7:21 PM Robert Haas <robertmhaas@gmail.com> wrote:
[..]
> I've attempted to address that in v8 [..snip]
[..]

The full TPC-H queries set still reported some issues with v8 (for
q20/with wrong plan after using advice and two "partially matched" for
q9 and q2). However after applying that tiny patch from [1] it makes
all of the problems go away and for the TPC-H query set there are no
failures anymore (yay!). By failure I mean a different plan when using
advice and/or any partial match or failure to apply advice.

So, I've switched to more realistic test for each TPC-H query:
1) ensure we have valid stats, gather plan, save advices
2) clear stats using pg_clear_relation_stats() , apply advice and
check if we have exact same plan

The only thing this hav revealed is what appears to be some tiny
problem with placing GroupAggregates or am I wrong or is that known
limitation? (The original plan shows "Partial GroupAggregate" while
the one using advice is not aware of the need to use it; yet
contrib/pg_plan_advices/README in "Future Work" indicates it is out of
scope for now, right?)

--- /tmp/plan
+++ /tmp/planadviced
Sort
   Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric -
lineitem.l_discount)))) DESC
-  ->  Finalize GroupAggregate
+  ->  GroupAggregate
         Group Key: nation.n_name
         ->  Gather Merge
               Workers Planned: 2
-              ->  Partial GroupAggregate
-                    Group Key: nation.n_name
-                    ->  Sort
-                          Sort Key: nation.n_name
-                          ->  Hash Join
-                                Hash Cond: ((lineitem.l_suppkey =
supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
+              ->  Sort
+                    Sort Key: nation.n_name
+                    ->  Hash Join
+                          Hash Cond: ((lineitem.l_suppkey =
supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))

-J.

[1] - https://www.postgresql.org/message-id/CA%2BTgmoZzBkd1BG8qusicUjme0kZuT8konQM_rcr0gMXs-TpK7A%40mail.gmail.com