public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jakub Wartak <[email protected]>
To: Robert Haas <[email protected]>
Cc: Jacob Champion <[email protected]>
Cc: Dian Fay <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice
Date: Wed, 17 Dec 2025 14:44:02 +0100
Message-ID: <CAKZiRmzb-SHvr4EVw_sB7pY-iGvszPETb1dvmbkpfRPnrOx3jw@mail.gmail.com> (raw)
In-Reply-To: <CAKZiRmx538gbg=sV8CQUoR2zf6q5Y3c_1pGBd1A7XONG-5c2eg@mail.gmail.com>
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>
<[email protected]>
<CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
<CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
<[email protected]>
<CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
<[email protected]>
<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>
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.
view thread (133+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_plan_advice
In-Reply-To: <CAKZiRmzb-SHvr4EVw_sB7pY-iGvszPETb1dvmbkpfRPnrOx3jw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox