PostgreSQLメモ

gistfile1.md

PostgreSQL 調査メモ

情報取得関係

テーブル/INDEXのファイルパス

• pg_relation_filepath(oid)

  SELECT relname, pg_relation_filepath(oid) FROM pg_class WHERE relname = 'テーブル名またはindex名';

表領域のディレクトリパス

• \db

        名前    |  所有者  |                 場所                 
    ------------+----------+--------------------------------------
     pg_default | postgres |  
     pg_global  | postgres |  
     testsp1    | postgres | /var/lib/postgresql/9.2/main/testsp1  
  

テーブル内の空き領域、論理削除行、断片化

• pgstattuple

• 事前準備：CREATE EXTENSION pgstattuple;

  SELECT * FROM pgstattuple('表名または索引名');

• table_len : テーブルのファイルサイズ（バイト数）

• tuple_count : 行数

• tuple_len : 行データのバイト数

• tuple_percent : table_lenにおけるtuple_lenの割合（tuple_len / table_len）

• dead_tuple_count : 論理削除された行の数

• dead_tuple_len : 論理削除された行のバイト数

• dead_tuple_percent: table_lenにおけるdead_tuple_lenの割合（table_len / dead_tuple_len）

• free_space : table_lenにおける空き領域のバイト数

• free_percent : table_len におけるfree_space の割合

索引の断片化

• pgstatuindex

• 事前準備：pgstattupleに付属

  SELECT * FROM pgstatuindex('索引名');

• version : 索引のバージョン

• tree_level : ルートの高さ（0スタート）

• index_size : 索引ファイルのバイト数

• root_block_no : ルートブロックのブロック番号（ブロック分割により高くなると、大きな番号のブロックにルートが移動する）

• internal_pages: "内部"（上位レベル）ページ数 ←？

• leaf_pages : リーフページ数

• empty_pages : 空のページ数

• deleted_pages : 削除されたページ数

• avg_leaf_density: リーフページの平均密度（%）

• leaf_fragmentation: リーフページの断片化割合（低い方がよい）

オブジェクトのページ数

pg_class

SELECT relname, pg_relation_filepath(oid), relpages
FROM   pg_class
WHERE  relname = 'オブジェクト名';

pgstattuple

SELECT table_len
FROM   pgstattuple('オブジェクト名');

• 結果をページ・サイズで割る（デフォルト8,192バイト）

データベースのoid

select datid,datname from pg_stat_database;

SQLの実行統計

• pg_stat_statements

事前準備

• postgres.conf
  • shared_preload_libraries = 'shared_preload_libraries'
• CREATE EXTENSION shared_preload_libraries;

実行統計の確認

SELECT * FROM pg_stat_statements
WHERE  query like 'SQL文%';

• userid : SQL文を実行したユーザのOID
• dbid : SQL文が実行されたデータベースのOID
• query : 代表的な文の文字列 (track_activity_query_size バイトまで)
• calls : 実行回数
• total_time : SQL文の処理に費やした総時間（ミリ秒単位）
• rows : SQL文によって取得された、あるいは影響を受けた行の総数
• shared_blks_hit : SQL文によってヒットした共有ブロックキャッシュの総数
• shared_blks_read : SQL文によって読み込まれた共有ブロックの総数
• shared_blks_dirtied : 文によりダーティ状態となった共有ブロックの総数
• shared_blks_written : SQL文によって書き込まれた共有ブロックの総数
• local_blks_hit : SQL文によってヒットしたローカルブロックキャッシュの総数
• local_blks_read : SQL文によって読み込まれたローカルブロックの総数
• local_blks_dirtied : SQL文によりダーティ状態となったローカルブロックの総数
• local_blks_written : SQL文によって書き込まれたローカルブロックの総数
• temp_blks_read : SQL文によって読み込まれた一時ブロックの総数
• temp_blks_written : SQL文によって書き込まれた一時ブロックの総数
• blk_read_time : SQL文がブロック読み取りに費やした、ミリ秒単位の総時間 （track_io_timingが有効な場合。無効であればゼロ）
• blk_write_time : SQL文がブロック書き出しに費やした、ミリ秒単位の総時間 （track_io_timingが有効な場合。無効であればゼロ

実行統計のクリア

SELECT pg_stat_statements_reset();

• 参考URL：http://www.postgresql.jp/document/9.2/html/pgstatstatements.html