On 2021/05/18 13:20, Masahiro Ikeda wrote:
On 2021/05/17 20:46, Fujii Masao wrote:
On 2021/05/17 18:58, Masahiro Ikeda wrote:
On 2021/05/17 15:32, Fujii Masao wrote:
On 2021/05/14 17:00, Masahiro Ikeda wrote:
Hi,
I worried the difference between "tup_returned" and "tup_fetched" in
pg_stat_database. I assumed that "tup_returned" means the number of tuples
that returned to clients. Of course, this is wrong.
- Number of rows returned by queries in this database
+ Number of live rows returned by sequential scans of queries in this
database
- Number of rows fetched by queries in this database
+ Number of live rows fetched by index scan of queries in this database
I found the following comments in pgstat.h. So maybe even these
new descriptions are incorrect?
* Note: for a table, tuples_returned is the number of tuples successfully
* fetched by heap_getnext, while tuples_fetched is the number of tuples
* successfully fetched by heap_fetch under the control of bitmap indexscans.
* For an index, tuples_returned is the number of index entries returned by
* the index AM, while tuples_fetched is the number of tuples successfully
* fetched by heap_fetch under the control of simple indexscans for this
index.
Oh, Thanks!
I updated the sentences using the descriptions of
"pg_stat_all_tables.seq_tup_read", "pg_stat_all_tables.idx_tup_fetch", and
"pg_stat_all_index.idx_tup_read".
- Number of rows returned by queries in this database
+ Number of rows returned by queries in this database. The rows
correspond to the live rows fetched by sequential scans and index entries
returned by scans on indexes
This is still not correct because this counter is incremented even when
other scan like TidScan happens?
Sorry, I couldn't find the way to increment tup_returned by TidScan.
Do you mean that Tid Range Scan increments the counter?
Yes, what I tried to mean is Tid Range Scan.
Tid Range Scan increments the tup_returned, and
pg_stat_all_tables.seq_tup_read is also incremented. I thought it's ok because
Tid Range Scan is like sequential scan.
Yes, you're right. One interesting thing I found is;
when Tid Range Scan happens, seq_tup_read is incremented
but seq_scan is not. I'm not sure if this is expected behavior or not.
That's the reason why the document of
pg_stat_all_tables.seq_tup_read says "Number of live rows fetched by
sequential scans"
Regarding the original issue, as far as I understand correctly,
* pg_stat_database.tup_returned = sum(pg_stat_all_tables.seq_tup_read) +
sum(pg_stat_all_indexes.idx_tup_read)
* pg_stat_database.tup_fetched = sum(pg_stat_all_tables.idx_tup_fetch)
But the counters for some system catalogs like pg_database shared
across all databases of a cluster are excluded from that calculation.
Is this my understanding right? If right, probably we can reuse
the existing descriptions for those counters to document
pg_stat_database counters. For example,
pg_stat_database.tup_returned:
Number of live rows fetched by sequential and index scans in this database
pg_stat_database.tup_fetched:
Number of index entries returned by scans on indexes in this database
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
