PostgreSQL 统计信息混淆之处(scan,read,fetch,hit)源码解读

最新推荐文章于 2024-08-18 22:56:44 发布
最新推荐文章于 2024-08-18 22:56:44 发布
阅读量1k 收藏 2
点赞数
文章标签： 数据库 数据结构与算法
原文链接：https://yq.aliyun.com/articles/8475
版权

PostgreSQL 几个统计信息的解释难以理解，所以本文花一些时间从源码的角度来解释一下。
让大家对这几个容易误解的统计值有更好的理解。

比较难理解的几个统计值为：
pg_stat_all_indexes 的

idx_scan       
idx_tup_read    
idx_tup_fetch

pg_statio_all_indexes 的

idx_blks_read  
idx_blks_hit

pg_stat_all_tables 的

seq_scan  
seq_tup_read  
idx_scan  
idx_tup_fetch

pg_statio_all_tables 的

heap_blks_read   
heap_blks_hit    
idx_blks_read    
idx_blks_hit     
toast_blks_read     heap表对应的toast表统计字段   
toast_blks_hit        
tidx_blks_read      heap表的索引对应的toast索引统计字段   
tidx_blks_hit

首先需要获得以上统计字段的信息来源：

postgres=# \d+ pg_stat_all_indexes   
           View "pg_catalog.pg_stat_all_indexes"  
    Column     |  Type  | Modifiers | Storage | Description   
---------------+--------+-----------+---------+-------------  
 relid         | oid    |           | plain   |   
 indexrelid    | oid    |           | plain   |   
 schemaname    | name   |           | plain   |   
 relname       | name   |           | plain   |   
 indexrelname  | name   |           | plain   |   
 idx_scan      | bigint |           | plain   | --     pg_stat_get_numscans(i.oid)  
 idx_tup_read  | bigint |           | plain   | --     pg_stat_get_tuples_returned(i.oid)  
 idx_tup_fetch | bigint |           | plain   | --     pg_stat_get_tuples_fetched(i.oid)  
View definition:  
 SELECT c.oid AS relid,  
    i.oid AS indexrelid,  
    n.nspname AS schemaname,  
    c.relname,  
    i.relname AS indexrelname,  
    pg_stat_get_numscans(i.oid) AS idx_scan,  
    pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,  
    pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch  
   FROM pg_class c  
     JOIN pg_index x ON c.oid = x.indrelid  
     JOIN pg_class i ON i.oid = x.indexrelid  
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]);  
postgres=# \d+ pg_statio_all_indexes   
          View "pg_catalog.pg_statio_all_indexes"  
    Column     |  Type  | Modifiers | Storage | Description   
---------------+--------+-----------+---------+-------------  
 relid         | oid    |           | plain   |   
 indexrelid    | oid    |           | plain   |   
 schemaname    | name   |           | plain   |   
 relname       | name   |           | plain   |   
 indexrelname  | name   |           | plain   |   
 idx_blks_read | bigint |           | plain   | --  pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)  
 idx_blks_hit  | bigint |           | plain   | --  pg_stat_get_blocks_hit(i.oid)  
View definition:  
 SELECT c.oid AS relid,  
    i.oid AS indexrelid,  
    n.nspname AS schemaname,  
    c.relname,  
    i.relname AS indexrelname,  
    pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid) AS idx_blks_read,  
    pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit  
   FROM pg_class c  
     JOIN pg_index x ON c.oid = x.indrelid  
     JOIN pg_class i ON i.oid = x.indexrelid  
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]);  
postgres=# \d+ pg_stat_all_tables   
                        View "pg_catalog.pg_stat_all_tables"  
       Column        |           Type           | Modifiers | Storage | Description   
