Thread

Re: pg_plan_advice

Robert Haas <robertmhaas@gmail.com> — 2025-12-18T20:39:13Z

On Wed, Dec 17, 2025 at 8:44 AM Jakub Wartak
<jakub.wartak@enterprisedb.com> 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