【データベース】統計情報と物理順序

Transcript

1. 【データベース】 統計情報と物理順序
2. 目次 1. はじめに 2. 統計情報とは何か？ 3. 物理順序と論理順序 4. 物理順序と論理順序のギャップが生む罠 5. 検証：カーディナリティ × 物理順序の違いによる実行速度の変化 6. どう直す？統計情報と物理順序のズレ対策 7. 【おまけ】Oracleで実行計画を固定することの落とし穴 8. まとめ
3. 1. 　はじめに • 統計情報は正しいのに、なぜかSQLが遅い… • 実行計画も問題なさそうなのに、処理が重い… ◦ →　その原因、物理順序の影響かもしれません • 本LTでは、統計情報と物理順序の違いと 　そのギャップが引き起こすパフォーマンス問題を紹介します 本資料は、統計情報が正しくてもSQLが遅い場合の“もう一つの原因 ”とし て、物理順序の重要性を再発見するものである。
4. 2. 　統計情報とは何か？ • 実行計画(※1)の最適化に使われる、データの概要情報 ◦ 例：件数（カーディナリティ）、値の分布、NULLの割合 など • データベースは統計情報をもとに 　「どのインデックスを使うか」「結合順序はどうするか」 を判断する • 情報が古い・不正確だと、非効率な実行計画が選ばれる (※1)実行計画 DBMSがクエリを最適に実行するための手順書 ・どのインデックスを使用するか ・どのテーブルをどの順番で結合するか ・テーブルスキャンorインデックススキャン 　等
5. 2. 　統計情報とは何か？ SELECT * FROM 購入明細 WHERE CUSTOMER_ID = ‘C’ パーサ （構文解析） リライタ （書換） プランナ （オプティマイザ） エグゼ キュータ 実行結果 きゃ 実行 計画 統計 情報 適切な実行計画がない場合 統計情報を使用し 実行計画を作成 テーブル 実行計画に従い データアクセス Oracle 実行計画取得 PostgreSQL 実行計画作成 ※例外有 クエリ実行の流れ
6. 3. 　物理順序と論理順序 • 物理順序 ◦ テーブルにデータが実際に格納されている順序（※ヒープ構造） ◦ データの並び順に意味はなく、 INSERTやUPDATEにより順序は崩れやすい ◦ 統計情報は各カラムごとに収集され、実行計画の判断材料となる • 論理順序 ◦ インデックスが管理する、検索用の並び順 ◦ インデックスは、特定のカラムやカラムの組み合わせごとに作成され、 ◦ 値の昇順／降順などの論理的な並び順でアクセスできるように構造化されている • 物理順序と論理順序は必ずしも一致しない ◦ 追加、更新、削除で差異が生じることがある • 順序が一致していると、 I/O効率が上がり、処理が高速化される ・※ヒープ構造：テーブルの実データが格納されている場所 （行の並び順に意味はなく、順序は自動で整わない）
7. 3. 　物理順序と論理順序 イメージ • 論理順（インデックス順） ◦ id = 1 → 2 → 3 → 4 → 5 • 物理順（実格納順）： ◦ id = 3 → 1 → 5 → 2 → 4 → 同じidでも、物理配置はバラバラなことがある 補足：PostgreSQLのヒープ構造では、 データの物理順序は自動で整わない（ CLUSTERなどが必要）
8. 4. 　物理順序と論理順序のギャップが生む罠 • 補足 ◦ Bitmap Index Scan：ヒット件数が多いときに選ばれ、ランダムI/Oを抑える効果がある ◦ TID：Tuple ID。 ヒープ上の行の物理的な位置情報(ブロック番号＋行番号 )を表す。 スキャン方式 概要 ヒープへのアクセス Seq Scan テーブル全体を順に読む ページを最初から最後まで順番に （シーケンシャルI/O） Index Scan インデックスで一致行を探す TIDを使ってヒープへ個別アクセス （ランダムI/O傾向） Bitmap Index Scan (Bitmap Heap Scan) インデックスを ビットマップ化して一括取得 TIDをまとめてソートしてから 一括でヒープアクセス • 説明の前に。。。
9. 4. 　物理順序と論理順序のギャップが生む罠 物理順序と論理順序（インデックス）の影響を受けるケース • WHERE id = 1 のように少数件ヒット する場合は、Index Scanが最も効率的 • しかし、該当値が多数ヒット（重複多等） する場合 ◦ 物理順序が整っていれば ▪ Index Scanでも高速 ◦ 物理順序がバラバラなら ▪ Bitmap Index ScanやSeq Scanが選ばれることも • プランナーは統計情報（pg_stats）をもとに、 最も効率的なスキャン方法を選択
10. 4. 　物理順序と論理順序のギャップが生む罠 シーケンシャルスキャン（ Seq Scan）のイメージ ・ブロック1 　行1 → user_id = 1 　行2 → user_id = 2 　行3 → user_id = 3 ・ブロック2 　行1 → user_id = 4 　行2 → user_id = 5 　行3 → user_id = 6 ・ブロック3 　行1 → user_id = 7 　行2 → user_id = 8 　行3 → user_id = 9 テーブル（ヒープ） 読む （条件に合わない） 例：WHERE user_id > 4 読む （行2,3が条件に合う） 読む （条件に合う） 順 番 に 読 む • 条件に関係なく、 最初から全ページを順に読む。 • 高速だけど無駄も多い。 補足 • ブロック ◦ PostgreSQLの読み取り単位。 複数の行を含む。 • ヒープ ◦ テーブル本体のデータ格納構造。 順番は保証されない。
11. 4. 　物理順序と論理順序のギャップが生む罠 インデックススキャン (連続)のイメージ (物理順序が整っている場合 ) ・ブロック1 　行1 → user_id = 1 　行2 → user_id = 2 　行3 → user_id = 3 ・ブロック2 　行1 → user_id = 4 　行2 → user_id = 5 　行3 → user_id = 6 ・ブロック3 　行1 → user_id = 7 　行2 → user_id = 8 　行3 → user_id = 9 テーブル（ヒープ） 例：WHERE user_id > 4 ・Index(B-Tree) user_id (TID) → 5 (2, 2) → 6 (2, 3) → 7 (3, 1) → 8 (3, 2) → 9 (3, 3) 各TIDが 指すブ ロックと 行を順番 に参照 • 処理の流れ： ◦ インデックスを上から順に走査し、 user_id > 4 に合致するエントリを探す ◦ 該当するTID（例：TID(2,2), (2,3), ...）だけを集める ◦ そのTID順（≒物理順）でヒープにアクセス ◦ → TID(1,1)はインデックスレベルで 除外されているのでアクセスしない • 特徴 ◦ 物理順序が整っている場合、 Index Scan でもヒープアクセスは TID順に近くなり、 I/O効率が向上します。 ◦ Indexによって条件外の TIDは除外されるため、 TID(1,1)などの不必要な行にはアクセスしません。 順 番 に 読 む 読 ま な い
12. 4. 　物理順序と論理順序のギャップが生む罠 インデックススキャン (ランダムIO)のイメージ (物理順序が崩れている場合 ) ・ブロック1 　行1 → user_id = 6 　行2 → user_id = 3 　行3 → user_id = 7 ・ブロック2 　行1 → user_id = 1 　行2 → user_id = 8 　行3 → user_id = 4 ・ブロック3 　行1 → user_id = 2 　行2 → user_id = 9 　行3 → user_id = 5 テーブル（ヒープ） 例：WHERE user_id > 4 ・Index(B-Tree) user_id (TID) → 5 (3, 3) → 6 (1, 1) → 7 (1, 3) → 8 (2, 2) → 9 (3, 2) 各TIDが 指すブ ロックと 行を個別 に参照 HIT HIT HIT HIT HIT • Indexが保持するTID(ブロック番号, 行番号)をもとに, ヒープ（テーブル本体）の対応する位置から 行を個別に読み出す。 • ヒット件数が多く物理順序が崩れている と ランダムI/Oが多発して遅くなることがある。
13. BitmapIndexScan 4. 　物理順序と論理順序のギャップが生む罠 BitmapIndexScan (BitmapHeapScan) Exact(TID)のイメージ 例：WHERE user_id > 4 BitmapHeapScan ・ブロック1 　行1 → user_id = 8 　行2 → user_id = 3 　行3 → user_id = 7 ・ブロック2 　行1 → user_id = 1 　行2 → user_id = 6 　行3 → user_id = 4 ・ブロック3 　行1 → user_id = 2 　行2 → user_id = 9 　行3 → user_id = 5 テーブル (ヒープ) HIT HIT HIT 順番 に読む user_id TID bit 1 2,1 0 2 3,1 0 3 1,2 0 4 2,3 0 5 3,3 1 6 2,2 1 7 1,3 1 8 1,1 1 9 3,2 1 ①Bitmap生成 ②Bitmap並び替え(TID) user_id TID bit 8 1,1 1 7 1,3 1 6 2,2 1 9 3,2 1 5 3,3 1 ③Heap読込 user_id TID bit 8 1,1 1 7 1,3 1 6 2,2 1 9 3,2 1 5 3,3 1
14. 4. 　物理順序と論理順序のギャップが生む罠 Exact（TIDベース） 　条件にマッチする行の TID（＝ブロック番号と行番号）単位 でビットマップが作られている 　→ 対象の 行位置が正確に特定されており、ピンポイントでヒープを読み出す 　→ 再チェックは不要で、無駄なアクセスが少なく、高精度かつ効率的なスキャンが可能 • ① Index（B-Tree）を走査して、TID（ブロック番号＋行番号）単位でビットマップを作成 → 条件に合致した行に対応する TID に「1」を立てて管理する → 複数インデックス条件に対してビット演算（ AND / OR） 　で効率よく合成できるのが大きな特長 • ② ビットが立った TID を TID順に並び替え、アクセス順を最適化 → ランダムI/Oを減らし、ヒープ読み込みをより効率的に • ③ 並び替えた TID に従い、該当する行だけをピンポイントで読み出す → 行番号が明確なため、再チェックは不要で、無駄な I/Oがほとんどない
15. BitmapIndexScan 4. 　物理順序と論理順序のギャップが生む罠 BitmapIndexScan (BitmapHeapScan) Lossy(Block)のイメージ 例：WHERE user_id > 6 BitmapHeapScan ・Block 1(対象) 　行1 → user_id = 8 　行2 → user_id = 3 　行3 → user_id = 7 ・Block 2(対象外) 　行1 → user_id = 1 　行2 → user_id = 6 　行3 → user_id = 4 ・Block 3(対象) 　行1 → user_id = 2 　行2 → user_id = 9 　行3 → user_id = 5 テーブル (ヒープ) HIT HIT Block bit user_id(TID) 1 1 7(1,3),8(1,1) 2 0 (該当なし) 3 1 9(3,2) ①Bitmap生成 ②Bitmap並び替え(Block) ③Heap対象Block読込 Block bit user_id(TID) 1 1 7(1,3),8(1,1) 2 0 (読込不要) 3 1 9(3,2) Block bit user_id(TID) 1 1 7(1,3),8(1,1) 2 0 (読込不要) 3 1 9(3,2) HIT MISS MISS MISS user_id TID 1 2,1 2 3,1 3 1,2 4 2,3 5 3,3 6 2,2 7 1,3 8 1,1 9 3,2 ⓪データ一覧
16. 4. 　物理順序と論理順序のギャップが生む罠 Lossy（Blockベース） 　条件にマッチする行の ブロック番号単位 でビットマップが作られている 　→ 行番号までは保持されず、ブロック全体を一度に読み出す 　→ そのため、再チェック（ Recheck Cond）が必要となるが、大量ヒット時でもメモリを節約できる • ① Bitmap作成（Index Scan） 条件にマッチする ブロック単位 でビットを立てる 行単位（TID）ではなく、「このブロックに該当行がある」で 1 を立てる 大量ヒット時や work_memory 制約下で有効 • ② 並び替え（Bitmap最適化） ビットが立っているブロックを ブロック番号順に並び替え ヒープアクセス時の ランダムI/Oを抑える効果 • ③ Heapアクセス（Bitmap Heap Scan） ビットが立った各ブロックをまるごと読み込む
17. 4. 　物理順序と論理順序のギャップが生む罠 ・correlation：pg_stats に格納される統計値で、値の昇順と物理順が一致している度合い（ -1〜1）0に近いほと物理順序バラバラ。 ・work_mem：クエリ実行時の一時作業メモリ。これを超えるとビットマップ構造が lossy に切り替わる スキャン方式 主な選択条件 特徴 備考（切り替わり要因） Seq Scan - 対象件数が多い（全体または大半。） - 適切なインデックスがない。 全ブロックを順に走査。 ランダムI/Oがない。 統計情報の選択率が高いと選ばれやすい。 Index Scan （物理順序整） - 対象件数が少ない。 - インデックスの論理順 ≒ テーブルの物理順 （correlation ≈ 1） ランダムI/Oが少なく、効率的。 correlation によって選択の優先度が上がる。 Index Scan （物理順序崩れ） - 対象件数が少ない。 - 物理順がバラバラ（correlation ≈ 0） ランダムI/Oが多くなるが 件数が少なければ高速。 correlation が低いと Bitmap Scan に切り替わる可能性あり。 Bitmap Index Scan （Exact） - 対象件数が中〜多。 - work_mem に収まる件数。 - 複数インデックス条件の合成に向く。 ビットマップ上に TID を正確に保持し 無駄なく読み込み。 メモリ余裕があれば最も効率的に処理できる。 Bitmap Index Scan （Lossy） - ヒット件数が多い。 - work_mem を超える量の TID が発生。 ブロック単位でビット管理し 再チェックを要する。 work_mem を超えると Exact から自動で Lossy に切り替わる。
18. 5.　検証：カーディナリティ × 物理順序の違いによる実行速度の変化 項目 内容 目的 重複の多いデータを SELECTし、物理順序の違いで実行計画や速度が変わるか検証する。 OS Mac 実行環境 Docker DB PostgreSQL（Version 17.4） クライアント PGAdmin（Version 9.1） その他条件 ・データ件数：400万件。ID(カラム)の値が2,10,1000種類の3パターンで検証（Index作成済） ・1回目はIDカラムの物理順序バラバラ、 2回目はCLUSTERを使用し物理順序を整えて検証。 ・検証クエリは「SELECT * FROM TEST_TABLE_RANDOM WHERE ID = 1」。 ・IDカラムの物理順序と論理順序の乖離は pg_statsテーブル.correlationカラムを確認 （-1〜1 の値が設定される。0に近いほど物理順序と論理順序がバラバラ。）
19. 5.　検証：カーディナリティ × 物理順序の違いによる実行速度の変化 CLUSTER実行前（pg_stats.correlation : 0.50433224） CLUSTER実行後（pg_stats.correlation : 1） データが2種類
20. 5.　検証：カーディナリティ × 物理順序の違いによる実行速度の変化 CLUSTER実行前（pg_stats.correlation : 0.10577792） CLUSTER実行後（pg_stats.correlation : 1） データが10種類
21. 5.　検証：カーディナリティ × 物理順序の違いによる実行速度の変化 CLUSTER実行前（pg_stats.correlation : -0.0051954687） CLUSTER実行後（pg_stats.correlation : 1） データが1000種類
22. 5.　検証：カーディナリティ × 物理順序の違いによる実行速度の変化 検証結果 データの種類数 CLUSTER スキャン方法 correlation 処理時間(ms) 2 実行前 Bitmap Index Scan 0.5043 449.838 2 実行後 Index Scan 1 341.733 10 実行前 Bitmap Index Scan 0.1057 224.348 10 実行後 Index Scan 1 56.174 1000 実行前 Bitmap Index Scan -0.0051 4.486 1000 実行後 Index Scan 1 0.694
23. 5.　検証：カーディナリティ × 物理順序の違いによる実行速度の変化 • 🔍 考察 ◦ CLUSTER により物理順序が整うと、correlation が 1.0 に近づき、 Bitmap Scan → Index Scan へ切り替わった ◦ スキャン方式の変化により、処理時間が大幅に短縮された （特に10種類では約75％減） ◦ ヒット件数が少ない1000種類でも、 Index Scan に切り替わる傾向は一貫して見られた ◦ correlation は、実行計画とパフォーマンスに 強く影響することが確認できた
24. 6.　どう直す？統計情報と物理順序のズレ対策 • 統計情報が古い or 実際の物理順序とずれている場合、 誤った実行計画が選ばれる ことがある。 • 特に大量UPDATEやランダムINSERT後は、 物理順序が崩れても correlation は古いままになる • その結果、Index Scan が選ばれず パフォーマンスが低下する可能性がある • 適切なタイミングで ANALYZE や CLUSTER を実行して 統計情報と順序を整えることが重要
25. 6.　どう直す？統計情報と物理順序のズレ対策 ANALYZEとCLUSTERの大まかな違い 項目 ANALYZE CLUSTER 目的 統計情報の更新。 「カラムごとの値の分布」「 NULLの割合」「重複の度合 い」「行数の見積もり」などが含まれる 物理順序の並び替え （インデックス順） 更新対象 pg_stats の統計情報のみ テーブル全体のデータの再配置 ＋統計情報更新 実行タイミング データが変化したあと（軽量） 順序を整えたいとき （重め（テーブルロック）） correlationの影響 更新はされるが順序は変えない 順序も統計も変えるので correlationが1に近づく ロック 共有ロック 排他ロック
26. 6.　どう直す？統計情報と物理順序のズレ対策 🛠 CLUSTER のベストプラクティス（ 6つのポイント） 1. よく使うWHERE条件のカラムに対して実行する 　→ 範囲検索（>=, BETWEEN）に効果大 2. 複合インデックスなら、先頭カラムが目的のカラムか確認する 　→ CLUSTERは先頭カラム順 に並び替える 3. 他のカラムの correlationが下がる可能性を考慮 　→ CLUSTERは1つの順序にしか最適化できない 4. 更新頻度が高いテーブルは実行タイミングに注意 　→ 排他ロックが発生する ため、夜間バッチなどで実行 5. 複数順序が必要ならパーティションや別テーブルを検討 　→ 順序ごとに分けて最適化するほうが安定 6. 定期実行は不要。 correlationが下がったときに実行 　→ ANALYZEで状況を確認してから判断
27. 7.　【おまけ】Oracleで実行計画を固定することの落とし穴 • PostgreSQLやOracleでは、 統計情報や物理順序をもとに実行計画を最適化している。 • しかし、実行計画を固定してしまうと、こういった最適化が無効化され、 劣化した計画が残り続けるリスクがある。 • 特に統計情報や物理順序が大きく変化する場合、 固定された実行計画は逆効果になる可能性がある。
28. 8.　まとめ • 統計情報と物理順序、どちらも実行計画に影響する “見えない重要要素 ”。 • correlation ≒ 1 の状態では、Index Scanが有利に選ばれやすく、 処理速度も向上。 • 実際の検証でも、 CLUSTERによる物理順序の整列が大幅なパフォーマンス改善に繋がった。 • ANALYZE（統計更新）と CLUSTER（順序最適化） を適切に使い分ける ことがチューニングの鍵。 💡 最適な実行計画は、 　統計と順序の “ダブルメンテナンス ”から生まれる。
29. 参考資料 ・BitmapHeapScanのEXACTとLOSSY https://pganalyze.com/docs/explain/scan-nodes/bitmap-heap-scan?utm_sourc e=chatgpt.com