---------------------+--------------------------+-----------+---------+-------------  
 relid               | oid                      |           | plain   |   
 schemaname          | name                     |           | plain   |   
 relname             | name                     |           | plain   |   
 seq_scan            | bigint                   |           | plain   | --  pg_stat_get_numscans(c.oid)  
 seq_tup_read        | bigint                   |           | plain   | --  pg_stat_get_tuples_returned(c.oid)  
 idx_scan            | bigint                   |           | plain   | --  sum(pg_stat_get_numscans(i.indexrelid))  
 idx_tup_fetch       | bigint                   |           | plain   | --  sum(pg_stat_get_tuples_fetched(i.indexrelid)) + pg_stat_get_tuples_fetched(c.oid)  
 n_tup_ins           | bigint                   |           | plain   |   
 n_tup_upd           | bigint                   |           | plain   |   
 n_tup_del           | bigint                   |           | plain   |   
 n_tup_hot_upd       | bigint                   |           | plain   |   
 n_live_tup          | bigint                   |           | plain   |   
 n_dead_tup          | bigint                   |           | plain   |   
 n_mod_since_analyze | bigint                   |           | plain   |   
 last_vacuum         | timestamp with time zone |           | plain   |   
 last_autovacuum     | timestamp with time zone |           | plain   |   
 last_analyze        | timestamp with time zone |           | plain   |   
 last_autoanalyze    | timestamp with time zone |           | plain   |   
 vacuum_count        | bigint                   |           | plain   |   
 autovacuum_count    | bigint                   |           | plain   |   
 analyze_count       | bigint                   |           | plain   |   
 autoanalyze_count   | bigint                   |           | plain   |   
View definition:  
 SELECT c.oid AS relid,  
    n.nspname AS schemaname,  
    c.relname,  
    pg_stat_get_numscans(c.oid) AS seq_scan,  
    pg_stat_get_tuples_returned(c.oid) AS seq_tup_read,  
    sum(pg_stat_get_numscans(i.indexrelid))::bigint AS idx_scan,  
    sum(pg_stat_get_tuples_fetched(i.indexrelid))::bigint + pg_stat_get_tuples_fetched(c.oid) AS idx_tup_fetch,  
    pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,  
    pg_stat_get_tuples_updated(c.oid) AS n_tup_upd,  
    pg_stat_get_tuples_deleted(c.oid) AS n_tup_del,  
    pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd,  
    pg_stat_get_live_tuples(c.oid) AS n_live_tup,  
    pg_stat_get_dead_tuples(c.oid) AS n_dead_tup,  
    pg_stat_get_mod_since_analyze(c.oid) AS n_mod_since_analyze,  
    pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum,  
    pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum,  
    pg_stat_get_last_analyze_time(c.oid) AS last_analyze,  
    pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze,  
    pg_stat_get_vacuum_count(c.oid) AS vacuum_count,  
    pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count,  
    pg_stat_get_analyze_count(c.oid) AS analyze_count,  
    pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count  
   FROM pg_class c  
     LEFT JOIN pg_index i ON c.oid = i.indrelid  
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])  
  GROUP BY c.oid, n.nspname, c.relname;  
postgres=# \d+ pg_statio_all_tables   
            View "pg_catalog.pg_statio_all_tables"  
     Column      |  Type  | Modifiers | Storage | Description   
-----------------+--------+-----------+---------+-------------  
 relid           | oid    |           | plain   |   
 schemaname      | name   |           | plain   |   
 relname         | name   |           | plain   |   
 heap_blks_read  | bigint |           | plain   | --  pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)  
 heap_blks_hit   | bigint |           | plain   | --  pg_stat_get_blocks_hit(c.oid)  
 idx_blks_read   | bigint |           | plain   | --  sum(pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))  
 idx_blks_hit    | bigint |           | plain   | --  sum(pg_stat_get_blocks_hit(i.indexrelid))  
 toast_blks_read | bigint |           | plain   | --  pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)  
 toast_blks_hit  | bigint |           | plain   | --  pg_stat_get_blocks_hit(t.oid)  
 tidx_blks_read  | bigint |           | plain   | --  sum(pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))  
 tidx_blks_hit   | bigint |           | plain   | --  sum(pg_stat_get_blocks_hit(x.indexrelid))  
