Re: Showing primitive index scan count in EXPLAIN ANALYZE (for skip scan and SAOP scans)

On Wed, 28 Aug 2024 at 15:53, Peter Geoghegan <pg@bowt.ie> wrote:
> On Wed, Aug 28, 2024 at 9:49 AM Robert Haas <robertmhaas@gmail.com> wrote:
> > On Wed, Aug 28, 2024 at 9:41 AM Peter Geoghegan <pg@bowt.ie> wrote:
> > > On Wed, Aug 28, 2024 at 9:35 AM Robert Haas <robertmhaas@gmail.com> wrote:
> > > > > If you think it's important to have this info on all indexes then I'd
> > > > > prefer the pgstat approach over adding a field in IndexScanDescData.
> > > > > If instead you think that this is primarily important to expose for
> > > > > nbtree index scans, then I'd prefer putting it in the BTSO using e.g.
> > > > > the index AM analyze hook approach, as I think that's much more
> > > > > elegant than this.
> > > >
> > > > I agree with this analysis. I don't see why IndexScanDesc would ever
> > > > be the right place for this.
> > >
> > > Then what do you think is the right place?
> >
> > The paragraph that I agreed with and quoted in my reply, and that you
> > then quoted in your reply to me, appears to me to address that exact
> > question.
>
> Are you talking about adding global counters, in the style of pgBufferUsage?

My pgstat approach would be that ExecIndexScan (plus ExecIOS and
ExecBitmapIS) could record the current state of relevant fields from
node->ss.ss_currentRelation->pgstat_info, and diff them with the
recorded values at the end of that node's execution, pushing the
result into e.g. Instrumentation; diffing which is similar to what
happens in InstrStartNode() and InstrStopNode() but for the relation's
pgstat_info instead of pgBufferUsage and pgWalUsage. Alternatively
this could happen in ExecProcNodeInstr, but it'd need some more
special-casing to make sure it only addresses (index) relation scan
nodes.

By pulling the stats directly from Relation->pgstat_info, no catalog
index scans are counted if they aren't also the index which is subject
to that [Bitmap]Index[Only]Scan.

In effect, this would need no changes in AM code, as this would "just"
pull the data from those statistics which are already being updated in
AM code.


Kind regards,

Matthias van de Meent
Neon (https://neon.tech)