MIME-Version: 1.0
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
 <CA+TgmoZf1F360z6jNVZyv7UF=URSNvCWbsCX7A_nsH=iaEqm0A@mail.gmail.com>
 <CAC0GmywDf5cs1_DoCyZwec=73Q5P7t88ikJ=LuKLXPO-Fd2LFQ@mail.gmail.com>
In-Reply-To: 
 <CAC0GmywDf5cs1_DoCyZwec=73Q5P7t88ikJ=LuKLXPO-Fd2LFQ@mail.gmail.com>
From: Hannu Krosing <hannuk@google.com>
Date: Sat, 1 Nov 2025 17:10:08 +0100
Message-ID: 
 <CAMT0RQRUgzc2kKLqs1goXZkMgrJ5p_TpP9BME6dn0i5RxKAH+A@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Robert Haas <robertmhaas@gmail.com>
Cc: Jakub Wartak <jakub.wartak@enterprisedb.com>,
 Alastair Turner <minion@decodable.me>,
	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/CAMT0RQRUgzc2kKLqs1goXZkMgrJ5p_TpP9BME6dn0i5RxKAH%2BA%40mail.gmail.com>
Precedence: bulk

This weas recently shared in LinkedIn
https://www.vldb.org/pvldb/vol18/p5126-bress.pdf

For example it says that 31% of all queries are metadata queries, 78%
have LIMIT, 20% of queries have 10+ joins, with 0.52% exceeding 100
joins. , 12% of expressions have depths between 11-100 levels, some
exceeding 100. These deeply nested conditions create optimization
challenges benchmarks don't capture.etc.

This reinforces my belief thet we either should have some kind of
two-level optimization, where most queries are handled quickly but
with something to trigger a more elaborate optimisation and
investigation workflow.

Or alternatively we could just have an extra layer before the query is
sent to the database which deals with unwinding the product of
excessively stupid query generators (usually, but not always, some BI
tools :) )


On Fri, Oct 31, 2025 at 10:18=E2=80=AFPM Alastair Turner <minion@decodable.=
me> wrote:
>
>
> On Fri, 31 Oct 2025, 12:51 Robert Haas, <robertmhaas@gmail.com> wrote:
>>
>> On Fri, Oct 31, 2025 at 5:59=E2=80=AFAM Jakub Wartak
>> <jakub.wartak@enterprisedb.com> wrote:
>> > > First, any form of user control over the planner tends to be a light=
ning rod for criticism around here.
>> >
>> > I do not know where this is coming from, but everybody I've talked to
>> > was saying this is needed to handle real enterprise databases and
>> > applications. I just really love it, how one could precisely adjust
>> > the plan with this even with the presence of heavy aliasing:
>
>
> I really like the functionality of the current patch as well, even though=
 I am suspicious of user control over the planner. By giving concise, preci=
se control over a plan, this allows people who believe they can out-plan th=
e planner to test their alternative, and possibly fail.
>
> Whatever other UIs and integrations you build as you develop this towards=
 you goal, please keep what's currently there user accessible. Not only for=
 testing code, but also for testing users' belief that they know better.
>
> Alastair