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>
 <CAKZiRmzpO=PpLmXEQUO5CETzT0mmLhT5P656hetseKQUgZ6BQg@mail.gmail.com>
 <CA+TgmobRAjY+xs_=fKMJ4NgW_i4bMoD5kW7oKrGACLLcY04ysA@mail.gmail.com> <CAKZiRmxmhjnKp-GMvdL-2jE-nic5g7-TLdsveirrbRv2zdSdyQ@mail.gmail.com>
In-Reply-To: <CAKZiRmxmhjnKp-GMvdL-2jE-nic5g7-TLdsveirrbRv2zdSdyQ@mail.gmail.com>
From: Ajay Pal <ajay.pal.k@gmail.com>
Date: Wed, 21 Jan 2026 17:21:57 +0530
Message-ID: <CABRHmyuoOBXG_SVG=xpH+5+hacqfofniYSZvyJBRL7EadphcVw@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Jakub Wartak <jakub.wartak@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.com>, 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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CABRHmyuoOBXG_SVG%3DxpH%2B5%2BhacqfofniYSZvyJBRL7EadphcVw%40mail.gmail.com>
Precedence: bulk

Subsequent testing revealed that UNION operations involving constants
which enforce empty subplans result in the generated partition-wise
plan not being recognized by the planner.
Below is the query and output for more details.

CREATE TABLE a_test (id int, category text) PARTITION BY LIST (category);
CREATE TABLE a_active PARTITION OF a_test FOR VALUES IN ('active');
CREATE TABLE a_retired PARTITION OF a_test FOR VALUES IN ('retired');

postgres=3D# set pg_plan_advice.advice=3D'PARTITIONWISE(unnamed_subquery)';
SET
postgres=3D# EXPLAIN (PLAN_ADVICE)
SELECT * FROM a_active WHERE id =3D 1
UNION ALL
SELECT * FROM a_active WHERE id =3D 2 AND 1=3D0; -- Constant false forces
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on a_active  (cost=3D0.00..25.88 rows=3D6 width=3D36)
   Filter: (id =3D 1)
 Supplied Plan Advice:
   PARTITIONWISE(unnamed_subquery) /* not matched */
 Generated Plan Advice:
   SEQ_SCAN(a_active@unnamed_subquery)
   PARTITIONWISE(unnamed_subquery)
   NO_GATHER(a_active@unnamed_subquery)
(8 rows)

Thanks
Ajay

On Tue, Jan 20, 2026 at 2:50=E2=80=AFPM Jakub Wartak
<jakub.wartak@enterprisedb.com> wrote:
>
> On Mon, Jan 19, 2026 at 9:00=E2=80=AFPM Robert Haas <robertmhaas@gmail.co=
m> wrote:
> >
> > On Mon, Jan 19, 2026 at 5:53=E2=80=AFAM Jakub Wartak
> > <jakub.wartak@enterprisedb.com> wrote:
> > >    a) v10-0001 - any example producing such a dummy subplan? (whateve=
r
> > > I've tried I cannot come up with one)
> [..]
> > EXPLAIN SELECT * FROM random() UNION SELECT * FROM random() WHERE false=
;
>
> Oh well, that was easy, thanks! Now I see `RTI 3 (function,
> in-from-clause): /  Subplan: setop_2 (dummy)`
>
> I don't have any further insights on v10-[124] other than mentioned earli=
er.
>
> > >    c) In v10-0004, maybe in pathnodes.h we could use typedef enum rat=
her than
> > >       list of #defines? (see attached)
> >
> > I personally hate that style and I think Andres loves it. Whee!
>
> Oh, ok, nvm, but while two of You we are at this, vim or emacs ? ;)
> /me ducks & covers
>
> > > 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 not
> > >    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
> >
> > I think these numbers look pretty good. I mean, there is obviously
> > room for improvement. We should look at where the CPU cycles are going
> > in the oq2 case and try to optimize. But even without that, it's not
> > terrible, IMHO.
> >
> > > 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 i=
t
> > > gets ~890TPS
> >
> > I'm not sure exactly where the CPU cycles are going here, but one
> > known problem is that we have to re-parse the advice string for every
> > query. This thread discusses the challenges of creating some
> > infrastructure that would allow us to avoid that:
> >
> > http://postgr.es/m/f87504a6-9dfd-4467-89de-84232cb54f72@gmail.com
> >
> > Maybe I should start thinking about other ways to avoid that overhead,
>
> Meh...
>
> > because that thread doesn't seem to be progressing much, but maybe the
> > reparsing isn't even the main problem.
> > > - in both cases (empty and set) the bottleneck seems to in palloc0, b=
ut
> > >     empty plan_advice: it's more like palloc0() <- newNode() <-
> > > create_index_path()
> > >     <- build_index_paths()
> > >     with plan_advice set: palloc0() <- newNode() <- create_nestloop_p=
ath() ..
> >
> > I've also seen some palloc-related issues with the patch -- it has to
> > build some data structures and that does palloc stuff -- but there
> > shouldn't really be any difference in the code paths you show here.
> > That's just core code, which should be doing the same thing either way
> > if the advice is not relevant.
>
> Yeah, in both it looks like memory allocation and lots of newNode()
> called , quite expected.
>
> > > - so if anything people should not put something there blindly, but j=
ust SET
> > >   and RESET afterwards (unless we get pinning of SQL plan id to advic=
es) as
> > >   this might have cost in high-TPS scenarios.
> >
> > Yes, I think that's definitely a potential issue. I'd like the
> > overhead of this module to be as low as possible, but it's bound to
> > have at least some overhead, and people will have to decide whether
> > it's worth it.
>
> I think we should simply ignore, and maybe later just note the fact this =
is
> not free with a single sentence in some docs for 0005. I was just curious=
 of the
> impact and this was measured using pure EXPLAIN (so without query executi=
on to
> measure impact of non-empty pg_plan_advice), I'm assuming that in
> properly managed
> systems execution part will always dominate the workload anyway and
> one should be
> using prepared statements anyway.
>
> -J.
>
>