PostgreSQLにおけるEXPLAIN ANALYZE活用ガイド:実行計画の読み解きとスロークエリの根本チューニング

Database tutorial - IT technology blog
Database tutorial - IT technology blog

障害現場:たった1つの重いクエリでデータベースが完全停止

月曜日の朝9時、オンコール用のSlackチャンネルにアラートが鳴り響きます。注文管理画面のローディングが回り続け、レスポンスが返るまでに10秒以上かかる事態に。焦ったユーザーがF5キーを連打した結果、データベースクラスタのCPU使用率は98%に達し、数百ものコネクションが待機状態に陥りました。

本質的にデータベースの動作は、数百万冊の本を所蔵する図書館によく似ています。「先月における特定VIP顧客の成立済み注文をすべて取得せよ」というクエリを実行した際、Postgresはその探索手順を決めなければなりません。整備された目録(インデックス)があれば、司書はわずか2秒で目的の本棚に直行できます。しかし目録がなければ、数百万冊の本を1ページずつめくって探すしかありません。その結果、背後に並ぶ利用者の大行列は一瞬で足止めを食らうことになります。

データベースの過負荷に直面したとき、多くのチームが取りがちな応急処置は、サービスの再起動やサーバースペックの増強です。しかし、RAMを増やしたりCPUコアを追加したりしても、問題の発生を翌週に先送りするだけにすぎません。根本的な原因は、非効率なまま実行されているSQLクエリそのものにあるのです。

ブラックボックスの解剖:Postgresはどのようにクエリを実行しているのか?

PostgreSQLは「Query Planner」を用いて、各クエリに対して最も効率的な実行経路を選択します。このプランナは蓄積された統計情報(statistics)に基づいて推定コストを計算します。しかし、机上の計算とディスク上の実データの間には、時に大きな乖離が生じることがあります。

データベース内部で実際に何が起きているのかを正確に把握するために、エンジニアは主に2つのコマンドを使用します。

  • EXPLAIN: 理論上の予測値のみを出力します。クエリを実際には実行せず、既存のメタデータに基づいてコストを推計します。
  • EXPLAIN ANALYZE: 本番データ上でクエリを実際に実行します。各工程の正確な所要時間を計測し、処理された実際の行数をカウントします。

本記事の検証用に100万件の注文テストデータを準備するにあたり、モックデータを出力した上で toolcraft.app/ja/tools/data/csv-to-json のCSV→JSON変換ツールを活用しました。ブラウザのクライアントサイドで処理されるため、社内データが外部へ漏洩する心配もありません。以下が問題を引き起こしていた注文検索クエリです。

EXPLAIN ANALYZE
SELECT id, customer_id, total_amount, created_at 
FROM orders 
WHERE customer_id = 98452 AND status = 'COMPLETED';

Postgresから返された実行結果がこちらです。

Seq Scan on orders  (cost=0.00..21458.00 rows=3 width=40) (actual time=48.215..95.120 rows=2 loops=1)
  Filter: ((customer_id = 98452) AND ((status)::text = 'COMPLETED'::text))
  Rows Removed by Filter: 999998
Planning Time: 0.124 ms
Execution Time: 95.160 ms

注目すべき重要な指標は以下の通りです。

  • Seq Scan (Sequential Scan): テーブルのフルスキャン(全件走査)です。データベースはディスク上の1,000,000行すべてを走査し、条件に合致するわずか2行を取り出すために999,998行を破棄しています(Rows Removed by Filter: 999998)。
  • Cost (0.00..21458.00): プランナによる推定コストの単位です。「0.00」は最初の1行を取得するまでのスタートアップコスト、「21458.00」はこのスキャンノード全体を完了するまでの総推定コストを表します。
  • Actual time (48.215..95.120 ms): 実際の実行所要時間です。単一のクエリだけで95.16msも消費しています。仮に200件のリクエストが同時に押し寄せれば、コネクションプールは瞬時に枯渇します。

よくある対症療法とその弊害

スロークエリに直面した際、開発チームが焦って短期的な解決策に走ってしまうケースがよく見られます。

1. サーバーのスペック増強(Scale Up)

RAMを16GBから32GBに倍増させれば、サーバーは数週間持ちこたえるかもしれません。しかしテーブルが1,000万行に達した時、I/Oボトルネックは必ず再発します。月額数百ドルのクラウド費用を追加で払い続けることになり、根本的な解決には至りません。

