Обсуждение: Expose custom planning data in EXPLAIN

Hi,

Background and motivation
-------------------------

This feature is inspired by the pg_overexplain, new EXPLAIN-related 
hooks and recent change of the Memoize node representation (4bc62b86849).

Based on user reports from the production system, we typically receive 
no more than an 'explain verbose analyse' output to identify the issue's 
origin. It is obvious to me that the detailisation of the EXPLAIN format 
will never be enough. Even more, each new parameter, adding more 
information, also complicates life for some people, flooding the screen 
with unnecessary (for them) data.

With extensible EXPLAIN options and per-node/summary hooks, we can add 
as many details as needed using modules. However, there is one 
limitation: we can only explore Plan and PlanState data. If something 
isn't transferred from the planning stage to the plan, we won't have a 
way to expose this data.
For example, it is sometimes unclear why the optimiser chose 
IncrementalSort or [did not choose] HashJoin, as we don't know the 
ngroups estimation used at this specific point of the plan.

Design Overview
---------------

It appears that the only two changes required to enable the feature are 
a hook and a field in the Plan node. In this patch, I have chosen to add 
the hook to the copy_generic_path_info routine to limit its usage for 
tracking purposes only. Also, I extended its interface with the 
PlannerInfo pointer, which may be helpful in many cases. The new extlist 
field in the Plan structure should contain (by convention) extensible 
nodes only to let modules correctly pick their data. Also, it simplifies 
the logic of the node serialisation.

An additional motivation for choosing Extensible Node is its lack of 
core usage, which makes it seem unpolished and requires copying a 
significant amount of code to use. This patch highlights this imperfection.

Tests
-----

To demonstrate its real-life application, I added an example to 
pg_overexplain. Here, a ngroups value is computed, stored in the Plan 
node, and exposed in explain. Also, it is a test for the ExtensionNode 
machinery.

Downsides
-----------

1. Growth of the plan node
2. Read/write extensible node - what if reading worker (or backend ?) 
doesn't have the module installed?
3. The point for the hook call.

The first issue is quite limited because the only version of the plan 
exists, in contrast to the multiple paths.
The second issue is a little more complicated. However, I believe the 
issue could be resolved by allowing extensions to determine the logic 
for serialising their ExtensibleNode.
The selection of the point for the hook appears to be quite strict. It 
does not permit any extensions to alter the final plan or disrupt its 
consistency, except for some cost data. However, it does allow for 
tracking the decisions made during the planning phase.

See the patch attached.

-- 
regards, Andrei Lepikhov

On Wed, Aug 13, 2025 at 9:51 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
> It appears that the only two changes required to enable the feature are
> a hook and a field in the Plan node. In this patch, I have chosen to add
> the hook to the copy_generic_path_info routine to limit its usage for
> tracking purposes only. Also, I extended its interface with the
> PlannerInfo pointer, which may be helpful in many cases. The new extlist
> field in the Plan structure should contain (by convention) extensible
> nodes only to let modules correctly pick their data. Also, it simplifies
> the logic of the node serialisation.
>
> An additional motivation for choosing Extensible Node is its lack of
> core usage, which makes it seem unpolished and requires copying a
> significant amount of code to use. This patch highlights this imperfection.

This seems quite closely related to what I propose here:

http://postgr.es/m/CA+TgmoYxfg90rw13+JcYwn4dwSC+agw7o8-A+fA3M0fh96pg8w@mail.gmail.com

There are some differences. In my proposal, specifically in v3-0004, I
just add a single member to the PlannedStmt, and assume that the code
can find a way to jam all the state it cares about into that single
field, for example by creating a list of plan_node_id values and a
list of associated nodes that can carry the corresponding data.
Likewise, I just put a single hook in there, in v3-0003, to allow data
to be propagated from the plan-time data structures to that new
PlannedStmt member. In your proposal, by contrast, there's a place to
put extension-relevant information in every single Plan node, and a
hook call for every single plan node as well.

I think both approaches have some advantages. One advantage of my
proposal is that it's cheaper. Your proposal makes every Plan node 8
bytes larger even though most of the time that extra pointer will be
NULL. I have been yelled at in the past for proposing to increase the
size of Plan, so I'm a little reluctant to believe that it's OK to do
that here. It might be less relevant now, as I think before we might
have been just on the cusp of needing one more cache line for every
Plan node, and it doesn't look like that's true currently, so maybe it
wouldn't provoke as much objection, but I'm still nervous about the
idea. A related disadvantage of your approach is that it needs to
consider calling the hook function lots of times instead of just one
time, though perhaps that's too insignificant to bother about. Also,
with my approach is that it's possible to propagate information from
PlannerInfo or PlannerGlobal structs, not just individual Plan nodes.

On the other hand, your proposal has usability advantages. If what
you're trying to do is save some details for every Plan node, my
approach requires you to run around and walk the plan tree and
marshall the data that you want to save, whereas your approach allows
you to do things in a more straightforward way. I think this actually
points to a deeper flaw in my approach: sure, you can run around and
look at the best path and the final plan and save whatever you want,
but how do you connect a path node to the corresponding plan node? The
Plan objects have a plan_node_id value, but the path objects don't
yet, and it's not real obvious how to match things up. Your approach
solves this problem by putting a callback in a place where it gets
passed the Path and the corresponding Plan at the same time. That's
extremely convenient.

Another thing that is different is that my patch series is clearer
about how multiple unrelated planner extensions are intended to
coexist. That's not a fundamental advantage of my approach, because
the same idea could be integrated into what you've done; it's only a
difference in how things stand as currently proposed.

