public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Robert Haas <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice
Date: Sun, 5 Apr 2026 09:57:13 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+TgmoaPgXYYEivQWxyVV=eYhN+T9JAgS9Xe4m7g9wVitVPF8g@mail.gmail.com>
References: <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
<CA+TgmoaK=4w7-qknUo3QhUJ53pXZq=c=KgZmRyD+k7ytqfmgSg@mail.gmail.com>
<CAP53Pkz3DSFaaowYvbO5LULf3NhydD_UhHkighfWf6_pwxiqUw@mail.gmail.com>
<CA+TgmoZ45n5jaNKKgbbj4-kYV8WsPvUn=Z8HnoZ7tUb_p9WKXg@mail.gmail.com>
<CA+TgmoYuWmN-00Ec5pY7zAcpSFQUQLbgAdVWGR9kOR-HM-fHrA@mail.gmail.com>
<CAP53Pkzn_wZ-R-cPdD9XSQ9+myPUUsPMMqVBPNG3XWXhgfm1-Q@mail.gmail.com>
<CA+Tgmobxbju8PrY_NULtPr7b7UShp4+Jqibm2Bou8TVS69gObQ@mail.gmail.com>
<[email protected]>
<CA+TgmoadkuOMJjvYe2h6aznHFeePprGEQ8CgUpRK=47sB6DMAg@mail.gmail.com>
<[email protected]>
<CA+TgmoY+g1u-fN=3igXG-8u0Ho3V4u-ooWXCj-FQ9DA=uGek9g@mail.gmail.com>
<[email protected]>
<[email protected]>
<CA+Tgmoben3_8rZbQ2X2+gOBOFpOgkc9hx3-z9e_Q_kHCfuW25g@mail.gmail.com>
<[email protected]>
<CA+TgmoaPgXYYEivQWxyVV=eYhN+T9JAgS9Xe4m7g9wVitVPF8g@mail.gmail.com>
On 5/4/26 00:52, Robert Haas wrote:
> On Sat, Apr 4, 2026 at 5:02 PM Andrei Lepikhov <[email protected]> wrote:
>> That’s exactly what concerns me. I see it as a potential design flaw if
>> the extension has to make assumptions about possible plan configurations.
>> I’m not sure how it works in detail, of course. However, when I designed
>> Postgres replanning in the past, and made similar core changes to what
>> you’ve done for pg_plan_advice, this kind of problem couldn’t have
>> happened. So, I think it’s worth questioning the current approach and
>> looking for other options.
>
> I mean, any plan stability feature is intrinsically tied to a
> particular planner. Nobody thinks you can use Aurora Postgres's Query
> Plan Management feature with MySQL or DB2 or Oracle. Those products
I don’t expect any Postgres extension to work in DB2.
These optimisations are simple. Here, I provided the optimiser with one
extra path that it skipped itself just to reduce computational overhead
- nice in the general case, but not ok in analytics. This extension of
planning scope allowed the optimiser to build JOIN over the Sort
operator, which didn’t change the main logic at all. I followed the
usual cost-based model and used add_path.
Another optimisation improves Memoize so it can run on top of SubPlan
when the cost model predicts many repeated parameter values. One more
extension uses MergeJoin estimation on the required values of its inputs
to determine how many tuples are needed from each input, which adds
kinda 'soft' LIMIT emerged from the plan structure ... The Append node
serves as the backbone of any partitioning or sharding setup, but
contributors often overlook it, and we use multiple extra optimisations
here too.
There’s a lot to say about branched out-of-core optimisations
infrastructure, but it’s clear that supporting analytical workloads
means adding extra features. Developers usually stick to standard
Postgres practices, cost model and routines providing the planner with
alternatives without forcing any 'magical' paths. So, they expect
built-in extensions not to interfere with their code by design.
Looking back at the pg_plan_advice development cycle, I don’t see many
discussions about the design. It seems unusual given how complex the
planner's structure is. It makes sense to follow the typical way and let
it serve out of the contrib for some time and see if it works well.
Introducing such a module into the core would effectively cancel
alternative solutions, as seen with PGSS. Therefore, it is important to
ensure the code is well-designed before proceeding. Do you agree?
--
regards, Andrei Lepikhov,
pgEdge
view thread (184+ 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]
Subject: Re: pg_plan_advice
In-Reply-To: <[email protected]>
* 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