PostgreSQL クエリチューニング

PostgreSQL クエリチューニング は何をしますか？

主張の検証は CHECKS.md を参照。以下は通常の PostgreSQL に適用されます。まずバージョンを確認してください（SHOW server_version）：デフォルトの BUFFERS 動作（PG18）、skip scan（PG18）、デフォルトの hash_mem_multiplier（PG15以降は2.0）はバージョンに依存します。

基本ルール

闇雲に修正しないでください。手順は次のとおりです：

1. 実際に時間がかかっている場所を特定する。
2. 原因となっているクエリとその呼び出し回数を特定する。
3. 計画内のどのノードで行数推定が誤っているかを特定する。
4. 修正を行う。
5. 本番規模のデータでページ数を使って検証する。

インデックス、work_mem、random_page_cost を変更するのは、診断でそれらが原因と示された場合のみにしてください。理由：プロファイルなしで「見た目が重い」クエリ（全体の1/60の負荷）を修正しても効果はなく、闇雲に追加したインデックスはむしろ長いクエリに悪影響を与える可能性があります。

時間がかかっている場所の特定

• 「遅い」という報告：まずデータベース内のクエリ実行時間とユーザーが感じる応答時間を比較します。クエリの合計が約100ミリ秒なのにユーザーが数分待っている場合、データベースは原因ではありません。クエリには手を加えず、アプリケーション、ネットワーク、フロントエンドを調査してください。
• 多数のクエリからなる操作：pg_stat_statements でプロファイルを取得します。
  1. pg_stat_statements.track = allを設定します。デフォルトはtopで、関数内のクエリは表示されません。
  2. pg_stat_statements_reset()を実行します。スーパーユーザー権限、または関数に付与された権限が必要です。 リセットはすべてのユーザーの統計情報を消去するため、ステージング環境か、本番では閑散時に行ってください。
  3. 操作を一度実行し、total_exec_time でソートします。
  4. calls、shared_blks_hit + shared_blks_read、toplevel を確認します。toplevel = true の行の時間には、内側のクエリの時間がすでに含まれています。すべてを単純に合計すると時間が二重にカウントされます。
• 大部分を占めるものだけを修正してください。 改善効果はその割合を超えません：60秒中の1秒のサブタスクなら、最大でも1/60の改善です。
• pg_stat_statements のクエリは正規化されています：定数は$1に置き換えられ、セッションごとに分割できず、集約値（平均、最小/最大、標準偏差）が保存されます。値によって計画が変わる可能性がある場合（大規模顧客と小規模顧客など）、具体的な値で EXPLAIN を個別に分析してください。pg_stat_activity と EXPLAIN VERBOSE を使って、queryid で行を紐づけることができます。これには compute_query_id が必要です（PG14以降。pg_stat_statements がロードされている場合、auto で有効になります）。
• ログ経由：log_line_prefix に %p または %c、および時間 %m を含める必要があります。内側のクエリは auto_explain でのみ表示されます。log_min_duration（デフォルトは -1、つまり無効）、log_analyze、log_nested_statements（デフォルトは off。変更にはスーパーユーザー権限または GRANT SET が必要。PG15以降）を設定してください。ログは pgBadger で解析します。単一セッションの場合は、SET log_min_duration_statement（スーパーユーザーまたは GRANT SET ON PARAMETER が必要）と LOAD 'auto_explain'（スーパーユーザーのみ）で十分です。

測定に使うデータについて

• おもちゃのようなデータの開発用データベースで結論を出さないでください。 計画はテーブルサイズと統計情報に依存し、小さなデータベースは丸ごとキャッシュに乗ります。小さなデータベースでクエリが速くても、それは何の証明にもなりません。
• ボリュームと分布は本番と同じにします。 本番に個人データがある場合、ステージングにコピーは持ち込まないでください。同じ形の合成データを生成します：メインテーブルの行数と、顧客ごとの分布を模倣します。本番環境には統計情報（pg_stat_statements、pg_class）の取得のみアクセスします。
• 本番環境からの定数を含む計画を外部のビジュアライザーに貼り付けないでください： 定数に個人データが含まれる可能性があります。ローカルツールを使用してください。

