PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗

Size: px

Start display at page:

Download "PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗"

かずきうるしはた
5 years ago
Views:

1 PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗

2 アジェンダ 1.EXPLAIN とは 2. 表アクセスの基本 3. 結合の基本 4. 統計情報とは 5.EXPLAIN コマンド 6. 問題解決例 7. まとめ 2

3 1.EXPLAIN とは実行計画とは - 目的地は 1 つでもアクセス方法は複数 3

4 1.EXPLAIN とは表の状態が分からなければどのパスが最適か分からない SqeScan と IndexScan を行った時の時間実行時間 Seq Scan Index Scan 表全体に対する走査対象データの割合 4

5 1.EXPLAIN とは実行計画担当 : プランナーです 5

6 1.EXPLAIN とはプランナがどのような実行計画を作ったのかを確認する手段が本日のテーマである EXPLAIN コマンドです 6

7 1.EXPLAIN とは 1 どのようなアクセス方法が適切か 2 どのような結合方法が適切か 3 統計情報を元に実行計画を作成する事がプランナの役目 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認する 7

8 2. 表アクセス方法 (1).Seq Scan 検索範囲 : 広 (3).Bit Map Scan 検索範囲 : 中 / 特殊 (2).Index Scan 検索範囲 : 狭 NEW 9.2 (4).Index Only Scan 検索範囲 : 特殊

9 3. 表結合方法 1 どのようなアクセス方法が適切か 2 どのような結合方法が適切か 3 統計情報を元に実行計画を作成する事がプランナの役目 1.Nested Loop Join 外部表内部表特徴 : いかなる場合でも選択可能 2.Sort Merge Join 外部表内部表特徴 : ソートが完了すれば早い 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認する外部表 Hash Join 内部表特徴 : ハッシュを作成できれば早い

10 4. 統計情報 1 つ 1 つの表の行数行サイズ平均相関ヒストグラムなどを見積もったもの統計情報取得コマンド ANALYZE 表名 ; 1 どのようなアクセス方法が適切か 2 どのような結合方法が適切か 3 統計情報を元に実行計画を作成する事がプランナの役目 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認する 10

11 5.EXPLAIN コマンド 1 どのようなアクセス方法が適切か 2 どのような結合方法が適切か empno ename job : deptno emp [INT] [CHAR(10)] [CHAR(9)] [INT] deptno dname loc 3 統計情報を元に実行計画を作成する事がプランナの役目 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認する dept [INT] [VARCHAR(10)] [VARCHAR(10)] SELECT d.dname,e.ename FROM emp e JOIN dept d USING (deptno); 11

12 5.EXPLAIN コマンド Explain Plan の例 # EXPLAIN ANALYZE SELECT d.dname,e.ename FROM emp e JOIN dept d USING (deptno); QUERY PLAN Hash Join (cost= rows= width=66) (actual time= rows=90000 loops=1) Hash Cond: (e.deptno = d.deptno) -> Seq Scan on emp e (cost= rows= width=41) (actual time= rows= loops=1) -> Hash (cost= rows=10 width=37) (actual time= rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on dept d (cost= rows=10 width=37) Total runtime: ms (7 rows) ANALYZE オプションを付けることで実際に SQL が実行され actual time の情報が出力されるシステムへの影響を考慮すること (actual time= rows=10 loops=1) 12

13 5.EXPLAIN コマンド ( アクセス方法 ) Explain Plan の例 1 どのようなアクセス方法が適切か # EXPLAIN ANALYZE SELECT d.dname,e.ename FROM emp e JOIN dept d USING (deptno); QUERY PLAN Hash Join (cost= rows= width=66) (actual time= rows=90000 loops=1) Hash Cond: (e.deptno = d.deptno) -> Seq Scan on emp e (cost= rows= width=41) (actual time= rows= loops=1) -> Hash (cost= rows=10 width=37) (actual time= rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on dept d (cost= rows=10 width=37) (actual time= rows=10 loops=1) Total runtime: ms (7 rows) 2 どのような結合方法が適切か 3 統計情報を元に実行計画を作成する事がプランナの役目 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認するインデックススキャンの場合の表記 Index Scan using emp_pkey on emp e 13

