![SQLチューニング 都市伝説② IN句が使用可能なら、ORよりIN句を。 testdb=# explain select uid from master where id = '1' or uid = '5' or uid = '10'; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on master (cost=7.64..13.68 rows=3 width=4) Recheck Cond: ((id = 1) OR (id = 5) OR (id = 10)) -> BitmapOr (cost=7.64..7.64 rows=3 width=0) -> Bitmap Index Scan on master_pkey (cost=0.00..2.54 rows=1 width=0) Index Cond: (id = 1) -> Bitmap Index Scan on master_pkey (cost=0.00..2.54 rows=1 width=0) Index Cond: (id = 5) -> Bitmap Index Scan on master_pkey (cost=0.00..2.54 rows=1 width=0) Index Cond: (id = 10) testdb=# explain select uid from master where id in ('1' , '5' , '10'); QUERY PLAN ------------------------------------------------------------------------------- Bitmap Heap Scan on master (cost=7.06..13.09 rows=3 width=4) Recheck Cond: (uid = ANY ('{1,5,10}'::integer[])) -> Bitmap Index Scan on master_pkey (cost=0.00..7.06 rows=3 width=0) Index Cond: (id = ANY ('{1,5,10}'::integer[]))](https://image.slidesharecdn.com/20090107postgresqlsql-1231404127643159-2/85/20090107-Postgre-Sql-Sql-26-320.jpg)
20090107 Postgre Sqlチューニング（Sql編）
- 1. PostgreSQL チューニング ～SQL編～ id:matsuou1
- 2. 目標 SQLの実行計画が読める。 遅いSQLを特定できる。 簡単なSQLのチューニングができる。
- 3. 実行計画とは？ 実行計画は、PostgreSQLのオプティマイザが最適だと判断したクエリを実行する個々のステップを示したもの。 Explainを使用することで、参照可能 testdb=# explain select count(id) from master where valid = true; QUERY PLAN ---------------------------------------------------------------------------- Aggregate (cost=217107.05..217107.06 rows=1 width=0) -> Seq Scan on master (cost=0.00..213742.88 rows=1345668 width=0) Filter: valid
- 4. ExplainとExplain analyzeの違い Explain オプティマイザが決定した実行計画を表示 Explain analyze 実際に実行した計画を表示 testdb=# explain analyze select count(id) from master where valid = true; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Aggregate (cost=217107.05..217107.06 rows=1 width=0) (actual time=2956.265..2956.265 rows=1 loops=1) -> Seq Scan on master (cost=0.00..213742.88 rows=1345668 width=0) (actual time=0.035..2661.190 rows=1374280 loops=1) Filter: valid Total runtime: 2956.301 ms
- 5. 実行計画の読み方① Cost=XXXXX…YYYYYY XXXXX：スタートアップコスト 一番最初のレコードを返却するのに掛かるコスト YYYYY：トータルコスト 一番最後のレコードを返却するのに掛かるコスト Costと実行時間は、相関関係はない。 Costとは、オプティマイザが複数のプランの中からある特定のプランを選ぶための指標 Aggregate (cost=217107.05..217107.06 rows=1 width=0) -> Seq Scan on master (cost=0.00..213742.88 rows=1345668 width=0) Filter: valid
- 6. 実行計画の読み方② rows=XXXXX 返却されるレコード数 実際の値と大きな乖離がある場合は、Analyzeが必要 width=XXXX 返却されるレコードの長さ あんまり重要ではない Aggregate (cost=217107.05..217107.06 rows=1 width=0) -> Seq Scan on master (cost=0.00..213742.88 rows=1345668 width=0) Filter: valid
- 7. 実行計画の読み方③ 下から順番に実行される。 COSTが大きい箇所は遅い原因の可能性が。 testdb=# explain select * from place_master p , member m testdb=# where p.pid = 5 and p.pid = m.pid order by m.id limit 10; QUERY PLAN ----------------------------------------------------------------------------------------- Limit (cost=517.11..517.13 rows=10 width=638) -> Sort (cost=517.11..517.74 rows=252 width=638) Sort Key: f.fid -> Nested Loop (cost=0.00..511.66 rows=252 width=638) -> Index Scan using place_master_pid on place_master p (cost=0.00..4.41 rows=1 width=599) Index Cond: (pid = 5) -> Index Scan using member_pid on member m (cost=0.00..504.73 rows=252 width=39) Index Cond: (m.pid = 5) ① ② ③ ④ ⑤
- 8. 実行計画の読み方④ - 1 データ取得 なし リレーション内のビットマップをメモリ内で作成し、スキャンする Bitmap Index Scan なし インデックスを使用してスキャン 大きいテーブルでは SeqScan より早い Index Scan なし 最初から最後のページまでスキャン 条件によらず全ての行をチェック 大きいテーブルだと遅い Seq Scan 開始コスト 説明 演算子
- 9. 実行計画の読み方④ - 2 テーブル結合 あり ２つのデータセットを結合 データセットはあらかじめソートが必要 Merge Join あり 一方の入力からハッシュテーブルを作成し、二つの入力を比較する INNER JOIN と OUTER JOIN と同時に使用される Hash Join なし 2 つのテーブルを結合 INNER JOIN と LEFT OUTER JOIN 外部テーブルをスキャンし、内部テーブルにマッチするものを取得 インデックスが無い場合は遅いかも Nested Loop 開始コスト 説明 演算子
- 10. 実行計画の読み方④ - 3 その他 あり Count 、 sum 、 max 等の集合関数 Aggregate あり Row は指定した行数 Offset を追加すると、少しだけ開始コストが必要になる Limit あり 取得したデータのソート処理 Sort 開始コスト 説明 演算子
- 11. 遅いSQLを特定するには？ 実行中のSQLから特定する ユーザ画面が遅い場合に。 ログファイルから特定する 夜間バッチ処理が時間内に終わらない場合に。
- 12. 遅いSQLの特定方法① 実行中のSQLを表示 SELECT pid, start, now() - start AS lap, current_query FROM (SELECT backendid, pg_stat_get_backend_pid(S.backendid) AS pid, pg_stat_get_backend_activity_start(S.backendid) AS start, pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S WHERE current_query <> '<IDLE>' ORDER BY lap DESC; pid | start | lap | current_query -------+------------------------+------------------+---------------------------------------- 16867 | 2008-12-24 16:49:54.00 | -00:00:02.000238 | SELECT * FROM master WHERE id = $1 AND valid = true LIMIT 1
- 13. 遅いSQLの特定方法② ログファイルから特定する postgres.conf の log_min_duration_statement にロギングする実行時間の閾値を指定（ミリ秒） 適当に閾値を指定すると、ロギング対象の SQL が増えて、パフォーマンスに影響が出る可能性があるので注意 2008-09-01 00:00:54 JST LOG: duration: 11098.465 ms statement: SELECT count(id) FROM master
- 14. SQLチューニングのポイント 直積結合の回避 インデックスを使う（全表走査の原則禁止） JOINのテーブル数制限 ソート処理の削減
- 15. 直積結合の回避 直積結合は、結合対象の2つのテーブルの全レコードの組み合わせを戻す処理 SQLが複雑で、結合条件や絞込み条件に漏れがあると直積結合が選択されるかも tableA = 1000 件、 tableB = 2 万件 の直積結合 1,000 * 20,000 = 20,000,000 2000 万件処理されてしまう が、結果が正しいと気付かない事も。 データ量が増加すれば、致命的な性能劣化に
- 16. 直積結合の回避 確認方法 実行計画の結合している箇所のROWの値が想定件数内であるかどうかを確認する 対策 結合条件が漏れてないか、 SQL に無理がないか要確認
- 17. インデックスを使う 索引を使用した検索では、検索対象となる表の規模によらず、安定した検索性能が得られる。 索引が存在しないか、使用できない場合は全件取り出して条件にマッチする行を返却する。 インデックスを使用しないと、 テーブルサイズに比例して性能が劣化
- 18. インデックスを使う 確認方法 実行計画中に Seq Scan が無いことを確認する 対策 左辺、右辺の型を一致させる 左辺に関数を使用しない 左辺に計算式を使用しない NOT EQUAL を使用しない 中間一致検索をしようしない WHERE SUBSTR(COL1, 1, 1) = ‘A’ ( 左辺に関数を使用 ) WHERE COL1 * 1.05 > 5000 ( 左辺に計算式を使用 ) WHERE COL1 != 10000 (NOT EQUAL を使用 ) WHERE COL1 LIKE ‘%hoge%’ ( 中間一致検索を使用 )
- 19. テーブルの結合数制限 オプティマイザは、考えられる表の結合順序を評価し、最適な結合順序を選ぼうとする。 結合表数が７を超えると、結合順序を評価する組み合わせが急激に増加し、 SQL の解析に時間が掛かる。 結合順序の組み合わせは、表数の階乗。 8! = 40,320 7! = 5,040 6! = 720 5! = 120 4! = 24 3! = 6 最適な実行計画を立てるのに時間がかかる
- 20. JOINのテーブル数制限 確認方法 結合しているテーブル数を数える。 対策 １ SQL で JOIN する表数は最大で ６ までに抑える
- 21. ソート処理の削減 ORDER BY、GROUP BY、DISTINCT、UNIONなど多くの処理でソート処理が実行される。 データ件数が増えてくると、 ソート処理も高コストになる
- 22. ソート処理の削減 確認方法 実行計画中に不要なソートがないか確認する。 対策 可能であれば、 UNION の代わりに UNION ALL を使用する 不要な GROUP BY 、 ORDER BY を削除する GROUP BY ＋ HAVING を使用する際は、 事前に WHERE 句で件数を絞る
- 23. 空気が読める開発者になるために SQLを書いたら、実行計画を確認する習慣を付けましょう。 データ量の見積もりは必ず行いましょう。 パフォーマンスの確認は、本番環境と同じデータで行いましょう。 定期的にパフォーマンスの確認を行いましょう。
- 25. SQLチューニング 都市伝説① IS NULL を使うとインデックスが使用されない NULL 値は Btree のインデックスでは格納されないため、 IS NULL 、 IS NOT NULL の検索の場合は、インデックスを使用できない。 ８．３からインデックス使用可能に！ testdb=# explain select count(id) from master where mail is null; QUERY PLAN ------------------------------------------------------------------------------------------ Aggregate (cost=4.90..4.91 rows=1 width=4) -> Index Scan using master_mail on master (cost=0.00..4.90 rows=1 width=4) Index Cond: (mail IS NULL)
- 26. SQLチューニング 都市伝説② IN句が使用可能なら、ORよりIN句を。 testdb=# explain select uid from master where id = '1' or uid = '5' or uid = '10'; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on master (cost=7.64..13.68 rows=3 width=4) Recheck Cond: ((id = 1) OR (id = 5) OR (id = 10)) -> BitmapOr (cost=7.64..7.64 rows=3 width=0) -> Bitmap Index Scan on master_pkey (cost=0.00..2.54 rows=1 width=0) Index Cond: (id = 1) -> Bitmap Index Scan on master_pkey (cost=0.00..2.54 rows=1 width=0) Index Cond: (id = 5) -> Bitmap Index Scan on master_pkey (cost=0.00..2.54 rows=1 width=0) Index Cond: (id = 10) testdb=# explain select uid from master where id in ('1' , '5' , '10'); QUERY PLAN ------------------------------------------------------------------------------- Bitmap Heap Scan on master (cost=7.06..13.09 rows=3 width=4) Recheck Cond: (uid = ANY ('{1,5,10}'::integer[])) -> Bitmap Index Scan on master_pkey (cost=0.00..7.06 rows=3 width=0) Index Cond: (id = ANY ('{1,5,10}'::integer[]))