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>
 <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>
 <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>
 <CA+Tgmoau7yJtvbeH-0kPt1Q=Gt_ezRdgM35Q1=LT665U_86Etg@mail.gmail.com>
 <1299934.1773938807@sss.pgh.pa.us>
 <CA+TgmobOLrMn5jEinWNPL5SrDH1DPpo3a4j+S6-4yhsZwWgzLg@mail.gmail.com>
In-Reply-To: 
 <CA+TgmobOLrMn5jEinWNPL5SrDH1DPpo3a4j+S6-4yhsZwWgzLg@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Thu, 19 Mar 2026 16:38:23 -0400
Message-ID: 
 <CA+TgmoZUN8FT1Ah=m6Uis5bHa4FUa+_hMDWtcABG17toEfpiUg@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000805cd0064d668d81"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoZUN8FT1Ah%3Dm6Uis5bHa4FUa%2B_hMDWtcABG17toEfpiUg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Mar 19, 2026 at 1:17=E2=80=AFPM Robert Haas <robertmhaas@gmail.com>=
 wrote:
> > I would dig into why grison and schnauzer are failing this test,
> > except that I don't agree that we should be running it in the
> > first place.
>
> I'll go have a look.

grison failed like this:

 CREATE INDEX ON vaccluster(wrap_do_analyze(i));
+ERROR:  indexes on virtual generated columns are not supported

This is a surprising diff, because either that command is trying to
create an index on a virtual generated column, or it's not, and it
either is or isn't for every machine in the buildfarm and under
test_plan_advice or not. The problem might be that the first
TupleDescAttr(...) =3D=3D ATTRIBUTE_GENERATED_VIRTUAL test in DefineIndex
can be reached with attno =3D=3D 0, which seems like it will then access
memory that is not part of the TupleDesc. If that memory happens to
contain a 'v' in the right spot, this will happen. (Is it not viable
to have TupleDescAttr() assert that the second argument is >=3D 0?)

skink has a failure that looks like this:

+WARNING:  supplied plan advice was not enforced
+DETAIL:  advice NESTED_LOOP_MEMOIZE(nt) feedback is "matched, failed"

I think this is caused by a minor bug in the pgs_mask infrastructure.
get_memoize_path() exits quickly when outer_path->parent->rows < 2, on
the theory that the resulting path must lose on cost. But that
presumes that we could do a plain nested loop instead, i.e. that
PGS_NESTLOOP_PLAIN is set. And it might not be. Before the pgs_mask
stuff, that case couldn't happen: enable_nestloop=3Doff disabled all
nested loops, and enable_memoize=3Doff disabled only the memoized
version, but there wasn't any way to disable only the non-memoized
version (which, of course, was part of the point of the whole thing).
I think the fix is as attached.

Unfortunately, the other failures look like they are pointing to a
rather more serious problem. schnauzer's got a failure that looks like
this:

+WARNING:  supplied plan advice was not enforced
+DETAIL:  advice SEQ_SCAN(pg_trigger@exists_1) feedback is "matched, failed=
"
+advice NO_GATHER(pg_trigger@exists_1) feedback is "matched, failed"

And an older run on skink has a failure that looks like this:

+WARNING:  supplied plan advice was not enforced
+DETAIL:  advice SEQ_SCAN(pg_trigger@exists_6) feedback is "matched, failed=
"
+advice NO_GATHER(pg_trigger@exists_6) feedback is "matched, failed"

