On Wed, Dec 17, 2025 at 8:44 AM Jakub Wartak <[email protected]> wrote: > 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))
This looks correct to me. > 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: This is obviously bad. I'm not quite sure what happened here, but my guess is that something prevented the JOIN_ORDER advice from being applied cleanly and then everything went downhill from there. I wonder if JOIN_ORDER doesn't interact properly with incremental sorts -- that's a situation for which I don't think I have existing test coverage. > 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. The first table in the JOIN_ORDER() specification isn't supposed to have a join method specification, because the join method specifier says what appears on the inner, i.e. second, arm of the join. -- Robert Haas EDB: http://www.enterprisedb.com
