内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch15 (実行計画の取得/解析) (3/3)
実行計画の見方
処理コストの見積もり
2020-02-24 08:27:46.782 UTC [564] LOG: duration: 0.023 ms plan:
Query Text: SELECT * FROM tbl2 t JOIN tbl2 t2 USING (col);
Merge Join (cost=359.57..860.00 rows=32512 width=4) (actual time=0.016..0.019 rows=10 loops=1)
Merge Cond: (t.col = t2.col)
-> Sort (cost=179.78..186.16 rows=2550 width=4) (actual time=0.010..0.010 rows=10 loops=1)
Sort Key: t.col
Sort Method: quicksort Memory: 25kB
-> Seq Scan on tbl2 t (cost=0.00..35.50 rows=2550 width=4) (actual time=0.006..0.006 rows=10 loops=1)
-> Sort (cost=179.78..186.16 rows=2550 width=4) (actual time=0.004..0.004 rows=10 loops=1)
Sort Key: t2.col
Sort Method: quicksort Memory: 25kB
-> Seq Scan on tbl2 t2 (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=10 loops=1)
- こういうやつ
cost=0.00..35.50
cost=始動コスト..総コスト- 1件目のデータを返却できるようになるまでにかかるコスト
- seq scanなら0から始動する
- インデックススキャンなら、インデックスを検索してから1件目のデータを取り出すので0始動でない
CREATE TABLE testtbl (id int primary key, col int); INSERT INTO testtbl VALUES (generate_series(1,10), generate_series(1,10)); SELECT * FROM testtbl t1 JOIN testtbl t2 ON t1.id < t2.id;
2020-02-24 12:15:28.779 UTC [564] LOG: duration: 0.037 ms plan:
Query Text: select * from testtbl t1 join testtbl t2 on t1.id < t2.id;
Nested Loop (cost=0.15..47229.85 rows=1702533 width=16) (actual time=0.009..0.025 rows=45 loops=1)
-> Seq Scan on testtbl t1 (cost=0.00..32.60 rows=2260 width=8) (actual time=0.004..0.005 rows=10 loops=1)
-> Index Scan using testtbl_pkey on testtbl t2 (cost=0.15..13.35 rows=753 width=8) (actual time=0.001..0.001 rows=4 loops=10)
Index Cond: (id > t1.id)
これ
cost=0.15..13.35
- 下位のノードのコストは上位ノードに加算される
- どのノードで総コストがどれだけ増加したかが肝要
行長と行数
CREATE TABLE tbl (id serial primary key, name text); INSERT INTO tbl (name) VALUES ('hoge'); ANALYZE tbl; SELECT * FROM tbl;
2020-02-24 12:28:47.240 UTC [564] LOG: duration: 0.010 ms plan:
Query Text: SELECT * FROM tbl;
Seq Scan on tbl (cost=0.00..1.01 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=1)
(cost=0.00..1.01 rows=1 width=9)
width=9ってなに- id: 4バイト
- 4バイト整数(serial)
- text: 5バイト
- 可変長文字列
- 4文字 = 4バイト
- 126バイト以下だから、ヘッダが1バイト
- id: 4バイト
- ANALYZEしないとタプルのバイト長がわからないのでコスト見積もりは不正確となる
- 行のバイト長は
pg_statsビューで確認できる
SELECT tablename,attname,avg_width FROM pg_stats WHERE tablename='tbl';
tablename | attname | avg_width -----------+---------+----------- tbl | id | 4 tbl | name | 5 (2 rows)
- 行数は
pg_classで確認できる
SELECT relname,reltuples FROM pg_class WHERE relname='tbl';
relname | reltuples ---------+----------- tbl | 1 (1 row)
処理コスト見積もりのパラメータ
- 公式/プランナコスト定数
- 見積もりのチューニング
postgresql.conf
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#parallel_tuple_cost = 0.1 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above
#jit_above_cost = 100000 # perform JIT compilation if available
# and query more expensive than this;
# -1 disables
#jit_inline_above_cost = 500000 # inline small functions if query is
# more expensive than this; -1 disables
#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if
# query is more expensive than this;
# -1 disables
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB
seq_page_cost,random_page_cost- シーケンシャルアクセス/ランダムアクセスのコスト比
- SSDの場合は後者を1に近づけるなど
effective_cache_size- 共有バッファ、OSファイルキャッシュ、ディスクキャッシュ諸々、PostgreSQLが利用していると仮定できるメモリサイズ
- 他のはあまりいじらない
処理コスト見積もりの例
- サンプルテーブルをつくる
- 1e7行
- 1〜10のランダム整数
CREATE TABLE random_t (i int); INSERT INTO random_t VALUES (generate_series(1,10000000)); UPDATE random_t SET i = 1 + floor(random() * 10); CREATE INDEX ON random_t USING btree (i); VACUUM FULL VERBOSE random_t; ANALYZE random_t;
- 統計情報
- 行数
- ページ数
SELECT relname,relpages,reltuples FROM pg_class WHERE relname LIKE 'random_t%';
relname | relpages | reltuples ----------------+----------+--------------- random_t | 44248 | 1.0000048e+07 random_t_i_idx | 27460 | 1.0000048e+07 (2 rows)
- カラムに関する統計情報
SELECT tablename,attname,avg_width,most_common_vals,most_common_freqs FROM pg_stats WHERE tablename LIKE 'random_t';
-[ RECORD 1 ]-----+--------------------------------------------------------------------------------------------------------------
tablename | random_t
attname | i
avg_width | 4
most_common_vals | {1,5,8,10,7,2,6,3,9,4}
most_common_freqs | {0.10406667,0.10136667,0.101166666,0.10063333,0.10053334,0.10006667,0.099366665,0.09836667,0.0978,0.09663333}
シンプルなシーケンシャルスキャンの場合
EXPLAIN SELECT * FROM random_t;
QUERY PLAN -------------------------------------------------------------------- Seq Scan on random_t (cost=0.00..144248.48 rows=10000048 width=4) (1 row)
- 総コスト
144248.48の出処
(seq_page_cost * relpages) + (cpu_tuple_cost * reltuples) = (1.0 * 44248) + (0.01 * 1.0000048e+07) = 144248.47999999998
条件付きシーケンシャルスキャンの場合
EXPLAIN SELECT * FROM random_t WHERE i <= 5;
QUERY PLAN --------------------------------------------------------------------------------- Seq Scan on random_t (cost=0.00..169248.60 rows=5005024 width=4) Filter: (i <= 5) JIT: Functions: 2 Options: Inlining false, Optimization false, Expressions true, Deforming true (5 rows)
cost=0.00..169248.60の169248.60の出処
(seq_page_cost * relpages) + (cpu_tuple_cost * reltuples) + (cpu_operator_cost * 演算子の数 * reltuples) = (1.0 * 44248) + (0.01 * 1.0000048e+07) + (0.0025 * 1 * 1.0000048e+07) = 169248.59999999998
rows=5005024の出処
reltuples * selectivity = 1.0000048e+07 * (0.10406667 + 0.09663333 + 0.09836667 + 0.10006667 + 0.10406667) = 5032024.25360048
EXPLAIN SELECT * FROM random_t WHERE i % 2 = 1;
ソート処理の場合
- インデックスなし版の
random_t2も作った
EXPLAIN ANALYZE SELECT i FROM random_t2 ORDER BY i;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Sort (cost=1580360.76..1605360.71 rows=9999977 width=4) (actual time=3046.892..3835.767 rows=10000000 loops=1) Sort Key: i Sort Method: external merge Disk: 137000kB -> Seq Scan on random_t2 (cost=0.00..144247.77 rows=9999977 width=4) (actual time=0.040..596.300 rows=10000000 loops=1) Planning Time: 0.028 ms Execution Time: 4089.538 ms (6 rows)
- テキストの机上計算と合わなかったのでスキップ
インデックススキャンの場合
EXPLAIN SELECT i FROM random_t WHERE i = 4;
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on random_t (cost=18937.08..75818.38 rows=1010664 width=4)
Recheck Cond: (i = 4)
-> Bitmap Index Scan on random_t_i_idx (cost=0.00..18684.42 rows=1010664 width=0)
Index Cond: (i = 4)
(4 rows)
- 下記項目より算出される
- インデックスのディスクアクセスコスト
seq_page_cost- インデックスの行数
- インデックスのCPUコスト
cpu_operator_costcpu_index_tuple_cost
- テーブルデータのディスクアクセスコスト
- インデックスの並び順とテーブルの行の物理的な並び順の揃い具合を考慮する
pg_statsのcorrelation- 0に近いと
random_page_costに近づく - 1,-1に近いと
seq_page_costに近づく
- 0に近いと
- テーブルデータのCPUコスト
- インデックスですでに判定済の条件式ぶんのコストは除く
SELECT tablename,attname,correlation FROM pg_stats WHERE tablename LIKE 'random_t';
tablename | attname | correlation -----------+---------+------------- random_t | i | 0.09853651 (1 row)
見積もりと実行計画の差
- 見積もりは見積もり
- 実際に期待する性能が出ているかはわからない
- ソートノードでは、実行して初めて確認できる情報がある
- Sort Method
- ANALYZEを有効にすると、実際に問い合わせを実行する
CREATE TABLE tbl1 (col int); CREATE TABLE tbl2 (col int); INSERT INTO tbl1 VALUES (generate_series(1,1000000)); INSERT INTO tbl2 VALUES (generate_series(1,10000000)); ANALYZE tbl1; ANALYZE tbl2;
EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl1.col * 10 = tbl2.col;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather (cost=155275.45..290344.57 rows=1000000 width=8)
Workers Planned: 2
-> Parallel Hash Join (cost=154275.45..189344.57 rows=416667 width=8)
Hash Cond: ((tbl1.col * 10) = tbl2.col)
-> Parallel Seq Scan on tbl1 (cost=0.00..8591.67 rows=416667 width=4)
-> Parallel Hash (cost=85914.87..85914.87 rows=4166687 width=4)
-> Parallel Seq Scan on tbl2 (cost=0.00..85914.87 rows=4166687 width=4)
JIT:
Functions: 10
Options: Inlining false, Optimization false, Expressions true, Deforming true
(10 rows)
- 行を大量に削除して実行計画を取得してみる
DELETE FROM tbl1 WHERE col > 10; DELETE FROM tbl2 WHERE col > 10; EXPLAIN SELECT * FROM tbl1 JOIN tbl2 ON tbl1.col * 10 = tbl2.col;
- 統計情報を更新していないので、変わらない
QUERY PLAN ----------------------------------------------------------------------------------------- Gather (cost=155275.45..290344.57 rows=1000000 width=8) Workers Planned: 2 -> Parallel Hash Join (cost=154275.45..189344.57 rows=416667 width=8) Hash Cond: ((tbl1.col * 10) = tbl2.col) -> Parallel Seq Scan on tbl1 (cost=0.00..8591.67 rows=416667 width=4) -> Parallel Hash (cost=85914.87..85914.87 rows=4166687 width=4) -> Parallel Seq Scan on tbl2 (cost=0.00..85914.87 rows=4166687 width=4) JIT: Functions: 10 Options: Inlining false, Optimization false, Expressions true, Deforming true (10 rows)
- 実際に問い合わせを実行すると
EXPLAIN ANALYZE SELECT * FROM tbl1 JOIN tbl2 ON tbl1.col * 10 = tbl2.col;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1001.23..107750.57 rows=10 width=8) (actual time=4.149..248.805 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=1.23..106749.57 rows=4 width=8) (actual time=144.520..224.889 rows=0 loops=3)
Hash Cond: (tbl2.col = (tbl1.col * 10))
-> Parallel Seq Scan on tbl2 (cost=0.00..85914.87 rows=4166687 width=4) (actual time=143.206..223.575 rows=3 loops=3)
-> Hash (cost=1.10..1.10 rows=10 width=4) (actual time=3.931..3.931 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on tbl1 (cost=0.00..1.10 rows=10 width=4) (actual time=0.005..0.010 rows=10 loops=1)
Planning Time: 0.084 ms
JIT:
Functions: 30
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 2.015 ms, Inlining 0.000 ms, Optimization 0.185 ms, Emission 3.615 ms, Total 5.815 ms
Execution Time: 249.450 ms
(15 rows)
- 行数の見積もりと実行数とが著しく乖離している
(cost=1001.23..107750.57 rows=10 width=8)
(actual time=4.149..248.805 rows=1 loops=1)
- 統計情報を更新すると、実行計画のノードの構造もがらりと変わる
ANALYZE tbl1; ANALYZE tbl2; EXPLAIN ANALYZE SELECT * FROM tbl1 JOIN tbl2 ON tbl1.col * 10 = tbl2.col;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.23..2.48 rows=10 width=8) (actual time=0.016..0.018 rows=1 loops=1)
Hash Cond: ((tbl1.col * 10) = tbl2.col)
-> Seq Scan on tbl1 (cost=0.00..1.10 rows=10 width=4) (actual time=0.005..0.006 rows=10 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=4) (actual time=0.006..0.006 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on tbl2 (cost=0.00..1.10 rows=10 width=4) (actual time=0.002..0.003 rows=10 loops=1)
Planning Time: 0.046 ms
Execution Time: 0.030 ms
(8 rows)
- 総実行時間に対する影響も大きいので定期的に取得しよう