View definition:  
 SELECT c.oid AS relid,  
    n.nspname AS schemaname,  
    c.relname,  
    pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid) AS heap_blks_read,  
    pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit,  
    sum(pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))::bigint AS idx_blks_read,  
    sum(pg_stat_get_blocks_hit(i.indexrelid))::bigint AS idx_blks_hit,  
    pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid) AS toast_blks_read,  
    pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit,  
    sum(pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))::bigint AS tidx_blks_read,  
    sum(pg_stat_get_blocks_hit(x.indexrelid))::bigint AS tidx_blks_hit  
   FROM pg_class c  
     LEFT JOIN pg_index i ON c.oid = i.indrelid  
     LEFT JOIN pg_class t ON c.reltoastrelid = t.oid  
     LEFT JOIN pg_index x ON t.oid = x.indrelid  
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
  WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"])  
  GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;

以上几个统计字段的统计信息来自如下函数：

 idx_scan        | bigint |           | plain   | --  pg_stat_get_numscans(i.oid)  
 idx_tup_read    | bigint |           | plain   | --  pg_stat_get_tuples_returned(i.oid)  
 idx_tup_fetch   | bigint |           | plain   | --  pg_stat_get_tuples_fetched(i.oid)  
 idx_blks_read   | bigint |           | plain   | --  pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)  
 idx_blks_hit    | bigint |           | plain   | --  pg_stat_get_blocks_hit(i.oid)  
  
 seq_scan        | bigint |           | plain   | --  pg_stat_get_numscans(c.oid)  
 seq_tup_read    | bigint |           | plain   | --  pg_stat_get_tuples_returned(c.oid)  
 idx_scan        | bigint |           | plain   | --  sum(pg_stat_get_numscans(i.indexrelid))  
 idx_tup_fetch   | bigint |           | plain   | --  sum(pg_stat_get_tuples_fetched(i.indexrelid)) + pg_stat_get_tuples_fetched(c.oid)  
 heap_blks_read  | bigint |           | plain   | --  pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)  
 heap_blks_hit   | bigint |           | plain   | --  pg_stat_get_blocks_hit(c.oid)  
 idx_blks_read   | bigint |           | plain   | --  sum(pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))  
 idx_blks_hit    | bigint |           | plain   | --  sum(pg_stat_get_blocks_hit(i.indexrelid))  
 toast_blks_read | bigint |           | plain   | --  pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)  
 toast_blks_hit  | bigint |           | plain   | --  pg_stat_get_blocks_hit(t.oid)  
 tidx_blks_read  | bigint |           | plain   | --  sum(pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))  
 tidx_blks_hit   | bigint |           | plain   | --  sum(pg_stat_get_blocks_hit(x.indexrelid))

这些SQL函数对应的C函数如下：

postgres=# \df+ pg_stat_get_numscans  
|     Source code      |                   Description                      
+----------------------+--------------------------------------------------  
| pg_stat_get_numscans | statistics: number of scans done for table/index  
  
postgres=# \df+ pg_stat_get_tuples_returned  
|         Source code         |                 Description                    
+-----------------------------+----------------------------------------------  
| pg_stat_get_tuples_returned | statistics: number of tuples read by seqscan  
  
postgres=# \df+ pg_stat_get_tuples_fetched  
|        Source code         |                   Description                     
+----------------------------+-------------------------------------------------  
| pg_stat_get_tuples_fetched | statistics: number of tuples fetched by idxscan  
  
postgres=# \df+ pg_stat_get_blocks_fetched  
|        Source code         |             Description                
+----------------------------+--------------------------------------  
| pg_stat_get_blocks_fetched | statistics: number of blocks fetched  
  
postgres=# \df+ pg_stat_get_blocks_hit  
|      Source code       |                 Description                   
+------------------------+---------------------------------------------  
| pg_stat_get_blocks_hit | statistics: number of blocks found in cache

以上5个C函数的源码分析

pg_stat_get_numscans  
  result = (int64) (tabentry->numscans);  
  
pg_stat_get_tuples_returned  
  result = (int64) (tabentry->tuples_returned);  
  
pg_stat_get_tuples_fetched  
  result = (int64) (tabentry->tuples_fetched);  
  
pg_stat_get_blocks_fetched  
  result = (int64) (tabentry->blocks_fetched);  
  
pg_stat_get_blocks_hit  
  result = (int64) (tabentry->blocks_hit);

他们都是返回以下数据结构中的某项计数器的值：

