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+njnRGcomnxTY6vsEW3wWigc0ruB0EyWFpb+PVVE8sWpw@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>
 <CAKZiRmz4zrZfx_wgzkTD88HzKiA22HKPUnubR3is0bjKra6Utg@mail.gmail.com>
 <CA+Tgmobp8gcB9DOxNUL10xAXXq=ya6oHDUJqtNLf_3ojxm5-fQ@mail.gmail.com>
 <CAKZiRmznM4cc_N5qQBgEdDj9F5J8=zTJbWm9erZzhp9jP7LMbg@mail.gmail.com> <CA+Tgmoa5HjTnBMNQuW5QrCUacC9gdok+02a2Y9TTGWtTQ6OBvQ@mail.gmail.com>
In-Reply-To: <CA+Tgmoa5HjTnBMNQuW5QrCUacC9gdok+02a2Y9TTGWtTQ6OBvQ@mail.gmail.com>
From: Jakub Wartak <jakub.wartak@enterprisedb.com>
Date: Mon, 19 Jan 2026 11:53:38 +0100
Message-ID: <CAKZiRmzpO=PpLmXEQUO5CETzT0mmLhT5P656hetseKQUgZ6BQg@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Robert Haas <robertmhaas@gmail.com>
Cc: Lukas Fittl <lukas@fittl.com>, 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/mixed; boundary="0000000000009f59120648bb815e"
Archived-At: <https://www.postgresql.org/message-id/CAKZiRmzpO%3DPpLmXEQUO5CETzT0mmLhT5P656hetseKQUgZ6BQg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jan 15, 2026 at 3:41=E2=80=AFPM Robert Haas <robertmhaas@gmail.com>=
 wrote:
>
[..]
>
> So here's v10.
[..]
> I'm very appreciative to everyone for all the testing and reports
> about 0005; I still do need some substantive code review particularly
> of 0001.

Hi,

1. With v10 all my minimal TPC-H checks are OK (both with stats/without sta=
ts,
   parallel and non-parallel).

2. I couldn't find any glaring issue during code review of v10-000[124]. Bu=
t I
   have some questions:
   a) v10-0001 - any example producing such a dummy subplan? (whatever
I've tried I
      cannot come up with one)
   b) v10-0001 - maybe we could add a comment nearby "dummy" struct
member to look
      on pgpa_plan_walker() on example how to use it, but that's part of v5=
 and
      contrib...
   c) In v10-0004, maybe in pathnodes.h we could use typedef enum rather th=
an
      list of #defines? (see attached)

3. Yes, I could too also repro Jacob's and get the same failure, so it's re=
al:
   TRAP: failed Assert("child_target->ttype =3D=3D PGPA_TARGET_IDENTIFIER")=
,
   File: "../contrib/pg_plan_advice/pgpa_walker.c", Line: 679, PID: 32344

4. Some raw perf numbers on non-assert builds (please ignore +/- 3%
jumps), it just hurts
   in one scenario where oq2 drops like 9% of juice (quite expected, it's n=
ot
   an issue to be, just posting full results)

tps                           oq1  oq2    oq3  oq4
master                        41   14745  439  435
master+v10-000[1-4]           42   15055  439  432
master+v10full                41   14734  429  437
master+v10full+loaded         42   15014  442  438
master+v10full+loaded+advice  41   13481  424  439

(same but in percentages)
%tps_to_master                oq1  oq2    oq3  oq4
master                        100  100    100  100
master+v10-000[1-4]           102  102    100  99
master+v10full                100  100    98   100
master+v10full+loaded         102  102    101  101
master+v10full+loaded+advice  100  91     97   101


Some explanation:
* oq =3D> my shortcut for Optimizer stress Query (to disambiguate from
TPC-H Queries)
* master+v10full+loaded - shared_preloaded_libraries was set to
  have pg_plan_advice
