On Wed, Dec 17, 2025 at 11:12 AM Jakub Wartak
<[email protected]> wrote:
>
> On Mon, Dec 15, 2025 at 9:06 PM Robert Haas <[email protected]> wrote:
> >
> > Here's v7.
> [..]
>[..q20..]
OK, now for the q10:
Sort
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric -
lineitem.l_discount)))) DESC
-> Finalize GroupAggregate
Group Key: customer.c_custkey, nation.n_name
-> Gather Merge
Workers Planned: 2
-> Partial GroupAggregate
Group Key: customer.c_custkey, nation.n_name
-> Sort
Sort Key: customer.c_custkey, nation.n_name
-> Hash Join
Hash Cond: (customer.c_nationkey =
nation.n_nationkey)
-> Parallel Hash Join
Hash Cond: (orders.o_custkey =
customer.c_custkey)
-> Nested Loop
-> Parallel Seq Scan on orders
Filter:
((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01
00:00:00'::timestamp without time zone))
-> Index Scan using
lineitem_l_orderkey_idx_l_returnflag on lineitem
Index Cond:
(l_orderkey = orders.o_orderkey)
-> Parallel Hash
-> Parallel Seq Scan on customer
-> Hash
-> Seq Scan on nation
Generated Plan Advice:
JOIN_ORDER(orders lineitem customer nation)
NESTED_LOOP_PLAIN(lineitem)
HASH_JOIN(customer nation)
SEQ_SCAN(orders customer nation)
INDEX_SCAN(lineitem public.lineitem_l_orderkey_idx_l_returnflag)
GATHER_MERGE((customer orders lineitem nation))
but when set the advice it generates wrong NL instead of expected
Parallel HJ (so another way to fix is to simply disable PQ, yuck),
but:
Sort
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric -
lineitem.l_discount)))) DESC
-> Finalize GroupAggregate
Group Key: customer.c_custkey, nation.n_name
-> Gather Merge
Workers Planned: 2
-> Partial GroupAggregate
Group Key: customer.c_custkey, nation.n_name
-> Sort
Sort Key: customer.c_custkey, nation.n_name
-> Nested Loop
-> Hash Join
Hash Cond:
(customer.c_nationkey = nation.n_nationkey)
-> Parallel Hash Join
Hash Cond:
(orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders
Filter:
((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01
00:00:00'::timestamp without time zone))
-> Parallel Hash
-> Parallel Seq
Scan on customer
-> Hash
-> Seq Scan on nation
-> Index Scan using
lineitem_l_orderkey_idx_l_returnflag on lineitem
Index Cond: (l_orderkey =
orders.o_orderkey)
Supplied Plan Advice:
SEQ_SCAN(orders) /* matched */
SEQ_SCAN(customer) /* matched */
SEQ_SCAN(nation) /* matched */
INDEX_SCAN(lineitem public.lineitem_l_orderkey_idx_l_returnflag) /*
matched */
JOIN_ORDER(orders lineitem customer nation) /* matched,
conflicting, failed */
NESTED_LOOP_PLAIN(lineitem) /* matched, conflicting */
HASH_JOIN(customer) /* matched, conflicting */
HASH_JOIN(nation) /* matched, conflicting */
GATHER_MERGE((customer orders lineitem nation)) /* matched */
So to me it looks like in Generated Plan Advice we:
- have proper HASH_JOIN(customer nation)
- but it somehow forgot to include "HASH_JOIN(orders)" to cover for
that Parallel Hash Join on (orders.o_custkey = customer.c_custkey)
with input from NL. After adding that manually, it achieves the same
input plan properly.
Please let me know if I'm wrong, I was kind of thinking Parallel is
not fully supported, but README/tests seem to state otherwise.
-J.
