Re: pg_*_advice: tsv load failure, etc.

On Fri, Oct 16, 2026 at 11:45 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
[..]
> My plan is to get all of these committed and back-patched to
> REL_19_STABLE before we wrap rc2. Thanks all for the reports, and
> sorry it's taking me a while to grind through these. I really have
> spent a lot of time on it, trust me...

As promised, I've took very quick look using TPC-H queries using that script
from the beggining of the project (tpch-vs-pg_plan_advise) that I've used
for finding initial problems (before there was built-in hairness). I've run it
on the the master (from yesterday) and compared to: master+v8. In short: LGTM,
but haven't looked at code.

Most of the plans seem to be identical to naked eye and 100% correct in terms
of joins, lookup types, paths, it's just that sometimes that v8 introduced more
parallelism that was happening without it. E.g. for Q7 for master+v8 it went
from:

# planner
GroupAggregate
Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
-> Gather Merge
Workers Planned: 2
-> Sort
Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM
lineitem.l_shipdate))
-> Hash Join
[..]

# captured advice
JOIN_ORDER(customer n2 orders lineitem (supplier n1))
MERGE_JOIN_PLAIN(n2)
NESTED_LOOP_PLAIN(orders lineitem)
HASH_JOIN(n1 (supplier n1))
SEQ_SCAN(supplier n1)
INDEX_SCAN(n2 public.pk_nation orders public.orders_o_custkey_idx lineitem
public.pk_lineitem)
INDEX_ONLY_SCAN(customer public.customer_c_nationkey_c_custkey_idx)
GATHER_MERGE((supplier lineitem orders customer n1 n2))

# wiping rel stats and got this plan after that using that above advice:
Finalize GroupAggregate
Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate))
-> Gather Merge
Workers Planned: 2
-> Partial GroupAggregate
Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM
lineitem.l_shipdate))
-> Sort
Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM
lineitem.l_shipdate))
-> Hash Join
[..]

which translates to more or less as I understand it that in adviced plan case,
it just added even more parallelism just after the sort. Anyway I think it's
known limitation as per sgml: "It is currently not possible to control any
aspect of the planner's behavior with respect to aggregation. This includes
both whether aggregates are computed by sorting (..)", so fine by me, I was
just surprised by this and it looks like it is not really deterministic (I can
dig further, but it's of scope anyway and it's not worth it).

-J.