【PostgreSQL】運用保守で使用するコマンド


PostgreSQL運用保守で使用するコマンドについての備忘録　随時更新

## 1. パフォーマンス監視、最適化

### 1.1 クエリ実行計画の確認

    EXPLAIN ANALYZE [対象のSQL]

**クエリ実行計画の表示**
クエリ実行計画と、実際にSQLを実行した場合の、DBMSの内部処理の結果が表示される。その違いを見ることで、実行計画が実際に問題が無いかどうかを調べることができる。

:::note warn
EXPLAIN ANALYZEでは実際にクエリを発行するため、データを更新したくない場合はロールバック、またはSELECTで実行する
:::

```sql:例
EXPLAIN ANALYZE SELECT * FROM test_table WHERE testname = 'test_value';
```


```plaintext:実行結果例
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` テーブルを使用して、特定テーブルに関連するインデックスの定義を取得可能。

```sql:例
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'test_table';
```

```plaintext:実行結果例
 indexname |                                indexdef
-----------+------------------------------------------------------------------------
 idx_test  | CREATE INDEX idx_test ON test_table USING btree (tablename)
```

indexnameがインデックス名、indexdefがインデックス定義(CREATE INDEX文)

---
**インデックスに関する統計情報の確認**

`pg_stat_user_indexes` テーブルを使用して、インデックスに関する統計情報を取得可能。

```sql:例
SELECT
    schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM
    pg_stat_user_indexes;
```

```plaintext:実行結果例
 | 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` 関数を使用してテーブルのディスク使用量を確認可能。

```sql:例
SELECT pg_size_pretty(pg_total_relation_size('test_table'));
```

```plaintext:実行結果例
 pg_size_pretty
----------------
 16 MB
```