/* ----------
 * PgStat_StatTabEntry            The collector's data per table (or index)
 * ----------
 */
typedef struct PgStat_StatTabEntry
{
    Oid            tableid;

    PgStat_Counter numscans;

    PgStat_Counter tuples_returned;
    PgStat_Counter tuples_fetched;

    PgStat_Counter tuples_inserted;
    PgStat_Counter tuples_updated;
    PgStat_Counter tuples_deleted;
    PgStat_Counter tuples_hot_updated;

    PgStat_Counter n_live_tuples;
    PgStat_Counter n_dead_tuples;
    PgStat_Counter changes_since_analyze;

    PgStat_Counter blocks_fetched;
    PgStat_Counter blocks_hit;

    TimestampTz vacuum_timestamp;        /* user initiated vacuum */
    PgStat_Counter vacuum_count;
    TimestampTz autovac_vacuum_timestamp;        /* autovacuum initiated */
    PgStat_Counter autovac_vacuum_count;
    TimestampTz analyze_timestamp;        /* user initiated */
    PgStat_Counter analyze_count;
    TimestampTz autovac_analyze_timestamp;        /* autovacuum initiated */
    PgStat_Counter autovac_analyze_count;
} PgStat_StatTabEntry;

这些值是由pgstats进程来进行统计的

PgstatCollectorMain(int argc, char *argv[])  
{  
...  
    for (;;)  
    {  
        /* Clear any already-pending wakeups */  
        ResetLatch(&pgStatLatch);  
  
...  
            /*  
             * O.K. - we accept this message.  Process it.  
             */  
            switch (msg.msg_hdr.m_type)  
            {  
...  
                case PGSTAT_MTYPE_TABSTAT:  
                    pgstat_recv_tabstat((PgStat_MsgTabstat *) &msg, len);  
                    break;  
...  


/* ----------  
 * pgstat_recv_tabstat() -  
 *  
 *    Count what the backend has done.  
 * ----------  
 */  
static void  
pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)  
{  
...  
            tabentry->numscans += tabmsg->t_counts.t_numscans;  
            tabentry->tuples_returned += tabmsg->t_counts.t_tuples_returned;  
            tabentry->tuples_fetched += tabmsg->t_counts.t_tuples_fetched;  
            tabentry->tuples_inserted += tabmsg->t_counts.t_tuples_inserted;  
            tabentry->tuples_updated += tabmsg->t_counts.t_tuples_updated;  
            tabentry->tuples_deleted += tabmsg->t_counts.t_tuples_deleted;  
            tabentry->tuples_hot_updated += tabmsg->t_counts.t_tuples_hot_updated;  
            tabentry->n_live_tuples += tabmsg->t_counts.t_delta_live_tuples;  
            tabentry->n_dead_tuples += tabmsg->t_counts.t_delta_dead_tuples;  
            tabentry->changes_since_analyze += tabmsg->t_counts.t_changed_tuples;  
            tabentry->blocks_fetched += tabmsg->t_counts.t_blocks_fetched;  
            tabentry->blocks_hit += tabmsg->t_counts.t_blocks_hit;  
...

tabmsg->t_counts.? 则是通过如下macro进行改写的：

/* nontransactional event counts are simple enough to inline */  
  
#define pgstat_count_heap_scan(rel)                                                                     \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_numscans++;                              \  
        } while (0)  
#define pgstat_count_heap_getnext(rel)                                                          \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_tuples_returned++;               \  
        } while (0)  
#define pgstat_count_heap_fetch(rel)                                                            \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_tuples_fetched++;                \  
        } while (0)  
#define pgstat_count_index_scan(rel)                                                            \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_numscans++;                              \  
        } while (0)  
#define pgstat_count_index_tuples(rel, n)                                                       \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_tuples_returned += (n);  \  
        } while (0)  
#define pgstat_count_buffer_read(rel)                                                           \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_blocks_fetched++;                \  
        } while (0)  
#define pgstat_count_buffer_hit(rel)                                                            \  
        do {                                                                                                                    \  
                if ((rel)->pgstat_info != NULL)                                                         \  
                        (rel)->pgstat_info->t_counts.t_blocks_hit++;                    \  
        } while (0)