What these failures have in common is that both of them involve
selecting from information_schema.views. What "matched, failed" means
is that we saw the advice target during planning and tried to
influence the plan, but then observed that the final plan doesn't
respect the supplied advice. The query for information_schema.views
involves three subplans, so the fact that exists_6 is mentioned here
is a strong hint that the AlternativeSubPlan machinery is in play
here. The query is planned once, and one of the two alternative
subplans is chosen, generating advice for that plan. Upon replanning,
the other plan is chosen, so the subplan for which we have plan advice
doesn't appear in the query at all, leading to this. Now, you might
wonder how that's possible, considering that we're planning the same
query twice in a row with advice that isn't supposed to change
anything. My guess is that it's possible because these machines are
slow and other tests are running concurrently. If those other sessions
execute DDL, they can send sinval messages, which can cause the second
planning cycle to see different statistics than the first one. That
then means the plan can change in any way except for what the advice
system already knows how to control, and choice of AlternativeSubPlan
is not in that set of things. I think I actually saw a failure similar
to this once or twice locally during development, but that was back
when the code had a lot of bugs, and I assumed that the failure was
caused by some transient bug in whatever changes I was hacking on at
the time, or some other bug that I fixed later, rather than being a
real issue. I think the reason it doesn't happen very often is because
the statistics have to change enough at just the right moment and even
on slower buildfarm machines, most of the time, they don't.

It's not really clear to me exactly where to place the blame for this
category of failure. One view is that tests are being run in parallel
and I didn't think about that, and therefore this is a defect in the
test methodology that needs to be rectified somehow (hopefully not by
throwing it out). We might be able to fix that by running the test
suite serially rather than in parallel, although I expect that since
you (Tom) are already unhappy with the time this takes, that will
probably go over like a lead balloon. Another angle is to blame this
on the decision to assign different plan names to the different
subroots that we use for the alternative subplans. If we used
exists_1, exists_2, and exists_3 twice each instead of
exists_1..exists_6, this wouldn't happen, though that idea seems
questionable on theoretical grounds. A third possible take is that not
including choice-of-alternative-subplan in the initial scope was an
error.

As of this moment, I'm not really sure which way to jump. I need to
think further about what to do about this one. We can continue the
discussion about reducing the cost at the same time; again, I am
definitely not saying that it isn't legitimate to be concerned about
the CPU cycles expended running these tests, but those CPU cycles have
found three separate problems in two days, which is not nothing.

Separately, I am now 100% convinced that I need to go revise the
pg_collect_advice patch, because that adds yet another run of the core
regression tests, but for much less possibility of any real gain. I'll
go get rid of that and figure out what, if anything, to replace it
with.

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

--000000000000805cd0064d668d81
Content-Type: application/octet-stream;
	name="v1-0001-get_memoize_path-Don-t-exit-quickly-when-PGS_NEST.nocfbot"
Content-Disposition: attachment;
	filename="v1-0001-get_memoize_path-Don-t-exit-quickly-when-PGS_NEST.nocfbot"
Content-Transfer-Encoding: base64
Content-ID: <f_mmxvmrhr0>
X-Attachment-Id: f_mmxvmrhr0