大量の高速呼び出し（N+1）

• クエリがすでに高速であっても、1回の操作で数十回、数百回呼び出されている場合、そのクエリ自体を高速化しようとしないでください。代わりに、タスク全体をひとつのクエリで処理するように書き換えてください：

  • コード内での ID リストに対するループの代わりに WHERE id = ANY($1) またはバッチ処理。
  • カテゴリ別の集計には count(*) FILTER (WHERE …)。
  • データがない行を失わないために LEFT JOIN。

  理由：1ms のクエリはこれ以上高速化できませんが、それを1000回呼び出すとボトルネックになります。例えば、27回の呼び出しをひとつのクエリにまとめると、約90万ページの読み取りが約6万ページに削減されます。

• ひとつのクエリで実現できない場合は、データベースの設定ではなく、サービス内のデータアクセス方法を見直してください。

バリアントの比較方法

• 主要な指標はページ数です。 EXPLAIN (ANALYZE, BUFFERS) を実行し、最上位ノードの shared hit + read を確認します。これには子ノードの合計が含まれます。hit と read はキャッシュ状態によって比率が変わるため、合計してください。PG18では、BUFFERS は ANALYZE と共に自動で含まれます。
• 時間は参考程度にします。 時間はキャッシュ、負荷、ハードウェア、ANALYZE 自身のオーバーヘッドによって変動します。2回目の実行は常に速くなり、何の証明にもなりません。「速くなった」と判断するには、複数回の実行結果を確認してください。
• ページ数は CPU 負荷（メモリ内ソート、関数、JIT、並列計画）を反映しません。 クエリが読み取りよりも計算を多く行う場合は、時間を個別に確認してください。
• 行数とバッファのみが必要な場合は、TIMING FALSE を追加します。これにより計測のオーバーヘッドがなくなります。

計画の分析

1. クエリのクラスを特定します。

   • 短いクエリ：返す行数が少なく、人間が応答を待つもの。例：オブジェクト詳細、リストページ、キー検索。インデックスアクセスと nested loop を想定します。分析中は SET max_parallel_workers_per_gather = 0 を設定します。
   • 長いクエリ：テーブルをほぼ全件読み、集計を行うもの。例：レポート、分析、データエクスポート。シーケンシャルスキャンとハッシュジョイン、並列処理を想定します。

   これは期待値であり、絶対的なルールではありません。計画が期待と異なる場合、インデックスを追加する前に、推定値に原因があるか調査してください。

2. 推定値と実測値の比較。 各ノードで rows と actual rows を比較します。loops > 1 の場合、actual rows は1回のループあたりの値なので、loops を掛けてください。最初に乖離が発生した最も内側のノードを修正します。誤差は計画の上位ノードに伝播し、誤ったアクセス方法や結合方法を引き起こします。

3. ページ数とテーブルサイズの比較（長いクエリ向け）。 ANALYZE を実行します。その後、最上位ノードの hit + read と、クエリ内のテーブルの pg_class の relpages の合計を比較します。クエリがインデックスや TOAST を読む場合は、それらを別途加算します。

   • 同等かそれ以下：データは一度だけ読み取られており、計画は妥当です。
   • 数倍以上：データが繰り返し読み取られています（例：nested loop 内の Index Scan）。

   短いクエリ（インデックス経由）にはこの基準は適用できません。

4. メモリ。 BUFFERS に temp read/written がある、または Sort がディスクに書き出されている、Hash の Batches > 1 である場合、メモリが不足しています。対策は「何を修正するか」のセクションを参照してください。

何を修正するか

最初に推定値とクエリ自身を修正し、次にメモリを修正します。コストパラメータは最後の手段です。

• 条件内のカラムに対する関数（starts_at::date = …、date_trunc('day', starts_at) = …、round(col) …）。半開区間 starts_at >= $1 AND starts_at < $2 に書き換えます。timestamptz の境界はサーバー時間ではなくユーザーのタイムゾーンで計算します。理由：関数に対して統計情報がない場合、プランナは固定の選択率（= の場合は 0.5%）を使用し、大きく誤差が生じます。カラムそのものに対する範囲条件は統計情報に基づいて評価され、インデックスが使用されます。書き換えが不可能な場合のみ、式インデックスまたは式に対する統計情報を作成してください。
• 選択されているインデックスを確認します。 複合インデックスでフィルタリング対象のカラムが最初でない場合、PG18以前はインデックス全体をスキャンします。PG18では skip scan が役立ちます（計画に Index Searches: N と表示されます）。ただし、最初のカラムの異なる値が少ない場合にのみ効果的です。そうでなければ、フィルタリング対象のカラムを最初に持つインデックスが必要です。
• 複合外部キーを宣言します（account_id, user_id など）。アプリケーション自身でチェックしている場合でも、JOIN は 外部キーのすべてのカラム を使用して単純な等価条件で記述します。プランナは外部キーを使用して複数列の結合を評価します。外部キーがない場合、プランナはカラムが独立していると見なし、大きく誤差が生じます（実際の10万行に対して1000行と見積もるなど）、その結果 nested loop が選択される代わりに hash join が選択されるべきところを見逃します。JOIN が外部キーを完全にカバーしていない場合（例えば、1つのカラムが式で使われている、または WHERE 句にある）、外部キーに基づく評価は適用されません。対象テーブルには同じカラムに対する一意キーが必要です。トレードオフとして、書き込みのオーバーヘッドが増加します。
• 長いクエリ内の不要な JOIN。 テーブルがフィルタリングに貢献しておらず、外部キーと NOT NULL 制約で結合が保証されている場合は、そのテーブルを削除します。これが最大の改善になることがよくあります。プランナが内部結合を自動的に削除するとは期待しないでください（外部キーがあっても同様）。プランナが削除するのは、LEFT JOIN で一意キーに結合し、そのカラムをクエリが読み取らない場合、および PG18以降では、自己結合で一意キーによる場合のみです。外部キーと NOT NULL の保証がない場合は、そのような結合を削除しないでください。結果が変わります。
• 推定値が不正確でクエリを書き換えられない場合：拡張統計情報を作成するか、ALTER TABLE … ALTER COLUMN … SET STATISTICS n（デフォルトは 100）を設定します。
• メモリ不足（テンポラリファイル、Batches > 1）。セッションまたはロールに対して work_mem または hash_mem_multiplier を引き上げます（SET、ALTER ROLE … SET）。グローバルには設定しないでください。work_mem は各セッションの各ノードに対して割り当てられます。ハッシュの上限は work_mem × hash_mem_multiplier です。デフォルトは 4MB × 2.0 = 8MB です（PG15以前は乗数が 1.0 でした）。SHOW で確認し、最終的な上限を計算してください。

「プランナが誤っている」という仮説

• トランザクション内で検証します：BEGIN; SET LOCAL enable_nestloop = off;（または enable_seqscan）、次に EXPLAIN (ANALYZE, BUFFERS)、その後 ROLLBACK。ページ数で比較します。これにより、より良い計画が存在するかどうかがわかります。
• enable_ 系設定を設定ファイルやサービスコードに残さないでください。* これらはプランナが特定のノードを使用するのを防ぐだけであり、すべてのクエリに影響します。ドキュメントではこれらを粗雑な一時的な手段と位置づけています。
• その後、二つの道があります：
  • 行数推定が不正確な場合 — 推定値を修正します（「何を修正するか」を参照）。
  • 推定値は正しいが、望ましい計画が複数のクエリで一貫して選択されない場合 — コストパラメータを変更します。SSD を使用しているか、データベースがほぼ完全にキャッシュに収まる場合は、random_page_cost を 4.0 から 1.1〜2 に下げますが、seq_page_cost（1.0）を下回らないようにします。effective_cache_size には実際の推定値（shared_buffers + OS キャッシュ、デフォルト 4GB）を設定します。データベース全体に変更を適用する前に、複数のクエリで検証してください。

このスキルの範囲外

• pg_query_state — パッチ適用済みカーネルが必要です。
• plprofiler と plpgsql_check — PL/pgSQL のロジック用です。ここではロジックはアプリケーション側にあります。
• 商用 PostgreSQL ビルドが必要な手法。