PostgreSQL運用保守で使用するコマンドについての備忘録 随時更新
1. パフォーマンス監視、最適化
1.1 クエリ実行計画の確認
EXPLAIN ANALYZE [対象のSQL]
クエリ実行計画の表示
クエリ実行計画と、実際にSQLを実行した場合の、DBMSの内部処理の結果が表示される。その違いを見ることで、実行計画が実際に問題が無いかどうかを調べることができる。
EXPLAIN ANALYZEでは実際にクエリを発行するため、データを更新したくない場合はロールバック、またはSELECTで実行する
例
EXPLAIN ANALYZE SELECT * FROM test_table WHERE testname = 'test_value';
実行結果例
QUERY PLAN
--------------------------------------------------------------
Seq Scan on test_table (cost=0.00..10.50 rows=5 width=32) (actual time=0.012..0.015 rows=3 loops=1)
Filter: (testname = 'test_value'::text)
Rows Removed by Filter: 7
Planning Time: 0.048 ms
Execution Time: 0.026 ms
| 項目 | 説明 |
|---|---|
| プラン演算子 | クエリを実行するためRDB内部でどのような処理を行うかを表す。代表的なものは Seq Scan, Sort, Index Scan, Nested Loop, Merge Join, Hash Join など。 |
| cost | クエリの初期コストからトータルコストまでの範囲。小さいほど効率が良い。この範囲とはクエリの最適化における予測負荷を表している。 |
| rows | 返却される行数の見積もり。 |
| width | 返却される1行あたりの長さをバイト単位で表示。 |
| actual time | 実際の実行時間の範囲。各ステップでの処理時間。 |
| rows, loops | 各ループでの処理行数、および実行されたループ回数。 |
| Filter | フィルタリング内容。例では testname が特定の値（'test_value'）でフィルタリングされている。 |
| Rows Removed by Filter | (例では)フィルタによって取り除かれた行数。 |
| Planning Time | クエリから実行計画を生成し、最適化するのにかかる時間。 |
| Execution Time | 実際のクエリ実行にかかる時間。 |
実行結果の判断について
-
主なクエリの性能評価、確認内容
・コストを確認し実行コストの低い方を採用する
・各ステップでの処理時間が想定外でないことを確認する
・クエリ実行時間が規定値を超えないこと
・上記を踏まえ、実際に実行される処理内容を確認、改善が必要か(可能か)判断
1.2 インデックス関連
インデックス定義の確認
pg_indexes テーブルを使用して、特定テーブルに関連するインデックスの定義を取得可能。
例
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'test_table';
実行結果例
indexname | indexdef
-----------+------------------------------------------------------------------------
idx_test | CREATE INDEX idx_test ON test_table USING btree (tablename)
indexnameがインデックス名、indexdefがインデックス定義(CREATE INDEX文)
インデックスに関する統計情報の確認
pg_stat_user_indexes テーブルを使用して、インデックスに関する統計情報を取得可能。
例
SELECT
schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM
pg_stat_user_indexes;
実行結果例
| schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch |
|------------|------------|--------------|----------|--------------|---------------|
| public | test_table | index_name1 | 100 | 5000 | 3000 |
| public | test_table | index_name2 | 50 | 2000 | 1500 |
| 列名 | 説明 |
|---|---|
| schemaname | スキーマ名 |
| relname | テーブル名 |
| indexrelname | インデックス名 |
| idx_scan | インデックスをスキャンした回数 |
| idx_tup_read | 読み取ったタプルの数 |
| idx_tup_fetch | フェッチしたタプルの数 |
インデックスの定義が必要な場合は pg_indexes を使用し、インデックスのパフォーマンスに関する統計情報が必要な場合は pg_stat_user_indexes を使用する。
2. メンテナンス、管理
2.1 テーブルのサイズ確認
pg_total_relation_size 関数を使用してテーブルのディスク使用量を確認可能。
例
SELECT pg_size_pretty(pg_total_relation_size('test_table'));
実行結果例
pg_size_pretty
----------------
16 MB