内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch15 (実行計画の取得/解析) (2/3)
実行計画の取得方法(つづき)
実行計画を自動収集する拡張モジュール「auto_explain」
- 公式/auto explain
- クエリがどの実行計画で実行されたかを集める人
- 性能低下のリスクを素早く発見できる
- インデックスが有効に活用されているか
- テーブルの結合順序が変化してしまっていないか
auto_explainのインストール方法
- 自分でコンパイルする
- パッケージ管理システムで入れる
auto_explainの利用方法
shared_preload_librariesを設定する方法
SELECT name,setting,context FROM pg_settings where name LIKE '%_preload_libraries';
name | setting | context ---------------------------+---------+------------ local_preload_libraries | | user session_preload_libraries | | superuser shared_preload_libraries | | postmaster (3 rows)
- #shared_preload_libraries = '' # (change requires restart) + shared_preload_libraries = 'auto_explain';
- サーバのrestart必要
- 全セッションで自動的にEXPLAINが実行されるようになる
SQLのLOAD文で読み込む方法
Command: LOAD Description: load a shared library file Syntax: LOAD 'filename' URL: https://www.postgresql.org/docs/12/sql-load.html
- 公式
- LOAD文を実行したセッションのみ
LOAD 'auto_explain';
auto_explainで利用可能なオプション
auto_explain.*という名前のパラメータ群
SELECT name,context,vartype,enumvals FROM pg_settings WHERE name LIKE 'auto_explain.%';
name | context | vartype | enumvals
------------------------------------+-----------+---------+--------------------------------------------------------------
auto_explain.log_analyze | superuser | bool |
auto_explain.log_buffers | superuser | bool |
auto_explain.log_format | superuser | enum | {text,xml,json,yaml}
auto_explain.log_level | superuser | enum | {debug5,debug4,debug3,debug2,debug1,info,notice,warning,log}
auto_explain.log_min_duration | superuser | integer |
auto_explain.log_nested_statements | superuser | bool |
auto_explain.log_settings | superuser | bool |
auto_explain.log_timing | superuser | bool |
auto_explain.log_triggers | superuser | bool |
auto_explain.log_verbose | superuser | bool |
auto_explain.sample_rate | superuser | real |
(11 rows)
auto_explain.log_min_duration
- 指定のミリ秒以上かかったクエリの実行計画をログ出力する
- -1:無効
- 0:すべてのクエリ
- デフォルト-1なので、
auto_explain利用時は設定必須
SELECT name,setting,context,unit FROM pg_settings WHERE name='auto_explain.log_min_duration';
name | setting | context | unit -------------------------------+---------+-----------+------ auto_explain.log_min_duration | -1 | superuser | ms (1 row)
- 0に設定してみる
SET auto_explain.log_min_duration = 0s;
insert into tbl values(generate_series(1,1000));
- ログ
2020-02-24 07:17:53.461 UTC [564] LOG: duration: 0.676 ms plan:
Query Text: insert into tbl values(generate_series(1,1000));
Insert on tbl (cost=0.00..5.02 rows=1000 width=4)
-> ProjectSet (cost=0.00..5.02 rows=1000 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
EXPLAIN文と同じ機能のやつ
log_analyzelog_verboselog_bufferslog_formatlog_timing
auto_explain特有のやつ
sample_rate- ログ出力する割合(
[0,1]の実数) - 1未満にすると、ログ出力したりしなかったりする
- ログ出力する割合(
log_nested_statement- 入れ子状の文の実行計画も表示する
- ユーザ定義関数とか
- 入れ子状の文の実行計画も表示する
log_triggers- トリガ実行の統計を出力
実行計画の構造
CREATE TABLE tbl (id int primary key); INSERT INTO tbl VALUES(generate_series(1, 1000));
SELECT * FROM tbl t NATURAL INNER JOIN tbl t2 WHERE id < 50 ORDER BY id;
- 階層構造
2020-02-24 07:51:41.676 UTC [564] LOG: duration: 0.123 ms plan:
Query Text: select * from tbl t natural inner join tbl t2 where id < 50 order by id;
Sort (cost=28.76..28.88 rows=49 width=4)
Sort Key: t.id
-> Hash Join (cost=9.75..27.38 rows=49 width=4)
Hash Cond: (t2.id = t.id)
-> Seq Scan on tbl t2 (cost=0.00..15.00 rows=1000 width=4)
-> Hash (cost=9.13..9.13 rows=49 width=4)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Index Only Scan using tbl_pkey on tbl t (cost=0.28..9.13 rows=49 width=4)
Index Cond: (id < 50)
- 深いノードから実行され、最上位ノードは最後に実行される
スキャン系ノード
- ふつう実行計画の最下層に現れて、最初に実行される
複数のデータを結合するノード
入れ子ループ構造
CREATE TABLE tbl3 (col int); INSERT INTO tbl3 values(generate_series(1,3)); SELECT * FROM tbl3 t1 CROSS JOIN tbl3 t2;
2020-02-24 08:14:32.502 UTC [564] LOG: duration: 0.016 ms plan:
Query Text: select * from tbl3 t1 cross join tbl3 t2;
Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
-> Seq Scan on tbl3 t1 (cost=0.00..35.50 rows=2550 width=4)
-> Materialize (cost=0.00..48.25 rows=2550 width=4)
-> Seq Scan on tbl3 t2 (cost=0.00..35.50 rows=2550 width=4)
Nested Loop (cost=0.00..81358.62 rows=6502500 width=8)
- これ
- 外側テーブルの件数が多いと遅い
- 外側テーブルの行数が少なく、内側テーブルのインデックスが効くと速い
ハッシュ結合
SELECT * FROM tbl t NATURAL INNER JOIN tbl t2 WHERE id < 1000 ORDER BY id;
2020-02-24 07:55:31.466 UTC [564] LOG: duration: 0.370 ms plan:
Query Text: select * from tbl t natural inner join tbl t2 where id < 1000;
Hash Join (cost=27.50..47.63 rows=999 width=4)
Hash Cond: (t.id = t2.id)
-> Seq Scan on tbl t (cost=0.00..17.50 rows=999 width=4)
Filter: (id < 1000)
-> Hash (cost=15.00..15.00 rows=1000 width=4)
Buckets: 1024 Batches: 1 Memory Usage: 44kB
-> Seq Scan on tbl t2 (cost=0.00..15.00 rows=1000 width=4)
Hash Join (cost=27.50..47.63 rows=999 width=4)
Hash Cond: (t.id = t2.id)
これ
内側テーブルの結合キーでハッシュテーブルを作成する
-> Hash (cost=15.00..15.00 rows=1000 width=4)
Buckets: 1024 Batches: 1 Memory Usage: 44kB
-> Seq Scan on tbl t2 (cost=0.00..15.00 rows=1000 width=4)
- ハッシュテーブルがメモリ上に収まるなら速い
- テーブル作成でオーバヘッドがあるが、ルックアップが速い
マージ結合
CREATE TABLE tbl2 (col int); INSERT INTO tbl2 VALUES (generate_series(1,10)); SELECT * FROM tbl2 t JOIN tbl2 t2 USING (col);
2020-02-24 08:04:46.786 UTC [564] LOG: duration: 0.019 ms plan:
Query Text: select * from tbl2 t join tbl2 t2 using (col);
Merge Join (cost=359.57..860.00 rows=32512 width=4)
Merge Cond: (t.col = t2.col)
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: t.col
-> Seq Scan on tbl2 t (cost=0.00..35.50 rows=2550 width=4)
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: t2.col
-> Seq Scan on tbl2 t2 (cost=0.00..35.50 rows=2550 width=4)
Merge Join (cost=359.57..860.00 rows=32512 width=4)
Merge Cond: (t.col = t2.col)
- これ
- 結合キーソートして順番に突き合わせる
- ので、事前のソートが必要
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: t.col
-> Seq Scan on tbl2 t (cost=0.00..35.50 rows=2550 width=4)
-> Sort (cost=179.78..186.16 rows=2550 width=4)
Sort Key: t2.col
-> Seq Scan on tbl2 t2 (cost=0.00..35.50 rows=2550 width=4)
- B-treeインデックスは「事前にソート済」
- PostgreSQLでは主キーは必ずB-treeインデックスなので、結合キー=主キーならソート済
- 大きなテーブル同士の結合で、ソートが不要で、ハッシュがメモリに収まらない場合に有効
データを加工するノード
その他のノード
- Hashとか
- パフォーマンスチューニング上、考慮すべき優先順位低め
ソートノード
- EXPLAINのANALYZEが有効だとソートメソッドも表示される
- 主キーはB-treeインデックスなのでそもそもソートされない
TRUNCATE tbl; INSERT INTO tbl VALUES (generate_series(1,1000000)); SELECT * FROM tbl ORDER BY id LIMIT 10 OFFSET 500000;
2020-02-24 08:45:07.614 UTC [564] LOG: duration: 93.325 ms plan:
Query Text: SELECT * FROM tbl ORDER BY id LIMIT 10 OFFSET 500000;
Limit (cost=15204.42..15204.73 rows=10 width=4) (actual time=93.312..93.314 rows=10 loops=1)
-> Index Only Scan using tbl_pkey on tbl (cost=0.42..30408.42 rows=1000000 width=4) (actual time=0.017..82.077 rows=500010 loops=1)
Heap Fetches: 500010
- クイックソート (quicksort)
- 行数が少なく、メモリ上に乗る
TRUNCATE tbl2; INSERT INTO tbl2 VALUES (generate_series(1,10000)); SELECT * FROM tbl2 ORDER BY col LIMIT 10 OFFSET 5000;
2020-02-24 08:48:45.031 UTC [564] LOG: duration: 1.777 ms plan:
Query Text: SELECT * FROM tbl2 ORDER BY col LIMIT 10 OFFSET 5000;
Limit (cost=934.80..934.82 rows=10 width=4) (actual time=1.768..1.770 rows=10 loops=1)
-> Sort (cost=922.30..950.99 rows=11475 width=4) (actual time=1.495..1.659 rows=5010 loops=1)
Sort Key: col
Sort Method: quicksort Memory: 853kB
-> Seq Scan on tbl2 (cost=0.00..159.75 rows=11475 width=4) (actual time=0.031..0.687 rows=10000 loops=1)
- 外部ソート (external merge)
- いわゆるファイルソート
- メモリに収まらない
TRUNCATE tbl2; INSERT INTO tbl2 VALUES (generate_series(1,1000000)); SELECT * FROM tbl2 ORDER BY col LIMIT 10 OFFSET 500000;
2020-02-24 08:45:23.997 UTC [564] LOG: duration: 165.921 ms plan:
Query Text: SELECT * FROM tbl2 ORDER BY col LIMIT 10 OFFSET 500000;
Limit (cost=112519.91..112521.08 rows=10 width=4) (actual time=163.561..165.911 rows=10 loops=1)
-> Gather Merge (cost=54182.50..151411.59 rows=833334 width=4) (actual time=76.291..153.965 rows=500010 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=53182.48..54224.15 rows=416667 width=4) (actual time=55.314..68.423 rows=167538 loops=3)
Sort Key: col
Sort Method: external merge Disk: 6648kB
Worker 0: Sort Method: external merge Disk: 3640kB
Worker 1: Sort Method: external merge Disk: 3528kB
-> Parallel Seq Scan on tbl2 (cost=0.00..8591.67 rows=416667 width=4) (actual time=0.009..19.380 rows=333333 loops=3)
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.119 ms, Inlining 0.000 ms, Optimization 0.059 ms, Emission 0.490 ms, Total 0.669 ms
- top-N heapsort
- 外部ソートになりそうなヤツでも、先頭数行とかならこれになるみたい
- 先頭N行ぶんだけヒープ構造を確保して、溢れたぶんは捨てる？
SELECT * FROM tbl2 ORDER BY col LIMIT 10;
2020-02-24 08:47:16.593 UTC [564] LOG: duration: 61.159 ms plan:
Query Text: SELECT * FROM tbl2 ORDER BY col LIMIT 10;
Limit (cost=18595.71..18596.88 rows=10 width=4) (actual time=59.413..61.150 rows=10 loops=1)
-> Gather Merge (cost=18595.71..115824.80 rows=833334 width=4) (actual time=59.412..61.148 rows=10 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=17595.69..18637.36 rows=416667 width=4) (actual time=40.085..40.085 rows=10 loops=3)
Sort Key: col
Sort Method: top-N heapsort Memory: 25kB
Worker 0: Sort Method: top-N heapsort Memory: 25kB
Worker 1: Sort Method: top-N heapsort Memory: 25kB
-> Parallel Seq Scan on tbl2 (cost=0.00..8591.67 rows=416667 width=4) (actual time=0.009..20.328 rows=333333 loops=3)
