MIME-Version: 1.0
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
 <CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
 <DEB1SAGVDX7S.JMV96X4R0RCS@gmail.com>
 <CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
 <CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
 <DEFNP3WX7BLX.1X7DS1P9X6JIF@nmfay.com>
 <CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
 <DELPC4B56Y5A.3OBZ3WCH3T4VX@nmfay.com>
 <CA+TgmoaOSBQD9Ux4eG40w723ZN=c0J7p-+oX4+J8urUeyLMo5w@mail.gmail.com>
 <CAOYmi+=g+MMoOpWkk2weXWKJcKH0eKey8gKHHdH0dF4Tiawrhw@mail.gmail.com>
 <CA+TgmobwaT=PXPDDrgDup+jA8KHBbkxigtziD-zNzAKKkQYVgQ@mail.gmail.com>
 <CAOYmi+mOmEW=amDRQMfw6-Fb3ZmDEQFaJzwk8Bc8W8DzaP85XQ@mail.gmail.com>
 <CA+TgmoaX2AMW4cdFM3OngBJxmxpkdmzF33R7-CWhvRLfucbFMg@mail.gmail.com>
 <CAOYmi+njnRGcomnxTY6vsEW3wWigc0ruB0EyWFpb+PVVE8sWpw@mail.gmail.com>
 <CAOYmi+k4AyWCQHK=XVF99KVDuFkqxcADao61OWGLxu0nRYMONQ@mail.gmail.com>
 <CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
 <CAKZiRmx538gbg=sV8CQUoR2zf6q5Y3c_1pGBd1A7XONG-5c2eg@mail.gmail.com>
In-Reply-To: 
 <CAKZiRmx538gbg=sV8CQUoR2zf6q5Y3c_1pGBd1A7XONG-5c2eg@mail.gmail.com>
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Wed, 17 Dec 2025 14:44:02 +0100
Message-ID: 
 <CAKZiRmzb-SHvr4EVw_sB7pY-iGvszPETb1dvmbkpfRPnrOx3jw@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jacob Champion <jacob.champion@enterprisedb.com>, Dian Fay <di@nmfay.com>,
	Matheus Alcantara <matheusssilv97@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAKZiRmzb-SHvr4EVw_sB7pY-iGvszPETb1dvmbkpfRPnrOx3jw%40mail.gmail.com>
Precedence: bulk

On Wed, Dec 17, 2025 at 11:12=E2=80=AFAM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
>
> On Mon, Dec 15, 2025 at 9:06=E2=80=AFPM Robert Haas <robertmhaas@gmail.co=
m> 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 =3D
nation.n_nationkey)
                                 ->  Parallel Hash Join
                                       Hash Cond: (orders.o_custkey =3D
customer.c_custkey)
                                       ->  Nested Loop
                                             ->  Parallel Seq Scan on order=
s
                                                   Filter:
((o_orderdate >=3D '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 =3D orders.o_orderkey)
                                       ->  Parallel Hash
                                             ->  Parallel Seq Scan on custo=
mer
                                 ->  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 =3D nation.n_nationkey)
                                       ->  Parallel Hash Join
                                             Hash Cond:
(orders.o_custkey =3D customer.c_custkey)
                                             ->  Parallel Seq Scan on order=
s
                                                   Filter:
((o_orderdate >=3D '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 =3D
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 =3D 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.