2. 手当たり次第のインデックス作成

WHERE句に含まれるすべてのカラムに個別のインデックスを作成するのは危険な悪習です。インデックスは読み取りを高速化する一方で、書き込み(INSERT、UPDATE、DELETE)のパフォーマンスを大きく低下させます。新たな行が追加・更新されるたびに、Postgresは該当するすべてのB-Treeインデックスを更新する必要があり、メモリを消費するだけでなくストレージ容量も肥大化します。

3. 直感や勘に頼ったSQLの書き直し

推測に基づいてWHERE句の条件順序を入れ替えたり、クエリを複数のサブクエリに分割したりしても、大抵は効果がありません。Postgresのオプティマイザは十分に賢く、論理的な順序を自動で最適化します。当てずっぽうな修正は、コードを複雑化させるだけです。

実測データに基づく4ステップの最適化手順

勘に頼るのではなく、Execution Planから得られる数値データを丹念に分析し、根本的に対処していきましょう。

ステップ1:BUFFERSオプションを有効にしてディスクI/Oを可視化する

クエリにBUFFERSオプションを追加することで、データがRAM上の共有バッファから読み取られたのか(shared hit)、それとも物理ディスクから読み出されたのか(read)を明確に把握できます。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount, created_at 
FROM orders 
WHERE customer_id = 98452 AND status = 'COMPLETED';
Seq Scan on orders  (cost=0.00..21458.00 rows=3 width=40) (actual time=48.215..95.120 rows=2 loops=1)
  Filter: ((customer_id = 98452) AND ((status)::text = 'COMPLETED'::text))
  Rows Removed by Filter: 999998
  Buffers: shared hit=124 read=8802
Planning Time: 0.145 ms
Execution Time: 95.210 ms

shared read=8802という数値は、約68.7MB分(8,802ブロック × 8KB)のデータが物理ディスクから直接読み出されたことを意味します。これこそがIOPSを高騰させ、クエリを停滞させていた元凶です。

ステップ2:カーディナリティ(選択度)を考慮した複合インデックスの作成

抽出条件にはcustomer_idとstatusの双方が含まれています。customer_idはカーディナリティが非常に高く(数万規模の顧客)、一方でstatusは数種類の固定値しか持ちません。B-Treeインデックスでは、選択度が高い(値のばらつきが大きい)カラムを先頭に配置するのが鉄則です。

-- 検索範囲を最速で絞り込むため、customer_idをインデックスの先頭に配置
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

ステップ3:テーブルの統計情報を再収集・更新する

インデックスを作成した後や、大量のデータ更新が発生した後は、手動でPostgresに統計情報の更新を促します。

ANALYZE orders;

ステップ4:最適化後の結果を検証・比較する

再度実行計画を取得し、Postgresの新たなアクセスパスを確認します。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total_amount, created_at 
FROM orders 
WHERE customer_id = 98452 AND status = 'COMPLETED';

出力された新たなExecution Planがこちらです。

Index Scan using idx_orders_customer_status on orders  (cost=0.42..8.45 rows=2 width=40) (actual time=0.038..0.042 rows=2 loops=1)
  Index Cond: ((customer_id = 98452) AND ((status)::text = 'COMPLETED'::text))
  Buffers: shared hit=4
Planning Time: 0.115 ms
Execution Time: 0.065 ms

数値を見れば、改善の成果は一目瞭然です。

  • Seq ScanからIndex Scanへの転換: Postgresはインデックスツリーを直接参照し、無駄な999,998行を走査することなく、目的の2行だけをピンポイントで取得しています。
  • バッファ参照はわずか4 shared hit: 必要なデータはすべてRAMキャッシュ内(約32KB)に収まっており、物理ディスクの読み込み(read=0)は一切発生していません。
  • 実行時間の大幅短縮: 95.160 msから0.065 msへと短縮され、約1,460倍の高速化を達成しました。

EXPLAIN ANALYZEを活用することで、推測ではなく定量的なデータに基づいた的確なチューニング判断が可能になります。真のボトルネックを見極めて適切なインデックスを設計することは、インフラコストの削減だけでなく、将来的なデータ急増時にも耐えうる堅牢なシステム運用につながります。

Share: