【PostgreSQL】パフォーマンスチューニング(できるようになるための知識)

# コマンド
## EXPLAIN文
実行計画を確認したいSQLの前に「EXPLAIN」をつけることで、実行計画を表示できる。

```sql:
[test_db=# explain select * from sample_tbl;
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on sample_tbl  (cost=0.00..22.70 rows=1270 width=36)
```

### ANALYZEオプション
EXPLAIN文で表示されるのは、予想コスト。
ANALYZEオプションをつけることで、実際の処理時間も表示させることができる。
※実際にクエリが実行されるため、INSERT/UPDATE/DELETEの場合は注意する

```sql:
test_db=# explain analyze select * from sample_tbl;
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on sample_tbl  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.012..0.013 rows=2 loops=1)
 Planning time: 0.067 ms
 Execution time: 0.039 ms
(3 rows)
```

「EXPLAIN ANALYZE」を実行した場合の表示内容の意味は以下のとおり。

- actual time : 処理時間
- rows : 実行結果として、実際に戻ってきた行数
- loops : ステップの実行回数
- Plannning time : 解析されたクエリから実行計画を生成し、最適化するのに要した時間
- Execution time : 実行時間

## ANALYZE文
統計情報を取得するためのSQL。

```sql:
[test_db=# analyze sample_tbl;
ANALYZE
```

# 実行計画の見方
実行計画はツリー構造になっている。
表示された実行計画を確認する際にも、最下層のノードから処理を追っていく。

## ノードの種類
各処理の単位であるノードについて整理。

### テーブルスキャンノード
テーブルからデータを取り出す役割のノード。

- Seq Scan
- Index Scan
- Bitmap Scan

```SQL:
Seq Scan on app_detail  (cost=0.00..418.68 rows=8468 width=348) (actual time=0.003..7.486 rows=8469 loops=1)

Index Scan using app_detail_pkey on app_detail  (cost=0.00..1455.15 rows=8468 width=348) (actual time=0.066..10.038 rows=8469 loops=1)
```

## cost
costには2つの数値が出力される。
1つ目は**始動コスト**で、「最初の行が返されるまでの時間」。
2つ目は**総コスト**で、「すべての行が返されるまでの時間」。
※バージョンによって違うこともあるらしい...

```sql:
(cost=0.00..12769.39 rows=235939 width=56) (actual time=0.045..1179.461 rows=235939 loops=1)
```



### 結合系ノード
複数テーブルを結合する役割のノード。
※[結合方式とインデックスの関係](http://d.hatena.ne.jp/tgk/20090114/1231927095)も理解しておく

#### Nested Loop Join
- outer table1行ごとに、inner tableを走査する
    - そのため、テーブル件数が増えるとコストが大きくなる
    - **結合キーでindexがある場合は、inner table走査時にIndex Scanになるため、高速化**ができる

- 有効なケース
    - 1.結合されるテーブルどちらかのデータ量が少なくて、もう一方が多い
        - inner table
    - 2.inner table側がインデックススキャンを使える
        - INNER JOINとかLEFT OUTER JOINに指定しているテーブルが「inner table」

#### Hash Join
- inner tableのハッシュ表をメモリ上に作成する
    - サイズがメモリサイズより大きくなってしまった場合、遅くなる
    - **結合する列にインデックスは不要**
        - パフォーマンス改善をするときには、**WHERE句述語にインデックスを定義**する

- 有効なケース
    - 結合対象のデータ量が多い場合
        - かつ、データがソートされていない場合

#### Sort Merge Join
- 結合キーでソートし、マッチングする(※「ファスナーのように組み合わせる」イメージ)
    - outer tableは１回調べればよく、走査回数は減る
    - ソートさえできれば速い
        - なので、**結合キーにインデックス**を張れば早くなる

- 有効なケース
    - 結合対象のデータ量が多い場合
        - かつ、データが既にソートされている場合


# SQLパフォーマンスチューニングのポイント
あくまでも個人的な考え。もっと充実させていきたい。

## 作業の流れ
- まずはexplain analyzeで、実際の実行プランを取得
- 統計情報が正しいか？
    - 推定のrowsと、実際のrowsの値を比較する
    - 近い場合は「統計情報が正しい」といえるが、あまりにも離れている場合は「統計情報が正しくない」
    - その場合は、ANALYZE文を実行して統計情報を最新化する

## 実行プラン確認
- 実行プランから、ネックになっている処理を突き止める
    - まずは「Total runtime」を確認
    - 各処理の「actual time」(実際の処理時間)を確認
        - Total runtimeに対して、最も時間がかかっている処理を突き止める
        - その処理に対して「なぜ重いのか」を推測し、チューニングを実行していく

    - 「なぜ重いのか」を考える観点としては...
        - インデックスを正しく使用しているか
            - Seq Scanではなく、Index Scanを実行したい
                - インデックスの貼り忘れ？
                - WHERE句の条件の指定の仕方が悪い？
        - 正しく件数を絞り込めているか(rowsの件数)
            - 「不要なデータを抽出したりしていないか」
            - 「JOINするレコード数を減らせないか」
                - 必要なレコードを先に絞り込んでからJOINする、など



# 参考URL
- [PostgreSQLの実行計画について調べてみた](https://www.casleyconsulting.co.jp/blog/engineer/259/)
- [PostgreSQLでインデックスを効率良く利用しよう](http://www.techscore.com/blog/2014/12/04/postgresql%E3%81%A7%E3%82%A4%E3%83%B3%E3%83%87%E3%83%83%E3%82%AF%E3%82%B9%E3%82%92%E5%8A%B9%E7%8E%87%E9%AB%98%E3%81%8F%E5%88%A9%E7%94%A8%E3%81%97%E3%82%88%E3%81%86/)
- [PostgreSQL Index Only Scan 奮闘記 その4](http://www.techscore.com/blog/2013/06/19/postgresql-index-only-scan-%E5%A5%AE%E9%97%98%E8%A8%98-%E3%81%9D%E3%81%AE4/)

- [第 11章インデックス PostgreSQL 8.1.9文書](https://www.postgresql.jp/document/8.1/html/indexes.html)


- [PostgreSQL Index Only Scan 奮闘記 その3](http://www.techscore.com/blog/2013/06/07/postgresql-index-only-scan-%E5%A5%AE%E9%97%98%E8%A8%98-%E3%81%9D%E3%81%AE3/)
- [PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！](https://employment.en-japan.com/engineerhub/entry/2017/09/05/110000#%E6%AF%94%E8%BC%83%E3%83%9D%E3%82%A4%E3%83%B3%E3%83%88%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E7%B5%90%E5%90%88JOIN%E3%81%AE%E3%82%A2%E3%83%AB%E3%82%B4%E3%83%AA%E3%82%BA%E3%83%A0)
- [結合方式とインデックスの関係](http://d.hatena.ne.jp/tgk/20090114/1231927095)

- [PostgreSQLを遅くしている犯人はどこだ？](http://www.atmarkit.co.jp/ait/articles/0806/09/news125.html)