Re: Split index and table statistics into different types of stats

Hi,

On Thu, Aug 06, 2026 at 11:06:29AM +0900, Michael Paquier wrote:
> On Wed, Oct 02, 2024 at 01:48:11PM +0900, Michael Paquier wrote:
> > It could always be re-created later on, as required.
>
> And here is a blast from the past, following the recent effort I have
> been putting for the relfilenode statistics:
> https://www.postgresql.org/message-id/alCLr8ya-4ojew-x@paquier.xyz

Thanks!

> One of the conclusions I got out of this initial work is that the
> split of the index/table part makes more sense if tackled first, so
> here we are.
>
> As discussed, I am reposting refined versions of the patch set that
> splits the index and table stats, by introducing a new stats kind for
> indexes. There are two patches, split the facilities with different
> consequences:
> - 0001 refactors PgStat_StatTabEntry so as the data for indexes and
> tables in shmem is split. The advantage of this one is a reduction of
> the shared memory usage, as most of the fields of PgStat_StatTabEntry
> are not used for indexes. These are hidden behind a new stats kind.
> This is mostly bulk refactoring, with new SQL functions to retrieve
> the stats info. Note the PgStat_TableStatus bits in pgstat_index.c;
> these are tackled in 0002. This part is enough to reduce the shmem
> footprint of index stats.

I had a look at 0001 only, and I've a few comments.

=== 1

+pgstat_index_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
+{
.
.
.
+ /* The entry was successfully flushed, add the same to database stats */
+ dbentry = pgstat_prep_database_pending(dboid);
+ dbentry->blocks_fetched += lstats->counts.blocks_fetched;
+ dbentry->blocks_hit += lstats->counts.blocks_hit;

Shouldn't we add tuples_returned and tuples_fetched here too?

=== 2

pg_stat_reset_single_table_counters() public doc has been updated:

@@ -6022,7 +6040,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<returnvalue>void</returnvalue>
</para>
<para>
- Resets statistics for a single table or index in the current database
+ Resets statistics for a single table in the current database

and also the pg_stat_get_numscans() and pg_stat_reset_single_table_counters()
descriptions in pg_proc.dat:

-{ oid => '1928', descr => 'statistics: number of scans done for table/index',
+{ oid => '1928', descr => 'statistics: number of scans done for table',
proname => 'pg_stat_get_numscans', provolatile => 's', proparallel => 'r',

- descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
+ descr => 'statistics: reset collected statistics for a single table in the current database or shared across all databases in the cluster',

But it looks like that pg_stat_get_xact_numscans() descr has not been updated
in pg_proc.dat although it now returns 0 for indexes.

There is no public doc for pg_stat_get_numscans() and pg_stat_get_xact_numscans(),
so existing callers will get different behavior (that is not publicly documented).

Should we preserve the old behavior while keeping the new functions as explicit
alternatives? Or is it less of an issue given that those 2 don't have public
doc?

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com