Re: Custom explain options

On 21/10/2023 19:16, Konstantin Knizhnik wrote:
> EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS,
> COST,...) which help to provide useful details of query execution.
> In Neon we have added PREFETCH option which shows information about page
> prefetching during query execution (prefetching is more critical for Neon
> architecture because of separation of compute and storage, so it is
> implemented not only for bitmap heap scan as in Vanilla Postgres, but
> also for seqscan, indexscan and indexonly scan). Another possible
> candidate  for explain options is local file cache (extra caching layer
> above shared buffers which is used to somehow replace file system cache
> in standalone Postgres).
>
> I think that it will be nice to have a generic mechanism which allows
> extensions to add its own options to EXPLAIN.

Generally, I welcome this idea: Extensions can already do a lot of work,
and they should have a tool to report their state, not only into the log.
But I think your approach needs to be elaborated. At first, it would be
better to allow registering extended instruments for specific node types
to avoid unneeded calls.
Secondly, looking into the Instrumentation usage, I don't see the reason
to limit the size: as I see everywhere it exists locally or in the DSA
where its size is calculated on the fly. So, by registering an extended
instrument, we can reserve a slot for the extension. The actual size of
underlying data can be provided by the extension routine.

--
regards,
Andrei Lepikhov
Postgres Professional