public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Robert Haas <[email protected]>
Cc: Jakub Wartak <[email protected]>
Cc: Alexandra Wang <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Jacob Champion <[email protected]>
Cc: Dian Fay <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice (now with transparent SQL plan performance overrides - pg_stash_advice)
Date: Tue, 3 Mar 2026 12:28:16 -0700
Message-ID: <CAKFQuwb2U_X6zMqY5aSewjTTURQyXR3xY-avzJMmM+eDZCNCjQ@mail.gmail.com> (raw)
In-Reply-To: <CA+TgmoYO0qtqz+V7S4q0e_dLhLrrsMxA51t5wks_y8Skv6cdRQ@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>
<[email protected]>
<CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
<CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
<[email protected]>
<CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
<[email protected]>
<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>
<[email protected]>
<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>
On Tuesday, March 3, 2026, Robert Haas <[email protected]> wrote:
> >
> > However with below SEQ_SCAN is applied/matched, but marked as failed
> > (so bug?):
> >
> > postgres=# set pg_plan_advice.advice to 'SEQ_SCAN(t1@minmax_1)';
> > SET
> > postgres=# explain (plan_advice, costs off) select max(id) from t1;
> > QUERY PLAN
> > -----------------------------------------------
> > Aggregate
> > -> Seq Scan on t1
> > Supplied Plan Advice:
> > SEQ_SCAN(t1@minmax_1) /* matched, failed */
> > Generated Plan Advice:
> > SEQ_SCAN(t1)
> > NO_GATHER(t1)
>
> I think this is just out of scope for now. It's documented that we
> don't have a way of controlling aggregation behavior at present. Here
> again, we can do more things in future releases, but it's too late to
> add more to the scope for this release. There's still plenty of time
> to fix bugs, but this isn't a bug. We'd need a whole lot of new
> machinery to prevent this sort of thing, including new core hooks and
> new syntax. Here again, we have the freedom to decide that I chose the
> scope wrongly and that this is therefore not shippable as is, but I do
> not think there is time to significantly expand the scope at this
> point.
>
>
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
target produces "matched" when the subplan doesn't appear.
SEQ_SCAN(t1@minmax_1) /* matched, failed */
Maybe we need to do something like:
relname - matches anywhere in the plan tree
relname@somewhere - only looks at "somewhere" for matches; absence of
somewhere results in "not matched" (the expected feedback for the
advice/query combination above).
This would necessitate needing some way to specify "top-level" after the
"@" symbol - leaving it blank would suffice. I haven't worked through
"directly at the named subplan" versus "the named subplan and any
descendants"...
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.
David J.
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_plan_advice (now with transparent SQL plan performance overrides - pg_stash_advice)
In-Reply-To: <CAKFQuwb2U_X6zMqY5aSewjTTURQyXR3xY-avzJMmM+eDZCNCjQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox