MIME-Version: 1.0
References: 
 <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
 <CA+HiwqEb8ZUfq92TMBNUtetfMV=50mHGTNrt8kOkoE9m_aqZvw@mail.gmail.com>
 <CA+TgmoY0J9==y93stO7QaAU+TyA-zm5wqsc4GWUa0hgzPAi_sw@mail.gmail.com>
In-Reply-To: 
 <CA+TgmoY0J9==y93stO7QaAU+TyA-zm5wqsc4GWUa0hgzPAi_sw@mail.gmail.com>
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 10 Dec 2025 16:09:24 -0500
Message-ID: 
 <CADkLM=d0yH-+z4T6SmuAjYvCj5n7PUVTUJVue48Y60yZFwRVhA@mail.gmail.com>
Subject: Re: pg_plan_advice
To: Robert Haas <robertmhaas@gmail.com>
Cc: Amit Langote <amitlangote09@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001bdca106459f72b3"
Archived-At: 
 <https://www.postgresql.org/message-id/CADkLM%3Dd0yH-%2Bz4T6SmuAjYvCj5n7PUVTUJVue48Y60yZFwRVhA%40mail.gmail.com>
Precedence: bulk

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

On Wed, Dec 10, 2025 at 9:54=E2=80=AFAM Robert Haas <robertmhaas@gmail.com>=
 wrote:

> On Wed, Dec 10, 2025 at 6:20=E2=80=AFAM Amit Langote <amitlangote09@gmail=
.com>
> wrote:
> > These are just high-level comments after browsing the patches and
> > reading some bits like pgpa_identifier to get myself familiarized with
> > the project.  I like that the key concept here is plan stability
> > rather than plan control, because that framing makes it easier to
> > treat this as infrastructure instead of policy.
>
> Thanks, I agree. I'm sure people will use this for plan control, but
> if you start with that, then it's really unclear what things you
> should allow to be controlled and what things not. Defining the focus
> as plan stability makes round-trip safety a priority and the scope of
> what you can request is what the planner could have generated had the
> costing come out just so. There's still some definitional questions at
> the margin, but IMHO it's much less fuzzy.
>

I couldn't have said this any better than Amit did. In my experience, lack
of a plan stability feature is far and away the most cited reason for not
porting to PostgreSQL. They want query plan stability first and foremost.
The amount of plan tweaking they do is actually pretty minimal, once they
get good-enough performance during user acceptance they want to encase
those query plans in amber because that's what the customer signed-off on.
After that, they're happy to scan the performance trendlines, and only make
tweaks when it's worth a change request.

But that's not to say I disagree with you categorically. Suppose we
> decided (and I'm not saying we should) to start showing relation
> identifiers in EXPLAIN output instead of identifying things in EXPLAIN
> output as we do today. Maybe we even decide to show elided subqueries
> and similar as first-class parts of the EXPLAIN output, also using
> relation identifier syntax. That would be a pretty significant change,
> and would destabilize a WHOLE LOT of regression test outputs, but then
> relation identifiers become a first-class PostgreSQL concept that
> everyone who looks at EXPLAIN output will encounter and, probably,
> come to understand.


I think the change would be worth the destabilization, because it makes it
so much easier to talk about complex query plans. Additionally, it would
make it reasonable to programmatically extract portions of a plan, allowing
for much more fine-grained regression tests regarding plans.

Showing the elided subqueries would be a huge benefit, outlining the
benefits that the planner is giving you "for free".


> > On the infrastructure patches (0001-0005): these look sensible. The
> > range table flattening info, elided node tracking, and append node
>

One thing I am curious about is that by tracking the elided nodes, would it
make more sense in the long run to have the initial post-naming plan tree
be immutable, and generate a separate copy minus the elided parts?

--0000000000001bdca106459f72b3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">On Wed, Dec 10, 2025 at 9:54=E2=80=AFAM Rober=
t Haas &lt;<a href=3D"mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On=
 Wed, Dec 10, 2025 at 6:20=E2=80=AFAM Amit Langote &lt;<a href=3D"mailto:am=
itlangote09@gmail.com" target=3D"_blank">amitlangote09@gmail.com</a>&gt; wr=
ote:<br>
&gt; These are just high-level comments after browsing the patches and<br>
&gt; reading some bits like pgpa_identifier to get myself familiarized with=
<br>
&gt; the project.=C2=A0 I like that the key concept here is plan stability<=
br>
&gt; rather than plan control, because that framing makes it easier to<br>
&gt; treat this as infrastructure instead of policy.<br>
<br>
Thanks, I agree. I&#39;m sure people will use this for plan control, but<br=
>
if you start with that, then it&#39;s really unclear what things you<br>
should allow to be controlled and what things not. Defining the focus<br>
as plan stability makes round-trip safety a priority and the scope of<br>
what you can request is what the planner could have generated had the<br>
costing come out just so. There&#39;s still some definitional questions at<=
br>
the margin, but IMHO it&#39;s much less fuzzy.<br></blockquote><div><br></d=
iv><div>I couldn&#39;t have said this any better than Amit did.=C2=A0In my =
experience, lack of a plan stability feature is far and away the most cited=
 reason for not porting to PostgreSQL. They=C2=A0want query plan stability =
first and foremost. The amount of plan tweaking they do is actually pretty =
minimal, once they get good-enough performance during user acceptance they =
want to encase those query plans in amber because that&#39;s what the custo=
mer signed-off on. After that, they&#39;re happy to scan the performance tr=
endlines, and only make tweaks when it&#39;s worth a change request.</div><=
div></div><div></div><div><br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">But that&#39;s not to say I disagree with you categorically. Sup=
pose we<br>
decided (and I&#39;m not saying we should) to start showing relation<br>
identifiers in EXPLAIN output instead of identifying things in EXPLAIN<br>
output as we do today. Maybe we even decide to show elided subqueries<br>
and similar as first-class parts of the EXPLAIN output, also using<br>
relation identifier syntax. That would be a pretty significant change,<br>
and would destabilize a WHOLE LOT of regression test outputs, but then<br>
relation identifiers become a first-class PostgreSQL concept that<br>
everyone who looks at EXPLAIN output will encounter and, probably,<br>
come to understand.</blockquote><div><br></div><div>I think the change woul=
d be worth the destabilization, because it makes it so much easier to talk =
about complex query plans. Additionally, it would make it reasonable to pro=
grammatically extract portions of a plan, allowing for much more fine-grain=
ed regression tests regarding plans.</div><div></div><div><br></div><div>Sh=
owing the elided subqueries would be a huge benefit, outlining the benefits=
 that the planner is giving you &quot;for free&quot;.</div><div>=C2=A0</div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left:1px solid rgb(204,204,204);padding-left:1ex">
&gt; On the infrastructure patches (0001-0005): these look sensible. The<br=
>
&gt; range table flattening info, elided node tracking, and append node<br>=
</blockquote><div><br></div><div><div>One thing I am curious about is that =
by tracking the elided nodes,=20
would it make more sense in the long run to have the=C2=A0initial post-nami=
ng
 plan tree be immutable, and generate a separate copy minus the elided=20
parts?</div><div></div></div></div></div>

--0000000000001bdca106459f72b3--