public inbox for [email protected]
help / color / mirror / Atom feedFrom: Robert Haas <[email protected]>
To: Jakub Wartak <[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: Thu, 18 Dec 2025 08:36:44 -0500
Message-ID: <CA+TgmoYx3SGxDKRVSbZy-yBNwkV+1MGWYP2C690W2UMxdq66dQ@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 5:12 AM Jakub Wartak
<[email protected]> wrote:
> Sort (cost=1010985030.44..1010985030.59 rows=61 width=51)
> Sort Key: supplier.s_name
> -> Nested Loop (cost=0.42..1010985028.63 rows=61 width=51)
> Join Filter: (nation.n_nationkey = supplier.s_nationkey)
> -> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4)
> Filter: (n_name = 'CANADA'::bpchar)
> -> Nested Loop Semi Join (cost=0.42..1010985008.29
> rows=1522 width=55)
> Join Filter: (partsupp.ps_suppkey = supplier.s_suppkey)
> -> Seq Scan on supplier (cost=0.00..249.30 rows=7730 width=59)
> -> Materialize (cost=0.42..1010755994.57 rows=1973 width=4)
> -> Nested Loop (cost=0.42..1010755984.71
> rows=1973 width=4)
> -> Seq Scan on part (cost=0.00..4842.25
> rows=1469 width=4)
> Filter: ((p_name)::text ~~ 'forest%'::text)
> -> Index Scan using pk_partsupp on
> partsupp (cost=0.42..688053.87 rows=1 width=8)
> Index Cond: (ps_partkey = part.p_partkey)
> Filter: ((ps_availqty)::numeric >
> (SubPlan expr_1))
> SubPlan expr_1
> -> Aggregate
> (cost=172009.42..172009.44 rows=1 width=32)
> -> Seq Scan on lineitem
> (cost=0.00..172009.42 rows=1 width=5)
> Filter: ((l_shipdate >=
> '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp
> without time zone) AND (l_partkey = partsupp.ps_partkey) AND
> (l_suppkey = partsupp.ps_suppkey))
>
>
> Generated Plan Advice:
> JOIN_ORDER(nation (supplier (part partsupp)))
> NESTED_LOOP_PLAIN(partsupp partsupp) <--- [X]
> NESTED_LOOP_MATERIALIZE(partsupp)
> SEQ_SCAN(nation supplier part lineitem@expr_1)
> INDEX_SCAN(partsupp public.pk_partsupp)
> SEMIJOIN_NON_UNIQUE((partsupp part))
> NO_GATHER(supplier nation partsupp part lineitem@expr_1)
Yeah, that's not right. There are three nested loops here, so we
should have three pieces of nested loop advice.
NESTED_LOOP_MATERIALIZE(partsupp) covers the innermost nested loop.
The other two are NESTED_LOOP_PLAIN, but the advice should cover all
the tables on the inner side of the join. I think it should read:
NESTED_LOOP_PLAIN((part partsupp) (supplier part partsupp))
Ordering isn't significant here, so NESTED_LOOP_PLAIN((part supplier
partsupp) (partsupp part)) would be logically equivalent. Doesn't
matter exactly what we output here, but it shouldn't be just partsupp.
> and apparently proper advice like below which has better yield:
> set pg_plan_advice.advice = '[..] NESTED_LOOP_PLAIN(part partsupp)
This isn't quite what you want, because this says that part should be
on the outer side of a NESTED_LOOP_PLAIN by itself and partsupp should
also be on the outer side of a NESTED_LOOP_PLAIN by itself. You need
the extra set of parentheses to indicate that the join product of
those two tables should be on the outer side of a NESTED_LOOP_PLAIN,
rather than each table individually.
What must be happening here is that either pgpa_join.c (maybe with
complicity from pgpa_walker.c) is not populating the
pgpa_plan_walker_context's join_strategies[JSTRAT_NESTED_LOOP_PLAIN]
member correctly, or else pgpa_output.c is not serializing it to text
correctly. I suspect the former is a more likely but I'm not sure
exactly what's happening.
--
Robert Haas
EDB: http://www.enterprisedb.com
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: <CA+TgmoYx3SGxDKRVSbZy-yBNwkV+1MGWYP2C690W2UMxdq66dQ@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