RnJvbSAwY2ZjNTViZWNiNDcxM2VhNmQ4YTUyZDczNjhjOTMwMjdiZmNkYTYzIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBSb2JlcnQgSGFhcyA8cmhhYXNAcG9zdGdyZXNxbC5vcmc+CkRh
dGU6IFRodSwgMTkgTWFyIDIwMjYgMTU6MzU6NDkgLTA0MDAKU3ViamVjdDogW1BBVENIIHYxXSBn
ZXRfbWVtb2l6ZV9wYXRoOiBEb24ndCBleGl0IHF1aWNrbHkgd2hlbgogUEdTX05FU1RMT09QX1BM
QUlOIGlzIHVuc2V0LgoKVGhpcyBmdW5jdGlvbiBleGl0cyBlYXJseSBpbiB0aGUgY2FzZSB3aGVy
ZSB0aGUgbnVtYmVyIG9mIGlubmVyIHJvd3MKaXMgZXN0aW1hdGVkIHRvIGJlIGxlc3MgdGhhbiAy
LCBvbiB0aGUgdGhlb3J5IHRoYXQgaW4gdGhhdCBjYXNlIGEKTmVzdGVkIExvb3Agd2l0aCBpbm5l
ciBNZW1vaXplIG11c3QgbG9zZSB0byBhIHBsYWluIE5lc3RlZCBMb29wLgpCdXQgc2luY2UgY29t
bWl0IDQwMjBiMzcwZjIxNDMxNWI4YzEwNDMwMzAxODk4YWMyMTY1ODE0M2YgaXQncwpwb3NzaWJs
ZSBmb3IgYSBwbGFpbiBOZXN0ZWQgTG9vcCB0byBiZSBkaXNhYmxlZCwgd2hpbGUgYSBOZXN0ZWQg
TG9vcAp3aXRoIGlubmVyIE1lbW9pemUgaXMgc3RpbGwgZW5hYmxlZC4gSW4gdGhhdCBjYXNlLCB0
aGlzIHJlYXNvbmluZwppcyBub3QgdmFsaWQsIHNvIGFkanVzdCB0aGUgY29kZSBub3QgdG8gZXhp
dCBlYXJseSBpbiB0aGF0IGNhc2UuCgpUaGlzIGlzc3VlIHdhcyByZXZlYWxlZCBieSBhIHRlc3Rf
cGxhbl9hZHZpY2UgZmFpbHVyZSBvbiBidWlsZGZhcm0KbWVtYmVyIHNraW5rLCB3aGVyZSBORVNU
RURfTE9PUF9NRU1PSVpFKCkgY291bGRuJ3QgYmUgZW5mb3JjZWQgb24KcmVwbGFubmluZyBkdWUg
dG8gdGhpcyBlYXJseSBleGl0LgotLS0KIHNyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2pvaW5w
YXRoLmMgfCA3ICsrKysrKy0KIDEgZmlsZSBjaGFuZ2VkLCA2IGluc2VydGlvbnMoKyksIDEgZGVs
ZXRpb24oLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9qb2lucGF0
aC5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvam9pbnBhdGguYwppbmRleCAwNDQ1NjBk
YTdiZi4uZTg5NDBhMjNjNjQgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRo
L2pvaW5wYXRoLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvam9pbnBhdGguYwpA
QCAtNzI5LDggKzcyOSwxMyBAQCBnZXRfbWVtb2l6ZV9wYXRoKFBsYW5uZXJJbmZvICpyb290LCBS
ZWxPcHRJbmZvICppbm5lcnJlbCwKIAkgKiB0aGFuIG9uZSBpbm5lciBzY2FuLiAgVGhlIGZpcnN0
IHNjYW4gaXMgYWx3YXlzIGdvaW5nIHRvIGJlIGEgY2FjaGUKIAkgKiBtaXNzLiAgVGhpcyB3b3Vs
ZCBsaWtlbHkgZmFpbCBsYXRlciBhbnl3YXkgYmFzZWQgb24gY29zdHMsIHNvIHRoaXMgaXMKIAkg
KiByZWFsbHkganVzdCB0byBzYXZlIHNvbWUgd2FzdGVkIGVmZm9ydC4KKwkgKgorCSAqIEhvd2V2
ZXIsIGlmIHRoZSAicGxhaW4gbmVzdGVkIGxvb3AiIHN0cmF0ZWd5IGlzIGRpc2FibGVkLCB0aGVu
IGl0IGlzIG5vCisJICogbG9uZ2VyIGNlcnRhaW4gdGhhdCBhbnkgcGF0aCB3ZSdkIGNvbnN0cnVj
dCBoZXJlIHdvdWxkIGxvc2Ugb24gY29zdC4KKwkgKiBTbywgaW4gdGhhdCBjYXNlLCBjb250aW51
ZSBhbmQgbGV0IGNvc3QgY29tcGFyaXNvbiBzb3J0IHRoaW5ncyBvdXQuCiAJICovCi0JaWYgKG91
dGVyX3BhdGgtPnBhcmVudC0+cm93cyA8IDIpCisJaWYgKG91dGVyX3BhdGgtPnBhcmVudC0+cm93
cyA8IDIgJiYKKwkJKGV4dHJhLT5wZ3NfbWFzayAmIFBHU19ORVNUTE9PUF9QTEFJTikgPT0gMCkK
IAkJcmV0dXJuIE5VTEw7CiAKIAkvKgotLSAKMi41MS4wCgo=
--000000000000805cd0064d668d81--