Re: Showing "Table Block Fetches" in EXPLAIN ANALYZE for index-only scans

From: Rui Zhao <zhaorui126(at)gmail(dot)com>
To: Peter Geoghegan <pg(at)bowt(dot)ie>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tomas Vondra <tomas(at)vondra(dot)me>
Subject: Re: Showing "Table Block Fetches" in EXPLAIN ANALYZE for index-only scans
Date: 2026-11-22 15:58:13
Message-ID: CAHWVJhEUysFc12Af_HPrGDhV41rDS3rrmMGD6TPzRddrh+-9xw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Peter,

Thanks for working on this. I agree with Sami and Greg that it would
be useful to show both counts. "Heap Fetches" tells me how often the
scan had to visit the heap to check visibility. Even if every check is
on the same page, I still want to know whether it happened once or a
hundred times. "Table Block Fetches" tells me how many times the scan
had to acquire a heap page for those checks.

I tested a parallel index-only scan with two workers. All 140 rows
were on one heap page. With leader participation off, each worker
fetched that page once, so EXPLAIN reported "Table Block Fetches: 2".
When the leader also scanned rows, the count rose to 3. In both runs
I could see the total, but not how many fetches came from each worker.

EXPLAIN (ANALYZE, VERBOSE) already shows each worker's row count and
buffer usage. Since v1 records block fetches per worker too, could we
show them there, alongside those figures? Without VERBOSE, the node
total would be enough.

For the output code, show_tidbitmap_info() in explain.c prints the node
statistics first and the worker details in a separate loop. We could
follow that layout at the end of show_indexscan_info(), after:

> ExplainPropertyUInteger("Index Searches", NULL, nsearches, es);

if (nodeTag(plan) == T_IndexOnlyScan && SharedInfo &&
es->verbose && es->workers_state)
{
for (int i = 0; i < SharedInfo->num_workers; ++i)
{
IndexScanInstrumentation *wi = &SharedInfo->winstrument[i];

if (planstate->worker_instrument->instrument[i].nloops <= 0)
continue;

ExplainOpenWorker(i, es);
ExplainPropertyUInteger("Table Block Fetches", NULL,
wi->ntableblockfetches, es);
ExplainCloseWorker(i, es);
}
}

If we keep just the total, perhaps perform.sgml could mention that
fetching the same heap page in two workers counts as two fetches.

Keeping the tuple count would also preserve an existing check in
horizons.spec. The test inserts two rows and expects "Heap Fetches: 2"
on the first scan. Both rows are on the same heap page, so v1 reports
"Table Block Fetches: 1". I got the same block count when I inserted
just one row. The new check can therefore pass even if the scan visits
only one of the two index entries.

When rebasing v1 onto master, I hit a conflict in show_indexscan_info().
I kept the ioss_Instrument NULL check from a20d50a00e and changed the
field read inside it from ntabletuplefetches to ntableblockfetches.

Regards,
Rui

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Matias Kenig 2026-11-22 16:11:50 Proposal: ABSENT ON TRAILING NULL for JSON_ARRAY
Previous Message Stepan Neretin 2026-11-22 15:53:25 Re: FATAL: could not send end-of-streaming message to primary: no COPY in progress