統計情報コレクタの標準統計情報ビュー・インデックス関連情報編

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
■■■■ PostgreSQL スキルアップノート
■■■■
■◆■■ 統計情報コレクタの標準統計情報ビュー・インデックス関連情報編
■■■■
■■■■
■■■■ 2012/11/17
■■■■ 使用環境：PostgreSQL9.1.4 (CentOS6.2)
                                                                   (C) 2012 ohdb
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
★自己都合により実機確認は9.1、マニュアルへのリンクは9.2としています。ご了承下さい。


【マニュアル】
・統計情報コレクタ→●[マニュアル]データベース活動状況の監視−統計情報コレクタ
・実行時統計情報→●[マニュアル]サーバの設定−実行時統計情報


【参考記事】
なし

・統計情報コレクタによって反映された情報




■１■ pg_stat_all_indexes  
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━



【□】 

select
    schemaname   ,
    relname      ,
    indexrelname ,
    idx_scan     ,
    idx_tup_read ,
    idx_tup_fetch
from pg_stat_user_indexes;


test1-# from pg_stat_user_indexes;
 schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
------------+---------+--------------+----------+--------------+---------------
 public     | t10     | t10_idx      |        5 |           32 |            32
 public     | t7      | t7_idx       |        0 |            0 |             0
(2 rows)





test1=# \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   |
 idx_tup_read  | bigint |           | plain   |
 idx_tup_fetch | bigint |           | plain   |

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"]);





■２■ pg_statio_all_indexes  ★特に重要
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

【□】 

select
schemaname    ,
relname       ,
indexrelname  ,
idx_blks_read ,
idx_blks_hit  
from pg_statio_user_indexes;


 schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
------------+---------+--------------+---------------+--------------
 public     | t10     | t10_idx      |           920 |       762085
 public     | t7      | t7_idx       |             0 |            0



test1=# \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   |
 idx_blks_hit  | bigint |           | plain   |
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"]);









以上