Re: making EXPLAIN extensible - Mailing list pgsql-hackers

On Tue, Mar 4, 2025 at 1:53 PM Jeff Davis <pgsql@j-davis.com> wrote:
> I don't expect there to be zillions of extensions that only add new and
> exciting explain options. Instead, it seems more likely that all
> TableAM and CustomScan extensions will have 1-3 new explain options,
> and that some of those might collide. For example, several may have a
> EXPLAIN(PUSHDOWN) option that explains what work is being pushed down
> into the TableAM/CustomScan.
>
> In that case it's not even clear to me that a collision is a problem.
> Would you really only want pushdown information from extension A, and
> be upset that it also emits pushdown information from extension B?
> Maybe we should just allow multiple extensions to use the same option
> name?

One fairly big problem with that idea is that options need not be
simple Booleans.  If extension A adds PUSHDOWN { 'summary' | 'detail'
} and extension B adds PUSHDOWN { 'scan' | 'join' | 'agg' }, it's
definitely not going to work, even if we arrange to call handlers for
both extensions. If we want to have EXPLAIN keywords that have
well-defined meanings that span across different
FDWs/tableAMs/whatever, I think we should add those to core and
document what we expect extensions to do. This mechanism is really for
cases where you need a completely new option that only you will care
about.

Actually, I don't think custom scans or table AMs are the design
center for this feature. Keep in mind that, for a custom scan, we
already have ExplainCustomScan as part of CustomScanState->methods. We
don't currently have anything similar for table AMs, and you could
work around that with these hooks, by checking every node to see
whether it's a scan node and if so whether it scans a relation that
matches your table AM, but it would probably be better to have a
method for it, similar to what we have for CustomScan, if this is
something people want to do. It would be more efficient and require
less code.

Granted, those interfaces don't let you add completely new options,
but I think actually for custom scans and table AMs you most likely
want to just display whatever details you have unconditionally, or
conditional on es->verbose. I'm not sure there's a real reason to add
a new option that gates, say, postgres_fdw's display of join pushdown
information. That seems awfully specific. You'd need a reason why you
would want to control that display separately from everything else,
and the use case for that seems thin.

Where I see this being more useful is for people who want to display
additional information for plan nodes that they did not implement. For
example, my EXPLAIN (RANGE_TABLE) option dumps every
range-table-related fact it can find in the Plan tree. That includes
both information about which plan nodes (as displayed by EXPLAIN) are
scanning which RTIs, and also a list of all the RTIs and a bunch of
properties of each one. When you're trying to show information by
topic, as in this case, hooks on individual node types like custom
scan or (hypothetically) table AM callbacks don't help you get the job
done -- and neither do the existing options.

That having been said, the vigorous response to this proposal so far
suggests to me that people probably will want to use this for things
that are a bit different than what I had in mind, and that's fine.
Maybe there's even some way to adjust this proposal so that it can
suit some of those other use cases better. But, I'm doubtful that
letting unrelated extensions try to share the same option name is that
thing.

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