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>
In-Reply-To: 
 <CAKFQuwb2U_X6zMqY5aSewjTTURQyXR3xY-avzJMmM+eDZCNCjQ@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Tue, 3 Mar 2026 22:36:13 -0500
Message-ID: 
 <CA+TgmoZBPeQtpoq+AQg2yYxoFPQbXmpEbcYSV6M4mySxHTPZiw@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%2BTgmoZBPeQtpoq%2BAQg2yYxoFPQbXmpEbcYSV6M4mySxHTPZiw%40mail.gmail.com>
Precedence: bulk

On Tue, Mar 3, 2026 at 2:28=E2=80=AFPM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I mostly get why specifying an index that doesn't exist as part of advice=
, alongside a target relation, produces "matched" along with "inapplicable"=
 and "failed".
>
> INDEX_SCAN(f no_such_index) /* matched, inapplicable, failed */
>
> But less understandable is why a failure to match a subplan-qualified tar=
get produces "matched" when the subplan doesn't appear.
>
> 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. This case is "matched" because we saw t1@minmax_1 get
planned, but it's "failed" because the non-minmax strategy then won on
cost.

Aside: This is actually an instance of a very common problem into
which I have already invested an enormous amount of time and energy.
Suppose that for some operation X (which might be a scan or a join or
any other kind of thing) you tell the planner that you want it to use
strategy S. If strategy S is not what was going to be chosen anyway,
you've just increased the cost of X. Unsurprisingly, the planner's
response to that is very often to choose not to perform X at all.
Otherwise, it ends up looking like the planner has just made an
end-run around the advice. For example, HASH_JOIN(X) says "put X on
the inner side of a hash join". Obviously, this means that when we see
a join with X as the inner rel, we should disallow all join strategies
other than hash join. Less obviously, it also means that when we see a
join with X as the outer rel, we shouldn't allow *any* join
strategies. If you don't do that, you get a problem very similar to
what you're complaining about here. The difference is that join
control is supported by the patch set, and so I have put in the work
to avoid instances of this problem that occur in that case, and
aggregate control is not, so I haven't.

> Maybe we need to do something like:
>
> relname - matches anywhere in the plan tree
> relname@somewhere - only looks at "somewhere" for matches; absence of som=
ewhere results in "not matched" (the expected feedback for the advice/query=
 combination above).

This would not work in general; "anywhere in the plan tree" is too
broad a scope, and it would be easy to construct an example where it
falsely matches an unrelated part of the query. I think that the
solution here probably looks more like letting the user write
AGGREGATE_PLAIN(something) or AGGREGATE_MINMAX(something), but to make
it really work, we would need to figure out what the "something"
should be, and also remember to account for partitionwise aggregation,
eager aggregation, partial aggregation, hashed aggregation, sorted
aggregation, plain aggregation, and mixed aggregation, some of which
can be combined with some of the others but not all of the others, and
then we would need to add design and implement hooks in core to allow
that advice to be enforced, and then we would need to get
pg_plan_advice to properly accept the syntax and enforce it and also
be able to generate the syntax from a finished plan tree, and then
document all that and add tests. I think that work is worth doing,
but, again, I think it would be a much better idea to spend the next
few weeks trying to figure out whether it makes sense to commit what
I've got, and perform any necessarily stabilization of that
functionality, than to keep saying "hey, maybe you should radically
expand the scope." It's probably a six-month project to get all that
working, and we don't have that time for v19.

> If keeping the status quo the existing behavior should be documented. The=
 existing wording for not matched; "or it may occur if the relevant portion=
 of the query was not planned," seems to be the one that covers this case.

That's definitely relevant here, although these specific examples seem
to have more to do with "failed" than "not matched".

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