14 5.EXPLAIN コマンド ( 結合方法 ) Explain Plan の例 1 どのようなアクセス方法が適切か # EXPLAIN ANALYZE SELECT d.dname,e.ename FROM emp e JOIN dept d USING (deptno); QUERY PLAN 2 どのような結合方法が適切か 3 統計情報を元に実行計画を作成する事がプランナの役目 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認する Hash Join (cost= rows= width=66) (actual time= rows=90000 loops=1) Hash Cond: (e.deptno = d.deptno) -> Seq Scan on emp e (cost= rows= width=41) (actual time= rows= loops=1) -> Hash (cost= rows=10 width=37) (actual time= rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on dept d (cost= rows=10 width=37) (actual time= rows=10 loops=1) Total runtime: ms (7 rows) 14

15 5.EXPLAIN コマンド ( 統計情報 ) Explain Plan の例 1 どのようなアクセス方法が適切かプランナが推定したコストと行数 # EXPLAIN ANALYZE SELECT d.dname,e.ename FROM emp e JOIN dept d USING (deptno); QUERY PLAN Hash Join (cost= rows= width=66) (actual time= rows=90000 loops=1) Hash Cond: (e.deptno = d.deptno) -> Seq Scan on emp e (cost= rows= width=41) (actual time= rows= loops=1) -> Hash (cost= rows=10 width=37) (actual time= rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on dept d (cost= rows=10 width=37) (actual time= rows=10 loops=1) 実際に SQL を実行した時間と行数 Total runtime: ms (7 rows) 2 どのような結合方法が適切か 3 統計情報を元に実行計画を作成する事がプランナの役目 4 どのような選択が行なわれたかどのように実行されたか EXPLAIN コマンドで確認する 15

16 5.EXPLAIN コマンド ( 統計情報 ) 見積もられた平均列長 (cost= rows= width=41) 表アクセスにかかるコストの見積もりディスクからのデータ読み込みメモリ上のスキャン CPU を使用する処理 (actual time= rows= loops=1) 16 取り出される行数の見積もり繰り返し実行された回数実際に取り出された行数実際に表アクセスにかかった時間 ( ミリ秒 )

17 5.EXPLAIN コマンド ( 統計情報見方のコツ ) Explain Plan の例 1. 統計情報は誤差が最も少なくなるであろう下 ( インデントが下のもの ) から見ていく 2. 共通するパラメータは rows # EXPLAIN ANALYZE SELECT d.dname,e.ename FROM emp e JOIN dept d USING (deptno); QUERY PLAN Hash Join (cost= rows= width=66) (actual time= rows=90000 loops=1) Hash Cond: (e.deptno = d.deptno) -> Seq Scan on emp e (cost= rows= width=41) (actual time= rows= loops=1) -> Hash (cost= rows=10 width=37) (actual time= rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on dept d (cost= rows=10 width=37) (actual time= rows=10 loops=1) Total runtime: ms (7 rows) 17

18 6. 問題解決演習 (1) 表の構成 exception_id complete exception プライマリキー exception_pkey [INT] [BOOLEAN] exception_notice_map exception_notice_map_id [INT] exception_id [INT] notice_id [INT] complete=false 全体の 0.25% SELECT exception_id FROM exception JOIN exception_notice_map USING (exception_id) WHERE complete IS FALSE AND notice_id = 3; 18

19 6. 問題解決演習 (1) =# EXPLAIN ANALYZE SELECT exception_id FROM exception -# JOIN exception_notice_map USING (exception_id) -# WHERE complete IS FALSE AND notice_id = 3; QUERY PLAN Hash Join (cost= rows=7 width=4) (actual time= rows=251 loops=1) Hash Cond: (exception_notice_map.exception_id = exception.exception_id) -> Seq Scan on exception_notice_map (cost= rows=24623 width=4) (actual time= rows=24800 loops=1) Filter: (notice_id = 3) -> Hash (cost= rows=267 width=4) (actual time= rows=251 loops=1) Seq Scan on exception (cost= Buckets: 1024 Batches: 1 Memory Usage: 6kB -> Seq Scan on exception (cost= rows=267 width=4) (actual time= rows=251 loops=1) Filter: (complete IS FALSE) Total runtime: ms (9 rows) exception 表に "WHERE complete IS False" という条件はわずかなのに全てのデータにアクセスしている 19

