Re: Report buffer access strategies in EXPLAIN

> > > What about retaining all strategies observed across rescans? The instrumentation
> > > could use a bitmask and EXPLAIN would report them as a list, for example:
> > >
> > > Buffer Access Strategies: normal, bulkread
> >
> > This makes sense,
>
> Thanks for confirming.
>
> > but now it gets me thinking...
> >
> > If EXPLAIN reports multiple strategies for a node, I wonder whether we will
> > also need to break down the existing shared buffer counters by access strategy.
> >
> > Something like:
> >
> > ```
> > Buffers: shared hit=95 read=8482 written=12
> > Strategy Buffers: normal hit=1, bulkread hit=94 read=8482 written=12
> > ```
> >
> > I think the breakdown should follow pg_stat_io's IOContext attribution. That
> > means shared hit/read, and probably written too, because those paths already
> > have an IOContext. I would leave dirtied out, since that does not seem to be
> > attributed to an IOContext today, based on my quick look at the code.
> >
> > These per-strategy counters could be tracked in pgBufferUsage and bubbled up
> > to EXPLAIN the same way the existing buffer counters are.
> >
> > What do you think?
>
> I agree that such a breakdown could be useful. It answers a different question
> though: the current patch reports which strategies were selected, while those
> counters would show how much buffer activity occurred under each strategy.
>
> For this first version, I’d prefer the patch series to focus on getting strategy
> reporting right while keeping the additional instrumentation relatively light.

Yes, that's the correct approach.

> Also, pg_stat_get_backend_io(pg_backend_pid()) already provides backend wide
> counters broken down by I/O context. It lacks per plan node attribution but it
> may already cover some diagnostic use cases.

Right, but that's a different tool. per-node attribution of buffers per strategy
is the natural next step of this feature, IMO.

> So I’d prefer to consider that as a follow up. Does that make sense?

Yes, I am ok with this.

--
Sami