public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jakub Wartak <[email protected]>
To: Robert Haas <[email protected]>
Cc: Lukas Fittl <[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
Date: Tue, 20 Jan 2026 10:19:48 +0100
Message-ID: <CAKZiRmxmhjnKp-GMvdL-2jE-nic5g7-TLdsveirrbRv2zdSdyQ@mail.gmail.com> (raw)
In-Reply-To: <CA+TgmobRAjY+xs_=fKMJ4NgW_i4bMoD5kW7oKrGACLLcY04ysA@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+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>
On Mon, Jan 19, 2026 at 9:00 PM Robert Haas <[email protected]> wrote:
>
> On Mon, Jan 19, 2026 at 5:53 AM Jakub Wartak
> <[email protected]> wrote:
> > a) v10-0001 - any example producing such a dummy subplan? (whatever
> > 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 earlier.
> > c) In v10-0004, maybe in pathnodes.h we could use typedef enum rather 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 it
> > 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/[email protected]
>
> 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, 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() ..
>
> 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 just SET
> > and RESET afterwards (unless we get pinning of SQL plan id to advices) 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 execution 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.
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]
Subject: Re: pg_plan_advice
In-Reply-To: <CAKZiRmxmhjnKp-GMvdL-2jE-nic5g7-TLdsveirrbRv2zdSdyQ@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