Re: pg_plan_advice

On Thu, Jan 8, 2026 at 7:21 PM Robert Haas <robertmhaas(at)gmail(dot)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