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+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>
 <CAKFQuwaA09Kxfbv2tgh-SaaCXLD5+grYj3F9RNGQhTHsXz9ENw@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwaA09Kxfbv2tgh-SaaCXLD5+grYj3F9RNGQhTHsXz9ENw@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Wed, 4 Mar 2026 08:37:33 -0500
Message-ID: 
 <CA+Tgmob7McaYzdfDJSb4=6YcAq9BeKxFUcHegWYk=bJjzO5o1A@mail.gmail.com>
Subject: Re: pg_plan_advice (now with transparent SQL plan performance
 overrides - pg_stash_advice)
To: "David G. Johnston" <david.g.johnston@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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmob7McaYzdfDJSb4%3D6YcAq9BeKxFUcHegWYk%3DbJjzO5o1A%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 3, 2026 at 11:11=E2=80=AFPM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> Ok, that=E2=80=99s what I was missing here, it saw the subplan in its opt=
ions but the winning plan didn=E2=80=99t include it.  So =E2=80=9Cmatched/f=
ailed=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 knowi=
ng what limitations the advice interpreter has in applying that advice.  Th=
erefore, generated advice targets may later fail for no other reason than c=
ost-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 los=
ing plan and thus the advice feedback will report matched even when the win=
ning plan does not include the specific target.

There is doubtless lots of room for improvement in the documentation,
but this specific text doesn't seem like a good idea, because it's not
true in general. The whole point of test_plan_advice is to ensure that
generated advice targets *don't* fail, and with all the patches
applied, I get a clean run where every single advice target generated
by the main regression test suite can be successfully applied back to
the plan that generated it. I think the real issue here is that Jakub
has found a case where fiddling with the scan method causes the
optimal aggregation method to change, and the resulting weird behavior
stems from the fact that aggregation control is not supported. What
you want to be able to do is nail down the aggregation behavior first,
and then it would be clear whether to provide scan advice for t1 or
for t1@minmax_1.

But I just don't see what the big deal is here. One thing you can do
is provide advice for both t1 and t1@minmax_1 and then the advice will
be followed for whichever one appears in the final plan. The other
will be marked as failed, but you can decide to just ignore that.
Possibly you could even indirectly control whether the minmax path is
selected by advising an inefficient strategy for the one you don't
want to be be picked and a great strategy for the one you do want to
be picked. Also, I've personally never run into a real-world case
where the planner made a bad decision about whether to use a minmaxagg
or a regular agg, which is why all of my development time and effort
went into scans and joins and related topics where I do regularly see
things go wrong.

So at the risk of repeating myself: It is not in general true that you
should expect your advice to randomly not work, but that happens in
this case because aggregation control is not supported, which is a
documented limitation.

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