MIME-Version: 1.0
In-Reply-To: 
 <CA+TgmoZBPeQtpoq+AQg2yYxoFPQbXmpEbcYSV6M4mySxHTPZiw@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>
 <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+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>
 <CA+TgmoaZMOikxK=LqS+Jn+835h9S139JLGk-3LyETVXw5W5j=w@mail.gmail.com>
 <1136161.1769654478@sss.pgh.pa.us>
 <CAP53PkwZ1ZTMARKg6iEfAw9qzBhkjBitj-9gr_Jvy7k2AwGgWA@mail.gmail.com>
 <CAMbWs4--NuEUFE_xTo991TRXaZryE29jarJPDnVxoaQOYdt7tA@mail.gmail.com>
 <CA+TgmobzR+XMGbRosVPbjHbSo4+cgJn=qZK6w05aF1sbj=C+9Q@mail.gmail.com>
 <CA+TgmoawzvCoZAwFS85tE5+c8vBkqgcS8ZstQ_ohjXQ9wGT9sw@mail.gmail.com>
 <CA+TgmoYS4ZCVAF2jTce=bMP0Oq_db_srocR4cZyO0OBp9oUoGg@mail.gmail.com>
 <CAK98qZ2RzbgCHrSg4zLkvpzyBam_X6te-KF8w1+_vON9BAVMEw@mail.gmail.com>
 <CA+TgmoaCdsuvNn6T6SfQ_0YD2Hh2+hgTXh9fTGHQhPg1zvy2rQ@mail.gmail.com>
 <CAK98qZ1zWzRB0ABG7ULzTeWKRR5C7-FxLqM-6v8wQDiFM+DNAg@mail.gmail.com>
 <CA+Tgmob7ozJAs5SU6bD2RfAt4w_AmsMGz-aaVA6WeLXHkBypOg@mail.gmail.com>
 <CAK98qZ1J42RoAsHnYWGPPmHziZmzmqE=Lp_O6WJ-9aKK2qjikA@mail.gmail.com>
 <CA+TgmoYjcBA6dw3nwiyfDzPXTCrxTZPXDMrc2TrDJcL1cPK6iA@mail.gmail.com>
 <CA+TgmoYru-vxoTKfwjQby30r2OkTXfb18Km_=VLs6qk8Akr0-g@mail.gmail.com>
 <CAKZiRmx=ijCZFpAYMb1z0=9u0iixqD6cBKPBx+WLFFKqOW8R=w@mail.gmail.com>
 <CA+TgmoYO0qtqz+V7S4q0e_dLhLrrsMxA51t5wks_y8Skv6cdRQ@mail.gmail.com>
 <CAKFQuwb2U_X6zMqY5aSewjTTURQyXR3xY-avzJMmM+eDZCNCjQ@mail.gmail.com>
 <CA+TgmoZBPeQtpoq+AQg2yYxoFPQbXmpEbcYSV6M4mySxHTPZiw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 3 Mar 2026 21:11:38 -0700
Message-ID: 
 <CAKFQuwaA09Kxfbv2tgh-SaaCXLD5+grYj3F9RNGQhTHsXz9ENw@mail.gmail.com>
Subject: Re: pg_plan_advice (now with transparent SQL plan performance
 overrides - pg_stash_advice)
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>,
	Alexandra Wang <alexandra.wang.oss@gmail.com>,
 Richard Guo <guofenglinux@gmail.com>,
	Lukas Fittl <lukas@fittl.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	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: multipart/alternative; boundary="0000000000004267a5064c2b04f0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwaA09Kxfbv2tgh-SaaCXLD5%2BgrYj3F9RNGQhTHsXz9ENw%40mail.gmail.com>
Precedence: bulk

--0000000000004267a5064c2b04f0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tuesday, March 3, 2026, Robert Haas <robertmhaas@gmail.com> wrote:

>
> >
> > SEQ_SCAN(t1@minmax_1) /* matched, failed */
>
> Because there's not a way to control aggregation behavior at present,
> you can't directly conrol whether t1 or t1@minmax_1 appears in the
> winning plan.


Ok, that=E2=80=99s what I was missing here, it saw the subplan in its optio=
ns but
the winning plan didn=E2=80=99t include it.  So =E2=80=9Cmatched/failed=E2=
=80=9D may produce a plan
where the target having been matched isn=E2=80=99t actually visible to the =
user.

Maybe add a note like this to pg_plan_advice:

Generated advice is produced to a high level of specificity without knowing
what limitations the advice interpreter has in applying that advice.
Therefore, generated advice targets may later fail for no other reason than
cost-based decisions resulted in the originally chosen plan to no longer be
chosen.  The planner will still likely see the original target on a now
losing plan and thus the advice feedback will report matched even when the
winning plan does not include the specific target.

David J.

--0000000000004267a5064c2b04f0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tuesday, March 3, 2026, Robert Haas &lt;<a href=3D"mailto:robertmhaas@gm=
ail.com" target=3D"_blank">robertmhaas@gmail.com</a>&gt; wrote:<br><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc s=
olid;padding-left:1ex"><br>
&gt;<br>
&gt; SEQ_SCAN(t1@minmax_1) /* matched, failed */<br>
<br>
Because there&#39;s not a way to control aggregation behavior at present,<b=
r>
you can&#39;t directly conrol whether t1 or t1@minmax_1 appears in the<br>
winning plan.</blockquote><div><br></div><div>Ok, that=E2=80=99s what I was=
 missing here, it saw the subplan in its options but the winning plan didn=
=E2=80=99t include it.=C2=A0 So =E2=80=9Cmatched/failed=E2=80=9D may produc=
e a plan where the target having been matched isn=E2=80=99t actually visibl=
e to the user.</div><div><br></div><div>Maybe add a note like this to pg_pl=
an_advice:</div><div><br></div><div>Generated advice is produced to a high =
level of specificity without knowing what limitations the advice interprete=
r has in applying that advice.=C2=A0 Therefore, generated advice targets ma=
y later fail for no other reason than cost-based decisions resulted in the =
originally chosen plan to no longer be chosen.=C2=A0 The planner will still=
 likely see the original target on a now losing plan and thus the advice fe=
edback will report matched even when the winning plan does not include the =
specific target.</div><div><br></div><div>David J.<br></div><div><br></div>

--0000000000004267a5064c2b04f0--