Re: making EXPLAIN extensible

On Wed, Mar 5, 2025 at 12:52 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> It would be good to clarify whether this is for (a) experimenting with
> explain options that might be useful in core some day; or (b) special
> developer-only options that would never be useful in core; or (c)
> production-grade explain options specific to an extension.

I think it could be any of these.

> And if (c) is part of the intended use, but not CustomScan or TableAM,
> then what kind of extensions would need extension-specific explain
> options?
>
> I am not trying to push the patch in any particular direction. On the
> contrary, I'd just like to know the scope of the feature so that I can
> stop accidentally pushing it in some direction by asking questions
> about out-of-scope use cases.

Heh, no problem. I see it probably being most useful for extensions
that touch the planner in some way that cuts across multiple node
types. We don't have a lot of those in contrib yet, but that's partly
because we don't have much infrastructure to support them.

Alena Rybakina gives an example upthread: "while writing the AQO
extension [0] we were just adding a hook (we called it
ExplainOnePlan_hook) similar to this one to add a description for the
node used in the plan, in particular its identifier and the
cardinality that is used during query planning." Andrei Lephikov's
comment upthread suggests similar things: "I write optimisation
helpers, and it is necessary to show which node was influenced and
how".

Another example - and the one that motivated this work - was my
proposal http://postgr.es/m/CA+TgmoZY+baV-T-5ifDn6P=L=aV-VkVBrPmi0TQkcEq-5Finww@mail.gmail.com
- this is an offshoot of that work, several steps removed.

I'm not sure if I'm being clear here, but the difference between these
examples and the ones you proposed is whether what you're adding to
the planner in your extension looks more like a new type of node or
more like a new overall feature. A Custom Scan node or a new table AM
is a new kind of thing that you can scan; those sorts of examples
probably want to solve their problems in some other way, though they
could use this infrastructure if they really wanted. On the other
hand, Alena's example of adaptive query optimization or my example of
letting extensions nudge the planner are a new type of optimizer
capability that happens to live in an extension. They don't show up in
a specific place in the plan; they affect things at some higher level.
That kind of thing is where I expect this infrastructure to be high
value.

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