digoal
2021-09-09
PostgreSQL , tuples_returned , tuples_fetched
pg_stat_database, pg_stat_xxx_tables|indexes|database, pg_stat_xact_xxx_tables 统计信息视图里面包含了两种指标: returned, fetched.
使用\d view可以了解到这些指标使用如下函数获取:
postgres=# \df *.*returned
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------------------------+------------------+---------------------+------
pg_catalog | pg_stat_get_db_tuples_returned | bigint | oid | func
pg_catalog | pg_stat_get_tuples_returned | bigint | oid | func
pg_catalog | pg_stat_get_xact_tuples_returned | bigint | oid | func
postgres=# \df *.*fetched
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+---------------------------------+------------------+---------------------+------
pg_catalog | pg_stat_get_db_tuples_fetched | bigint | oid | func
pg_catalog | pg_stat_get_tuples_fetched | bigint | oid | func
pg_catalog | pg_stat_get_xact_blocks_fetched | bigint | oid | func
pg_catalog | pg_stat_get_xact_tuples_fetched | bigint | oid | func
returned, fetched代表什么含义?
表:
tuples_returnedis the number of tuples successfully fetched byheap_getnext,tuples_fetchedis the number of tuples successfully fetched byheap_fetchunder the control ofbitmap indexscans.
索引:
tuples_returnedis the number of index entries returned by theindex AM,tuples_fetchedis the number of tuples successfully fetched byheap_fetchunder the control ofsimple indexscansfor this index.
数据库:
tuples_returned, 直接扫描表的tuple行数 + 直接扫描索引的索引条目数.tuples_fetched, 通过索引指向的tid去回表访问的tuple行数.
* IDENTIFICATION
* src/backend/access/heap/heapam.c
*
*
* INTERFACE ROUTINES
* heap_beginscan - begin relation scan
* heap_rescan - restart a relation scan
* heap_endscan - end relation scan
* heap_getnext - retrieve next tuple in scan
* heap_fetch - retrieve tuple with given tid
* heap_insert - insert tuple into a relation
* heap_multi_insert - insert multiple tuples into a relation
* heap_delete - delete a tuple from a relation
* heap_update - replace a tuple in a relation with another tuple
/* ----------
* PgStat_TableCounts The actual per-table counts kept by a backend
*
* This struct should contain only actual event counters, because we memcmp
* it against zeroes to detect whether there are any counts to transmit.
* It is a component of PgStat_TableStatus (within-backend state) and
* PgStat_TableEntry (the transmitted message format).
*
* 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.
*
* tuples_inserted/updated/deleted/hot_updated count attempted actions,
* regardless of whether the transaction committed. delta_live_tuples,
* delta_dead_tuples, changed_tuples are set depending on commit or abort.
* Note that delta_live_tuples and delta_dead_tuples can be negative!
* ----------
*/
typedef struct PgStat_TableCounts
{
PgStat_Counter t_numscans;
PgStat_Counter t_tuples_returned;
PgStat_Counter t_tuples_fetched;
PgStat_Counter t_tuples_inserted;
PgStat_Counter t_tuples_updated;
PgStat_Counter t_tuples_deleted;
PgStat_Counter t_tuples_hot_updated;
bool t_truncated;
PgStat_Counter t_delta_live_tuples;
PgStat_Counter t_delta_dead_tuples;
PgStat_Counter t_changed_tuples;
PgStat_Counter t_blocks_fetched;
PgStat_Counter t_blocks_hit;
} PgStat_TableCounts;
《PostgreSQL pg_stat_ pg_statio_ 统计信息(scan,read,fetch,hit)源码解读》
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
