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>
 <CAP53Pkycc=7N2bLzVT3x+qE1JamvRZWev5tFjdLJ1+-AV3Di+Q@mail.gmail.com>
 <CA+TgmoaKhuD91RnazbRyGkmP7--JdNq8oNDC3UcgTZSWbMxC7w@mail.gmail.com>
 <CAP53Pkw5-wMEeDJXFmqo_RTyL_spzCXb7HHKrbSnQqokVoZcNQ@mail.gmail.com>
 <CA+Tgmob-69bzbdi3U_QtebqAf6u1y8js=5=oNK639csVe1VbhA@mail.gmail.com>
 <CAKZiRmz4zrZfx_wgzkTD88HzKiA22HKPUnubR3is0bjKra6Utg@mail.gmail.com>
 <CA+Tgmobp8gcB9DOxNUL10xAXXq=ya6oHDUJqtNLf_3ojxm5-fQ@mail.gmail.com>
 <CAKZiRmznM4cc_N5qQBgEdDj9F5J8=zTJbWm9erZzhp9jP7LMbg@mail.gmail.com>
 <CA+Tgmoa5HjTnBMNQuW5QrCUacC9gdok+02a2Y9TTGWtTQ6OBvQ@mail.gmail.com>
 <CAKZiRmzpO=PpLmXEQUO5CETzT0mmLhT5P656hetseKQUgZ6BQg@mail.gmail.com>
 <CA+TgmobRAjY+xs_=fKMJ4NgW_i4bMoD5kW7oKrGACLLcY04ysA@mail.gmail.com>
 <CAKZiRmxmhjnKp-GMvdL-2jE-nic5g7-TLdsveirrbRv2zdSdyQ@mail.gmail.com>
 <CABRHmyuoOBXG_SVG=xpH+5+hacqfofniYSZvyJBRL7EadphcVw@mail.gmail.com>
 <CA+TgmoaDqNB68kN8OO=4rKSALrWQ8LGRpU_kKi_vr9p=YgBqYQ@mail.gmail.com>
 <CA+Tgmoa6EvDrqOfwyBwoVOWL7-y34=FUTwV62Rx32bN0RkDVOQ@mail.gmail.com>
 <CABRHmyvLPcx_K1T9Cwg4tFHiyh95fQU9tUhDwEFtvJiDsQDdaA@mail.gmail.com> <CA+TgmobbV53ogwJoXc2S-HXYi+bwGLHDrm1SOPkB_yKyOHbbmA@mail.gmail.com>
In-Reply-To: <CA+TgmobbV53ogwJoXc2S-HXYi+bwGLHDrm1SOPkB_yKyOHbbmA@mail.gmail.com>
From: Ajay Pal <ajay.pal.k@gmail.com>
Date: Tue, 27 Jan 2026 18:18:46 +0530
Message-ID: <CABRHmyvGE7ebajakpaOioYw8uD1yz2Kw+fW0KsUoGpANsvtBpA@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>, Lukas Fittl <lukas@fittl.com>, 
	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/CABRHmyvGE7ebajakpaOioYw8uD1yz2Kw%2BfW0KsUoGpANsvtBpA%40mail.gmail.com>
Precedence: bulk

Thank you Robert for clarification.

On Tue, Jan 27, 2026 at 6:02=E2=80=AFPM Robert Haas <robertmhaas@gmail.com>=
 wrote:
>
> On Tue, Jan 27, 2026 at 2:49=E2=80=AFAM Ajay Pal <ajay.pal.k@gmail.com> w=
rote:
> > #1 Grouped Hash Join, This forces the join of dim1 and dim2 to happen
> > first, and then places that resulting set on the inner side of a Hash
> > Join against fact.
> > but the planner partially matches the generated advice.
> >
> > -- We want (dim1 JOIN dim2) to be the inner side of a Hash Join
> > SET LOCAL pg_plan_advice.advice =3D 'HASH_JOIN((dim1 dim2))';
> >
> > postgres=3D*# EXPLAIN (COSTS OFF, PLAN_ADVICE)
> > SELECT * FROM fact
> >                   JOIN dim1 ON fact.d1_id =3D dim1.id
> >                   JOIN dim2 ON fact.d2_id =3D dim2.id;
> >                         QUERY PLAN
> > -----------------------------------------------------------
> >  Nested Loop
> >    Disabled: true
> >    ->  Nested Loop
> >          Disabled: true
> >          ->  Seq Scan on fact
> >          ->  Index Scan using dim1_pkey on dim1
> >                Index Cond: (id =3D fact.d1_id)
> >    ->  Index Scan using dim2_pkey on dim2
> >          Index Cond: (id =3D fact.d2_id)
> >  Supplied Plan Advice:
> >    HASH_JOIN((dim1 dim2)) /* partially matched */
> >  Generated Plan Advice:
> >    JOIN_ORDER(fact dim1 dim2)
> >    NESTED_LOOP_PLAIN(dim1 dim2)
> >    SEQ_SCAN(fact)
> >    INDEX_SCAN(dim1 public.dim1_pkey dim2 public.dim2_pkey)
> >    NO_GATHER(fact dim1 dim2)
> > (17 rows)
>
> Thanks for the report, but this is actually correct behavior. There's
> no join clause between dim1 and dim2, so the planner doesn't consider
> a dim1-dim2 join. This is a good example of the phenomenon described
> in the documentation: you can't force the planner to create an
> arbitrary plan that it wouldn't otherwise have considered. I might
> tweak the documentation wording a little to try  to mention that this
> is another way "partially matched" can happen, but there's no bug
> here.
>
> > #2 Multiple Instances of Same Table in Subqueries, here target the
> > second instance of dim1 inside the subquery 'sq'. both seq_scan and
> > index_scan advices are not matching.
> >
> > SET LOCAL pg_plan_advice.advice =3D 'SEQ_SCAN(dim1#2@sq)
> > INDEX_SCAN(dim1@sq dim1_pkey)';
> >
> > postgres=3D*# EXPLAIN (COSTS OFF, PLAN_ADVICE)
> > SELECT * FROM fact
> > JOIN (
> >     SELECT a.id FROM dim1 a
> >     JOIN dim1 b ON a.id =3D b.id
> >     OFFSET 0
> > ) sq ON fact.d1_id =3D sq.id;
> >                     QUERY PLAN
> > ---------------------------------------------------
> >  Hash Join
> >    Hash Cond: (fact.d1_id =3D b.id)
> >    ->  Seq Scan on fact
> >    ->  Hash
> >          ->  Seq Scan on dim1 b
> >  Supplied Plan Advice:
> >    SEQ_SCAN(dim1#2@sq) /* not matched */
> >    INDEX_SCAN(dim1@sq dim1_pkey) /* not matched */
> >  Generated Plan Advice:
> >    JOIN_ORDER(fact sq)
> >    HASH_JOIN(sq)
> >    SEQ_SCAN(b@sq fact)
> >    NO_GATHER(fact b@sq)
> > (13 rows)
>
> I'm not sure what why you expected this to work. You can see what the
> correct relation identifiers are from the generated plan advice, and
> you've used something else, so it doesn't match. It's documented in
> both the SGML documentation and the README that relation identifiers
> are based on the relation alias, not the relation name.
>
> In general, this seems like a good to reiterate that this is first and
> foremost a plan stability feature. More than anything, these examples
> show that if you try to write your own plan advice from scratch to
> force a novel plan that the planner has never produced itself, you may
> not have much luck. If you do want to try to produce a novel plan, you
> should at least look at the generated plan advice and adapt it instead
> of starting from scratch. And if you find, when trying to produce a
> novel plan, that it doesn't work, you need to consider the possibility
> that this is because the optimizer did not ever consider that plan,
> and that is why pg_plan_advice is unable to induce the planner to
> prefer it. That's not to say there can't be any remaining bugs in
> pg_plan_advice; there probably are. But it also is absolutely not a
> "write your own plan and do anything you like" feature.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com