20 6. 問題解決演習 (1) 表の構成 exception_id complete exception プライマリキー exception_pkey [INT] [BOOLEAN] exception_notice_map exception_notice_map_id [INT] exception_id [INT] notice_id [INT] complete=false 全体の 0.25% active_exceptions を追加 SELECT exception_id FROM exception JOIN exception_notice_map USING (exception_id) WHERE complete IS FALSE AND notice_id = 3; 20

21 6. 問題解決演習 (1) =# CREATE INDEX active_exceptions ON exception(complete) WHERE complete IS false; =# EXPLAIN ANALYZE SELECT exception_id FROM exception -# JOIN exception_notice_map USING (exception_id) -# WHERE complete IS FALSE AND notice_id = 3; QUERY PLAN =# EXPLAIN ANALYZE SELECT exception_id FROM exception -# JOIN exception_notice_map USING (exception_id) -# WHERE complete IS FALSE AND notice_id = 3; Hash Join (cost= rows=5 width=4) (actual time= rows=251 loops=1) Hash Cond: (exception_notice_map.exception_id = exception.exception_id) -> Seq Scan on exception_notice_map (cost= rows=24623 width=4) (actual time= Index Scan using rows=24800 active_exceptions loops=1) on exception Filter: (notice_id = 3) -> Hash (cost= rows=200 width=4) (actual time= rows=251 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 6kB -> Index Scan using active_exceptions on exception (cost= rows=200 w idth=4) (actual time= rows=251 loops=1) インデックスを使ってくれた Index Cond: (complete = false) Total runtime: ms (9 rows) INDEX 作成前 Total runtime: ms 21

22 例 1) のまとめ EXPLAIN ANALYZE を活用しよう! 22

23 6. 問題解決演習 (2) images site host host_id [INT] host_name [CHAR(20)] images_id [INT] site_id [INT] host_id [INT] image_name [TEXT] site_id name [INT] [CHAR(20)] SELECT h.host_name,s.name,i.image_name FROM images i JOIN host h USING (host_id) JOIN site s USING (site_id) WHERE images_id > 2212; 23

24 6. 問題解決演習 (2) =#explain analyze SELECT h.host_name,s.name,i.image_name FROM images i -# JOIN host h USING (host_id) JOIN site s USING (site_id) -# WHERE images_id > 2212; Hash Join (cost= rows=788 width=70) (actual time= rows=788 loops=1) Hash Cond: (h.host_id = i.host_id) -> Seq Scan on host h (cost= rows= width=4) (actual time= rows= loops=1) -> Hash (cost= ミリ秒に rows= width=74) 行抽出 (actual time= rows=788 loops=1) host Buckets: 1024 Batches: 1 Memory Usage: 75kB -> Hash Join (cost= rows=788 width=74) (actual time= rows=788 loops=1) host_id Hash Cond: (s.site_id = i.site_id) host_name -> Seq Scan on site s (cost= rows=3000 width=37) (actual time= rows=3000 loops=1) (actual -> time= Hash (cost= rows=788 rows=3000 width=41) loops=1) (actual time= rows=788 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 50kB -> Index Scan using 1 ミリ秒で images_pkey on images 行抽出 i site (cost= rows=788 width=41) (actual time= rows=788 loops=1) site_id Index Cond: (images_id > 2212) Total runtime: ms name 24 host 表の Seq Scan 時間が site と比べて効率が悪い (actual time= rows= loops=1) [INT] [CHAR(20)] [INT] [CHAR(20)]

25 6. 問題解決演習 (2) host host_id host_name [INT] [CHAR(20)] 1 行のサイズは? INT 4byte + CHAR 20byte + Tupleheader 23+αbyte= 約 50bytes ブロックヘッダは? 23bytes 1 ブロックのサイズは? 8192bytes 1 ブロックに入る最大行数 ( )/50 = 約 163 行 25