My overall feeling is that we should try to come up with a unified
approach here. I'm not sure exactly what it should look like, though.
I think the strongest part of your proposal is the fact that it
connects each Path node to the corresponding Plan node in a very clear
way, and I think that the weakest part of your proposal is that it
makes each Plan node larger. I would be curious to hear what others
think.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 9/9/2025 17:32, Robert Haas wrote:
> On Wed, Aug 13, 2025 at 9:51 AM Andrei Lepikhov <lepihov@gmail.com> wrote:
> This seems quite closely related to what I propose here:
> 
> http://postgr.es/m/CA+TgmoYxfg90rw13+JcYwn4dwSC+agw7o8-A+fA3M0fh96pg8w@mail.gmail.com
I'd say it is another viewpoint (mostly opposite) of the feature.>
> There are some differences. In my proposal, specifically in v3-0004, I
> just add a single member to the PlannedStmt, and assume that the code
> can find a way to jam all the state it cares about into that single
> field, for example by creating a list of plan_node_id values and a
> list of associated nodes that can carry the corresponding data.
It may work if it is underpinned with a proper hook at the place where 
we already have the final path tree and can pointer to the plan node.> 
Likewise, I just put a single hook in there, in v3-0003, to allow data
> to be propagated from the plan-time data structures to that new
> PlannedStmt member. In your proposal, by contrast, there's a place to
> put extension-relevant information in every single Plan node, and a
> hook call for every single plan node as well.
Yes, we don't know which path tree is the final one. That's more, a plan 
tree may contain nodes that have never been in the path tree (Hash and 
Sort nodes as an example). My approach was caused by years of struggle 
to match path -> plan and plan -> 'no path' cases. The create_plan hook 
provides a straightforward way and guarantees.>
> I think both approaches have some advantages. One advantage of my
> proposal is that it's cheaper. Your proposal makes every Plan node 8
> bytes larger even though most of the time that extra pointer will be
> NULL. I have been yelled at in the past for proposing to increase the
> size of Plan, so I'm a little reluctant to believe that it's OK to do
> that here.
That's why I have not exposed this approach before. But now I see how 
specific plan nodes grow just to let explain be more detailed (remember 
the recent Memoise node expose of distinct predictions). The same stuff 
may be usual for HashJoin and IncrementalSort. IndexScan still hides a 
lot of the optimiser decisions that are sometimes needed to reveal 
performance issues. MergeJoin doesn't always show hidden 
optimisations... So, I think it would be better to extend the Plan node 
once and let in-core and external modules put their data inside that 
list on demand.> It might be less relevant now, as I think before we might
> have been just on the cusp of needing one more cache line for every
> Plan node, and it doesn't look like that's true currently, so maybe it
> wouldn't provoke as much objection, but I'm still nervous about the
> idea.
I think it needs an alternative glance.

> A related disadvantage of your approach is that it needs to
> consider calling the hook function lots of times instead of just one
> time, though perhaps that's too insignificant to bother about. Also,
The final plan does not contain so many nodes to care about. 
Additionally, plan tracking extensions will require adding data to most 
plan nodes. So, it would allow us to design more effective extensions, 
having data right in place (the Plan node) and no need to pass through a 
hash table or node tree.> with my approach is that it's possible to 
propagate information from
> PlannerInfo or PlannerGlobal structs, not just individual Plan nodes.
PlannerInfo and PlannerGlobal are the right nodes to be extended. I also 
constantly patch the RelOptInfo node because it is a highly stable node 
during the planning phase and represents a kinda of relational operation.
In my extensions/patches, PlannerGlobal data is usually needed for the 
initialisation of the EState, and PlannerInfo data is used to set up 
PlannedStmt and Subplan nodes properly.
> yet, and it's not real obvious how to match things up. Your approach
> solves this problem by putting a callback in a place where it gets
> passed the Path and the corresponding Plan at the same time. That's
> extremely convenient.
Yes, when we need to track plan decisions and observe their impact on 
query (node) performance, stability of this match becomes a critical part.>
> Another thing that is different is that my patch series is clearer
> about how multiple unrelated planner extensions are intended to
> coexist. That's not a fundamental advantage of my approach, because
> the same idea could be integrated into what you've done; it's only a
> difference in how things stand as currently proposed.
We have the same issues with hooks. But up to now, extensions have lived 
together, sharing hooks. Having a convention with DefElem or an 
Extensible node, we may forget about that issue. More importantly, we 
need to survive read/write/copy operations, at least to live inside a 
parallel worker, plan cache, generic plan, and pass READ/WRITE plan tree 
tests, enabled on-demand during compilation.>
> My overall feeling is that we should try to come up with a unified
> approach here. I'm not sure exactly what it should look like, though.
> I think the strongest part of your proposal is the fact that it
> connects each Path node to the corresponding Plan node in a very clear
> way, and I think that the weakest part of your proposal is that it
> makes each Plan node larger. I would be curious to hear what others
> think.
Agree with the node grow issue.
Positive arguments from the top of my mind:
1. It provides a clear and unified approach, allowing to extend any node 
in the same way according to the same convention.
2. Extensions may collaborate through these fields. It is suitable for 
developers who implement business logic close to the Postgres core as an 
extension (remember Yuri Rashkovskii's initiative).
3. It is clearer how to maintain read/write/copy object.

Generally, it seems we represent opposite design approaches. Business 
app developers include a lot of logic in their code and want to reduce 
overhead as much as possible. So, they value flexibility, like hooks 
provide. In their mind, DBMS is another glibc ;). You look from the core 
safety point of view and want to protect everything possible.
Not sure if it is needed here.

Anyway, I will personally reduce the core patch size in my modules 
drastically with your approach, too, but it will come at the cost of 
increasing complexity.

-- regards, Andrei Lepikhov