* master+v10full+loaded+advice - as above, but with system-wide GUC set
  to lengthy and irrelevant (as none of the queries used such aliases)
     JOIN_ORDER(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
     NESTED_LOOP_PLAIN(x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
     SEQ_SCAN(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
     NO_GATHER(x1 x2 x3 x4 x5 x6 x7 x8 x9 x10 x11)
  The idea was to see how that impacts oq1..4 while not using those.

So out of curiosity the oq2 on 1 CPU core behavior looks like below:
- no advices --> ~1000 TPS
- enabled pg_plan_advice.advice to lengthy, but unrelated thing and it
gets ~890TPS
- in both cases (empty and set) the bottleneck seems to in palloc0, but
    empty plan_advice: it's more like palloc0() <- newNode() <-
create_index_path()
    <- build_index_paths()
    with plan_advice set: palloc0() <- newNode() <- create_nestloop_path() =
..
- so if anything people should not put something there blindly, but just SE=
T
  and RESET afterwards (unless we get pinning of SQL plan id to advices) as
  this might have cost in high-TPS scenarios.

-- details about suite for benchmarking:
SELECT 'CREATE TABLE t' || g || ' (id int primary key, val int)'
FROM generate_series(1, 11) g;
\gexec
-- 1k parts
CREATE TABLE tstresspart (id int, val text) PARTITION BY RANGE (id);
SELECT 'CREATE TABLE tpart' || g || ' PARTITION OF tstresspart FOR
VALUES FROM ('
|| g*10 || ') TO (' || (g+1)*10 || ')' FROM generate_series(1, 1000) g;
\gexec

-- oq1, obtakes ~500ms, below GEQO threshold
EXPLAIN SELECT * FROM t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11
WHERE t1.id =3D t2.id AND t2.id =3D t3.id AND t3.id =3D t4.id
  AND t4.id =3D t5.id AND t5.id =3D t6.id AND t6.id =3D t7.id
  AND t7.id =3D t8.id AND t8.id =3D t9.id AND t9.id =3D t10.id
  AND t10.id =3D t11.id;

-- oq2, hit nested subqueries hard
EXPLAIN SELECT * FROM t1
    WHERE id IN (SELECT id FROM t2
    WHERE id IN (SELECT id FROM t3
    WHERE id IN (SELECT id FROM t4
    WHERE id IN (SELECT id FROM t5
    WHERE id IN (SELECT id FROM t6
    WHERE id IN (SELECT id FROM t7
    WHERE id IN (SELECT id FROM t8
    WHERE id IN (SELECT id FROM t9
    WHERE id IN (SELECT id FROM t10
    WHERE id IN (SELECT id FROM t11))))))))))
OR id IN (SELECT val FROM t1);

-- oq3, part stress test, no part pruning
EXPLAIN SELECT * FROM tstresspart WHERE id =3D (SELECT (random()*1000));

-- oq4, stress test IN/VALUES
perl -e 'print "SELECT * FROM t1 WHERE id IN ("; for(1..40000)
{ print "$_"; print "," if $_ !=3D 40000 }; print ");"' > oq4.sql

-J.

--0000000000009f59120648bb815e
Content-Type: text/plain; charset="US-ASCII"; name="typedef_enum_pgsscantype.h.txt"
Content-Disposition: attachment; filename="typedef_enum_pgsscantype.h.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_mkl1ob3d0>
X-Attachment-Id: f_mkl1ob3d0

ZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL25vZGVzL3BhdGhub2Rlcy5oIGIvc3JjL2luY2x1ZGUv
bm9kZXMvcGF0aG5vZGVzLmgKaW5kZXggYzE3NWVlOTViNjguLjc3MjMwNmQ4MjYyIDEwMDY0NAot
LS0gYS9zcmMvaW5jbHVkZS9ub2Rlcy9wYXRobm9kZXMuaAorKysgYi9zcmMvaW5jbHVkZS9ub2Rl
cy9wYXRobm9kZXMuaApAQCAtNjMsMjUgKzYzLDI3IEBACiAgKiBhbGwgb2YgdGhlIG90aGVycywg
YnV0IHRoYXQgZG9lc24ndCB3b3JrIGhlcmU6IHVuc2V0dGluZyBQR1NfU0VRU0NBTiwKICAqIGZv
ciBpbnN0YW5jZSwgd291bGQgZGlzYWJsZSBib3RoIHBhcnRpYWwgYW5kIG5vbi1wYXJ0aWFsIHNl
cXVlbnRpYWwgc2NhbnMuCiAgKi8KLSNkZWZpbmUgUEdTX1NFUVNDQU4JCQkJCTB4MDAwMDAwMDEK
LSNkZWZpbmUgUEdTX0lOREVYU0NBTgkJCQkweDAwMDAwMDAyCi0jZGVmaW5lIFBHU19JTkRFWE9O
TFlTQ0FOCQkJMHgwMDAwMDAwNAotI2RlZmluZSBQR1NfQklUTUFQU0NBTgkJCQkweDAwMDAwMDA4
Ci0jZGVmaW5lIFBHU19USURTQ0FOCQkJCQkweDAwMDAwMDEwCi0jZGVmaW5lIFBHU19GT1JFSUdO
Sk9JTgkJCQkweDAwMDAwMDIwCi0jZGVmaW5lIFBHU19NRVJHRUpPSU5fUExBSU4JCQkweDAwMDAw
MDQwCi0jZGVmaW5lIFBHU19NRVJHRUpPSU5fTUFURVJJQUxJWkUJMHgwMDAwMDA4MAotI2RlZmlu
ZSBQR1NfTkVTVExPT1BfUExBSU4JCQkweDAwMDAwMTAwCi0jZGVmaW5lIFBHU19ORVNUTE9PUF9N
QVRFUklBTElaRQkweDAwMDAwMjAwCi0jZGVmaW5lIFBHU19ORVNUTE9PUF9NRU1PSVpFCQkweDAw
MDAwNDAwCi0jZGVmaW5lIFBHU19IQVNISk9JTgkJCQkweDAwMDAwODAwCi0jZGVmaW5lIFBHU19B
UFBFTkQJCQkJCTB4MDAwMDEwMDAKLSNkZWZpbmUgUEdTX01FUkdFX0FQUEVORAkJCTB4MDAwMDIw
MDAKLSNkZWZpbmUgUEdTX0dBVEhFUgkJCQkJMHgwMDAwNDAwMAotI2RlZmluZSBQR1NfR0FUSEVS
X01FUkdFCQkJMHgwMDAwODAwMAotI2RlZmluZSBQR1NfQ09OU0lERVJfSU5ERVhPTkxZCQkweDAw
MDEwMDAwCi0jZGVmaW5lIFBHU19DT05TSURFUl9QQVJUSVRJT05XSVNFCTB4MDAwMjAwMDAKLSNk
ZWZpbmUgUEdTX0NPTlNJREVSX05PTlBBUlRJQUwJCTB4MDAwNDAwMDAKK3R5cGVkZWYgZW51bSB7
CisJUEdTX1NFUVNDQU4JCQkJCT0gMSA8PCAwLAorCVBHU19JTkRFWFNDQU4JCQkJPSAxIDw8IDEs
CisJUEdTX0lOREVYT05MWVNDQU4JCQk9IDEgPDwgMiwKKwlQR1NfQklUTUFQU0NBTgkJCQk9IDEg
PDwgMywKKwlQR1NfVElEU0NBTgkJCQkJPSAxIDw8IDQsCisJUEdTX0ZPUkVJR05KT0lOCQkJCT0g
MSA8PCA1LAorCVBHU19NRVJHRUpPSU5fUExBSU4JCQk9IDEgPDwgNiwKKwlQR1NfTUVSR0VKT0lO
X01BVEVSSUFMSVpFCT0gMSA8PCA3LAorCVBHU19ORVNUTE9PUF9QTEFJTgkJCT0gMSA8PCA4LAor
CVBHU19ORVNUTE9PUF9NQVRFUklBTElaRQk9IDEgPDwgOSwKKwlQR1NfTkVTVExPT1BfTUVNT0la
RQkJPSAxIDw8IDEwLAorCVBHU19IQVNISk9JTgkJCQk9IDEgPDwgMTEsCisJUEdTX0FQUEVORAkJ
CQkJPSAxIDw8IDEyLAorCVBHU19NRVJHRV9BUFBFTkQJCQk9IDEgPDwgMTMsCisJUEdTX0dBVEhF
UgkJCQkJPSAxIDw8IDE0LAorCVBHU19HQVRIRVJfTUVSR0UJCQk9IDEgPDwgMTUsCisJUEdTX0NP
TlNJREVSX0lOREVYT05MWQkJPSAxIDw8IDE2LAorCVBHU19DT05TSURFUl9QQVJUSVRJT05XSVNF
CT0gMSA8PCAxNywKKwlQR1NfQ09OU0lERVJfTk9OUEFSVElBTAkJPSAxIDw8IDE4Cit9IFBnc1Nj
YW5UeXBlOwogCiAvKgogICogQ29udmVuaWVuY2UgbWFjcm9zIGZvciB1c2VmdWwgY29tYmluYXRp
b24gb2YgdGhlIGJpdHMgZGVmaW5lZCBhYm92ZS4K
--0000000000009f59120648bb815e--