26 6. 問題解決演習 (2) =#explain analyze SELECT h.host_name,s.name,i.image_name FROM images i -# JOIN host h USING (host_id) JOIN site s USING (site_id) -# WHERE images_id > 2212; Hash Join (cost= rows=788 width=70) (actual time= rows=788 loops=1) Hash Cond: (h.host_id = i.host_id) -> Seq Scan on host h (cost= rows= width=4) (actual time= rows= loops=1) -> Hash (cost= rows=788 width=74) (actual time= rows=788 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 75kB -> Hash Join (cost= rows=788 width=74) (actual time= rows=788 loops=1) Hash Cond: (s.site_id = i.site_id) -> Seq Scan on site s (cost= rows=3000 width=37) (actual time= rows=3000 loops=1) -> Hash (cost= rows=788 width=41) (actual time= rows=788 loops=1) 1Buckets: ブロックに 1024 Batches: 10 行しか格納できていない 1 Memory Usage: 50kB -> Index Scan using images_pkey on images i (cost= rows=788 width=41) (actual time= rows=788 loops=1) Index Cond: (images_id > 2212) Total runtime: ms 削除フラグが立った行が多数あるのではないか 26 host_id host_name host [INT] [CHAR(20)] Seq Scan on host h (cost= rows= width=4)

27 6. 問題解決演習 (2) =#vacuum full host; =#explain analyze SELECT h.host_name,s.name,i.image_name FROM images i -# JOIN host h USING (host_id) JOIN site s USING (site_id) -# WHERE images_id > 2212; Hash Join (cost= rows=788 width=70) (actual time= rows=788 loops=1) Hash Cond: (h.host_id = i.host_id) -> Seq Scan on host h (cost= rows= width=4) (actual time= rows= loops=1) -> Hash (cost= rows=788 width=74) (actual time= rows=788 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 大幅に改善 75kB -> Hash Join (cost= rows=788 width=74) (actual time= rows=788 loops=1) Hash Cond: (s.site_id = i.site_id) -> Seq Scan on site s (cost= rows=3000 width=37) (actual time= rows=3000 loops=1) 対処後 -> Hash (actual (cost= time= rows=788 rows= width=41) loops=1) (actual time= rows=788 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 50kB -> Index Scan using images_pkey on images i (cost= VACUUM rows=788 前 Total width=41) runtime: ms (actual time= rows=788 loops=1) Index Cond: VACUUM (images_id 後 Total > 2212) runtime: ms Total runtime: ms 27 1 行あたりにかかる時間が対処前 (actual time= rows= loops=1)

28 6. 問題解決演習 (2) まとめ VACUUM FULL がいらない設計運用を EXPLAIN を見ればメンテナンスの必要性も分かる 28

29 まとめまとめどのようなアクセス方法が適切かどのような結合方法が適切か統計情報を元に実行計画を作成する事がプランナの役目どのような選択が行なわれたかを EXPLAIN コマンドで確認できる EXPLAIN には ANALYZE をつけるインデントの下の方から時間がかかっているものを見つける対処例 ) -INDEX を作成する -VACUUM FULL を実行する 29

30 ご静聴ありがとうございました参考資料 Explaining Explain ~ PostgreSQL の実行計画を読む ~ 内部を知って業務に活かす PostgreSQL 研究所第 4 回 Robert Haas blog 問合せ最適化インサイド象と戯れスライドの画像 30

ShikumiBunkakai_2011_10_29

ShikumiBunkakai_2011_10_29 Explaining Explain 第3回第21回しくみ分科会+アプリケーション分科会勉強会 2011年10月29日 PostgreSQLのしくみ分科会田中健一朗 1 本日のメニュー ExplainingExplainの第3回目味付け 9.1対応項目ごとにTips 2 本日の勉強会の目的 Explain Analyzeを使った問題箇所の見つけ方と対処方法を理解してもらうアジェンダ

PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日 株式会社アシスト 田中健一朗

PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