取出每个macro对应的调用以及解释如下：
pgstat_count_index_scan(idx_oid)
统计指定索引的扫描次数，和扫描多少条记录无关，和node有个，一般看执行计划中，同一个索引如果在NODE中出现一次，那么就算扫描一次。
1
10

pgstat_count_index_tuples(rel, n)
统计从指定索引获取tid的条数
2

pgstat_count_heap_fetch
统计从使用指定索引的tid获取HEAP tuple的条数
3

pgstat_count_buffer_read
统计所有的buffer read次数, 包括在postgresql shared buffers中命中的，或者未在postgresql shared buffers中命中的。
4

pgstat_count_buffer_hit(rel)
统计在shared buffers中命中的buffer read次数。
5
9

pgstat_count_heap_scan(rel)
统计指定表的，全表扫描的次数，和返回的记录数无关，只和执行计划中的node相关，如果在plan中某个表只有一个seq scan的次数，则这条SQL执行一次时算一次。
6

pgstat_count_heap_getnext(rel)
指从全表扫描节点中扫描的记录数
7
8

梳理一下最初的统计字段和这些macro的对应关系：

pg_stat_all_indexes 的   
idx_scan         对应  pgstat_count_index_scan(idx_oid)  产生的计数的累加  
idx_tup_read     对应  pgstat_count_index_tuples(idx_oid, n)  产生的计数的累加  
idx_tup_fetch    对应  pgstat_count_heap_fetch(idx_oid)  产生的计数的累加  
  
pg_statio_all_indexes 的   
idx_blks_read    对应  pgstat_count_buffer_read(idx_oid)  产生的计数的累加  
idx_blks_hit     对应  pgstat_count_buffer_hit(idx_oid)  产生的计数的累加  
  
pg_stat_all_tables 的   
seq_scan         对应  pgstat_count_heap_scan(tbl_oid)  产生的计数的累加  
seq_tup_read     对应  pgstat_count_heap_getnext(tbl_oid)  产生的计数的累加  
idx_scan         对应  sum( 索引 pgstat_count_index_scan(tbl_oid) )  产生的计数的累加  
idx_tup_fetch    对应  sum( 索引 pgstat_count_index_tuples(idx_oid, n) ) + pgstat_count_heap_fetch(tbl_oid)  产生的计数的累加  
  
pg_statio_all_tables 的   
heap_blks_read    对应  pgstat_count_buffer_read(tbl_oid) - pgstat_count_buffer_hit(tbl_oid)  产生的计数的累加  
heap_blks_hit     对应  pgstat_count_buffer_hit(tbl_oid)  产生的计数的累加  
idx_blks_read     对应  sum( 索引 pgstat_count_buffer_read(idx_oid) - pgstat_count_buffer_hit(idx_oid) )  产生的计数的累加  
idx_blks_hit      对应  sum( pgstat_count_buffer_hit(idx_oid) )  产生的计数的累加  
toast_blks_read   对应  pgstat_count_buffer_read(toast_oid) - pgstat_count_buffer_hit(toast_oid)  产生的计数的累加  
toast_blks_hit    对应  pgstat_count_buffer_hit(toast_oid)   产生的计数的累加  
tidx_blks_read    对应  sum( 索引 pgstat_count_buffer_read(toast_idx_oid) - pgstat_count_buffer_hit(toast_idx_oid) )  产生的计数的累加  
tidx_blks_hit     对应  sum( pgstat_count_buffer_hit(toast_idx_oid) )  产生的计数的累加

小结：

pg_stat_all_indexes 的

idx_scan            索引被扫描了多少次，和扫描多少条记录无关，和node有关，一般看执行计划，同一个索引如果在NODE中出现一次，那么这条SQL执行时这个索引就算扫描一次。
idx_tup_read        从指定索引获取tid的条(tid是指向heap表数据块上某条记录的一个数据结构, 如(1,10)表示1号数据块的第10条记录)
idx_tup_fetch       从索引的tid获取HEAP tuple的记数（每扫一条tuple计1）

pg_statio_all_indexes 的

