Re: pg_plan_advice

On Wed, Dec 10, 2025 at 9:54 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Dec 10, 2025 at 6:20 AM Amit Langote <amitlangote09(at)gmail(dot)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?