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: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 18 Dec 2025 08:36:44 -0500
Message-ID: 
 <CA+TgmoYx3SGxDKRVSbZy-yBNwkV+1MGWYP2C690W2UMxdq66dQ@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Jakub Wartak <jakub.wartak@enterprisedb.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/CA%2BTgmoYx3SGxDKRVSbZy-yBNwkV%2B1MGWYP2C690W2UMxdq66dQ%40mail.gmail.com>
Precedence: bulk

On Wed, Dec 17, 2025 at 5:12=E2=80=AFAM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
>  Sort  (cost=3D1010985030.44..1010985030.59 rows=3D61 width=3D51)
>    Sort Key: supplier.s_name
>    ->  Nested Loop  (cost=3D0.42..1010985028.63 rows=3D61 width=3D51)
>          Join Filter: (nation.n_nationkey =3D supplier.s_nationkey)
>          ->  Seq Scan on nation  (cost=3D0.00..1.31 rows=3D1 width=3D4)
>                Filter: (n_name =3D 'CANADA'::bpchar)
>          ->  Nested Loop Semi Join  (cost=3D0.42..1010985008.29
> rows=3D1522 width=3D55)
>                Join Filter: (partsupp.ps_suppkey =3D supplier.s_suppkey)
>                ->  Seq Scan on supplier  (cost=3D0.00..249.30 rows=3D7730=
 width=3D59)
>                ->  Materialize  (cost=3D0.42..1010755994.57 rows=3D1973 w=
idth=3D4)
>                      ->  Nested Loop  (cost=3D0.42..1010755984.71
> rows=3D1973 width=3D4)
>                            ->  Seq Scan on part  (cost=3D0.00..4842.25
> rows=3D1469 width=3D4)
>                                  Filter: ((p_name)::text ~~ 'forest%'::te=
xt)
>                            ->  Index Scan using pk_partsupp on
> partsupp  (cost=3D0.42..688053.87 rows=3D1 width=3D8)
>                                  Index Cond: (ps_partkey =3D part.p_partk=
ey)
>                                  Filter: ((ps_availqty)::numeric >
> (SubPlan expr_1))
>                                  SubPlan expr_1
>                                    ->  Aggregate
> (cost=3D172009.42..172009.44 rows=3D1 width=3D32)
>                                          ->  Seq Scan on lineitem
> (cost=3D0.00..172009.42 rows=3D1 width=3D5)
>                                                Filter: ((l_shipdate >=3D
> '1994-01-01'::date) AND (l_shipdate < '1995-01-01 00:00:00'::timestamp
> without time zone) AND (l_partkey =3D partsupp.ps_partkey) AND
> (l_suppkey =3D 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 =3D '[..] 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.

--=20
Robert Haas
EDB: http://www.enterprisedb.com