idx_blks_read       该索引的buffer read记数(每读一个block记1次), 包括在postgresql shared buffers中命中的，或者未在postgresql shared buffers中命中的。
idx_blks_hit        在postgresql shared buffers中命中的buffer read记数。

pg_stat_all_tables 的

seq_scan            全表扫描的次数
seq_tup_read        使用全表扫描扫过的tuple条数
idx_scan            该表的所有索引扫描次数总和
idx_tup_fetch       该表的所有 从索引的tid获取HEAP tuple的条数

pg_statio_all_tables 的

heap_blks_read      该表的buffer read记数, 包括在postgresql shared buffers中命中的，或者未在postgresql shared buffers中命中的。
heap_blks_hit       在postgresql shared buffers中命中的buffer read记数。
idx_blks_read       该表所有索引 所有buffer read记数(含命中和未命中)
idx_blks_hit        该表所有索引 在shared buffer命中的buffer read记数
toast_blks_read     heap表对应的toast表统计字段   
toast_blks_hit      
tidx_blks_read      heap表的索引对应的toast索引统计字段   
tidx_blks_hit

希望能帮助到大家，还不理解就要打屁屁了。

weixin_34174132
关注
Postgresql源码解读.docx
07-03
4500页的源码解读 光是SELECT语句相关实现，就阐述了300多页 看完了你就是postgreSQL达人了 章节明细，需要精读那个功能点就仔细看
定位postgresql中疯狂执行DML的表&原理学习
Hehuyi_In的博客
08-15 2244
定位一段时间内DML量最大的表，并稍微改动监控每天表变化量。另外，也通过源码学习学习相关字段究竟是从何获取的。
Postgresql 常用数据库管理SQL
118路司机的博客
02-15 2534
修改数据库时区 ALTER DATABASE mydb SET timezone TO 'Asia/Shanghai'
openGauss 之blks_hit统计实现的代码走读
王飞活的博客
08-18 1022
​ 在openGauss中，select * from pg_stat_database可以看到每个数据库缓存命中率（blks_hit）的统计，如下所示：本文主要走读openGauss的代码来了解openGuass是在怎样进行缓存命中率的统计的。
一文读懂数仓中的pg_stat
华为云官方博客
07-07 1100
GaussDB(DWS)在SQL执行过程中，会记录表增删改查相关的运行时统计信息，并在事务提交或回滚后记录到共享的内存中。这些信息可以通过 “pg_stat_all_tables视图” 查询，也可以通过下面函数进行查询。...
阅读PostgreSQL系统目录
Auspicious航的博客
10-27 731
PostgreSQL系统目录是一个包含表和视图的模式，其中包含有关数据库内所有其他对象的元数据等。从系统目录中，我们可以发现各种操作是何时发生的，如何访问表或索引，甚至数据库系统是从内存中读取信息还是需要从磁盘获取数据。这对于数据库管理员对数据库进行管理有很大的帮助。 本文将概述系统目录，并重点介绍如何阅读它，以及如何从中提取有用的信息。 1 概述 系统目录是关系型数据库存放模式元数据的地方，比如表和列的信息，以及内部统计信息等。PostgreSQL的系统目录就是普通表。通常情况下，不应该手工修改系统
HGDB pg_stat_statements 用法
脑子进水养啥鱼？的博客
01-29 677
安装配置 --编译安装 hgdb默认编译安装好，目录 /opt/HighGo4.5.7-see/share/postgresql/extension --参数配置 #pg_stat_statements seting shared_preload_libraries='pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all pg_stat_statements.track_utility = off
PostgreSQL源码结构
01-27
PostgreSQL采用C/S(客户机/服务器)模式结构。应用层通过INET或者UnixSocket利用既定的协议与数据库服务器进行通信。另外，还有一种‘StandaloneBackend’使用的方式,虽然通过这种方式也可以启动服务器，但是一般只在...
pg_stat_monitor：PostgreSQL统计信息收集器
02-05
**pg_stat_monitor：PostgreSQL统计信息收集器** 在PostgreSQL数据库管理系统中，监控数据库的运行状态对于优化性能、排查问题以及确保系统健康至关重要。`pg_stat_monitor`是Percona公司开发的一款工具，它扩展了...
postgresql replication源码解析。
05-09
PostgreSQL 复制源码解析 PostgreSQL 复制是指在数据库中实现数据的复制，以确保数据的一致性和可靠性。复制可以分为逻辑复制和物理复制两种。 逻辑复制是指将数据重新执行一次insert、update或delete，以实现数据...
基于PostgreSQL WAL日志解析的WalMiner设计源码
最新发布
09-24
WalMiner是一个利用C语言开发的PostgreSQL WAL日志解析工具，能够从WAL日志中提取执行的SQL语句并生成相应的undo SQL。它不依赖于PostgreSQL的logical日志级别，提供了更灵活的解析方式。该项目包含72个文件，其中19...
Postgres性能和并发
文档搬运工
02-18 1327
参考文档： PostgreSQL 9 Administration Cookbook （第二版）中文版 第十章 性能和并发 寻找执行缓慢的SQL语句（设置有点类似mysql的慢查询），将该参数修改为10秒，日志中会记录查询超过10秒的语句 #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements # and their d
PostgreSQL 如何对索引进行分析和处理
sql server的专栏
06-25 663
开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共890人左右 1 + 2 + 3）新人会进入3群。最近有人私信问POSTGRESQL 怎么比MYSQL的索引大，这个问题升级上我个人觉得从这几点...
进阶数据库系列（九）：PostgreSQL 执行计划
民工哥的博客
07-07 1374
点击下方名片，设为星标！回复“1024”获取2TB学习资源！前面介绍了 PostgreSQL 常用管理命令、访问控制与认证、语法、数据类型、常用函数、锁操作相关的知识点，今天我将详细的为大家介绍 PostgreSQL 执行计划介绍相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！了解 PostgreSQL 执行计划对于程序员来说是一项关键技能，执行计划是我们优化查询，验证我...
程序员硬核“年终大扫除”，清理了数据库 70GB 空间
AI科技大本营
02-02 7128
作者 |Haki Benita编译 | 伍杏玲出品 | AI科技大本营（ID：rgznai100）【导语】春节将至，俗话说“腊月二十四，掸尘扫房子”，很多人会在腊月二十四给家里做大扫除...
未完笔记
weixin_40593250的博客
12-07 104
Linux 记录 更改时间命令：date -s “更改后的时间” hwclock -w vmstat命令：vmstat delay count 虚拟内存统计工具，可以看每秒有多少个CPU进程在进出、当前CPU队列中有几个进程在等待。 iostat命令：iostat -dx delay count 监控磁盘的写入写出。 mpstat命令：mpstat delay count ...
跟我一起读postgresql源码(九)——Executor(查询执行模块之——Scan节点(上))
weixin_30352645的博客
01-13 422
从前面介绍的可优化语句处理相关的背景知识、实现思想和执行流程，不难发现可优化语句执行的核心内容是对于各种计划节点的处理，由于使用了节点表示、递归调用、统一接口等设计，计划节点的功能相对独立、代码总体流程相似，下面介绍执行器中各种计划节点的相关执行过程。 在PostgreSQL中，计划节点分为四类，分别是控制节点（Control Node)、扫描节点（ScanNode),物化节点（Materiali...
PostgreSQL 怎么找到缺少的索引和相关问题的解决
sql server的专栏
08-15 981
在POSTGRESQL 的使用中，找到缺失的索引这个问题在维护POSTGRESQL 是一个重要的工作，在商业数据库中都有一些语句或工具可以直接给出一些添加索引的建议来帮助DB 人员快速的识别和建立相关的索引。PostgreSQL 在这方面有没有什么成熟的方案或办法。首先网络上大部分的方案基于的都是系统表，并通过系统表来判断是否有缺失索引的问题，这样的方法的好处是通过现有的系统表就可以找到相关的信息...
深入理解PostgreSQL：源码解析
"该文档是一份详尽的PostgreSQL源码解读资料，共计4500页，专门针对PostgreSQL数据库系统的核心功能进行了深入解析。文档涵盖了从数据插入到查询处理的全过程，通过一系列章节逐步剖析了PostgreSQL的内部工作原理。...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值