ZrobDev/sql-tuning

SQL チューニング（PostgreSQL を第一に）

変更する前に測定してください。変更前後の計画とタイミングなしでのチューニング変更は推測に過ぎません。

手順

1. ターゲットを見つける：単一の最も遅い呼び出しではなく、総コストでランク付けします（トリアージループを参照）。
2. ベースラインを取得する：本番規模のデータに対して EXPLAIN (ANALYZE, BUFFERS) <query> を実行します。小さな開発用データでは異なる計画が選択されます。
3. 時間または行数がおかしくなっているノードを見つける（以下の読み方のルールを参照）。そのノードのみを修正します。
4. 同じ計画取得を再実行します。時間とバッファの両方で改善された場合のみ、変更を保持します。
5. インデックスまたは書き換えとその理由を、スキーママイグレーションの隣に記録します。

INSERT/UPDATE/DELETE の EXPLAIN ANALYZE は BEGIN; ... ROLLBACK; でラップしてください。ANALYZE は実際にステートメントを実行するためです。

計画の読み方

• 下から上に、内側のノードから読んでいきます。ノードごとの actual time はループあたりの時間です。実際のコストを得るには loops を掛けてください。
• rows（推定値）と actual rows を比較します。10 倍以上の乖離がある場合、その原因はインデックス欠如よりもはるかに多いです。その場合は ANALYZE <table> を実行するか、列の統計情報対象を増やすか、相関列に対して拡張統計情報（CREATE STATISTICS ... (dependencies, ndistinct)）を追加してください。
• Rows Removed by Filter が返された行数よりもはるかに大きい場合、インデックス（またはその欠如）がこの述語に対して選択的でないことを意味します。
• Buffers: shared read はディスクまたは OS キャッシュ、shared hit は shared_buffers です。繰り返し実行しても読み取りが多い場合、ワーキングセットが収まっていないことを示します。
• Sort Method: external merge Disk: は、このソートに対して work_mem が小さすぎたことを意味します。セッションごとまたはロールごとに増やしてください。グローバルに増やすと、接続ごとにソートノードごとにメモリが掛け算されるためです。
• Nested Loop で内側の loops 数が大きく、内側にインデックスがない場合は、古典的な低速結合です。Hash Join が溢れている（Batches: >1）場合は、同じ work_mem の問題です。
• Seq Scan は、クエリがテーブルの大部分を読み取る場合には正しい選択です。行の 20% を返す Seq Scan を排除するためにインデックスを追加しないでください。
• Index Only Scan で Heap Fetches が多い場合、可視性マップが古くなっています。そのテーブルで VACUUM を実行してください。

インデックスの選択

測定した述語、結合、または ORDER BY にのみインデックスを作成してください。すべてのインデックスは書き込み時間、WAL、およびバキューム作業のコストがかかります。

• btree：等価、範囲、ソートのデフォルト。列順序：最初に等価列、次に範囲またはソート列。PG 18 のスキップスキャンは先頭列が欠けているケースに役立ちますが、それを前提に設計しないでください。
• 部分インデックス（WHERE status = 'open'）：クエリが小さく安定したスライス（未処理のアイテム、未処理の行など）をターゲットとする場合。クエリの WHERE がインデックスの述語を含意している必要があります。
• カバリングインデックス（INCLUDE (col)）：インデックスオンリースキャンがホットなクエリのヒープフェッチを排除する場合。含まれる列は検索可能なキーではありません。
• 式インデックス（ON (lower(email))、ON ((payload->>'id'))）：クエリが列に関数を適用する場合。クエリは同一の式を使用する必要があります。
• BRIN：列が物理的な順序（タイムスタンプ、シリアル ID）と相関する巨大な追記専用テーブル向け。小さなインデックスで粗いプルーニングを行います。pg_stats.correlation が 1 に近いことを確認してください。低い場合、BRIN はプルーニングしません。
• GIN：jsonb 包含、配列、全文検索。GiST：範囲、ジオメトリ、排他制約。
• 稼働中のテーブルには CREATE INDEX CONCURRENTLY で作成してください。トランザクションブロック内では実行できず、失敗した実行では削除すべき INVALID インデックスが残ります。
• 未使用のインデックスは、完全なビジネスサイクル（財務ワークロードでは月末）にわたって pg_stat_user_indexes.idx_scan を確認し、レプリカでも確認した後にのみ削除してください。

Sargability（索引利用可能性）

述語がインデックスを使用できるのは、インデックス列が片側に単独で存在する場合のみです。

• WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' と記述し、WHERE extract(year from created_at) = 2026 や date_trunc(...) = ... とは記述しないでください。
• WHERE col = $1 と型を一致させてください。numeric_col = '5'::text のような列側でのキャストはインデックスをブロックします。パラメータをキャストし、列はキャストしないでください。
• 先頭ワイルドカード LIKE '%x' は避け、pg_trgm GIN または全文検索を使用してください。
• 異なる列にまたがる OR はしばしばインデックスを失います。代わりにインデックス付きの 2 つのブランチを UNION ALL で結合してみてください。
• NOT IN (サブクエリ) は NULL に弱く低速です。NOT EXISTS を使用してください。
• セミ結合には DISTINCT よりも EXISTS を優先してください。最初の一致で停止し、行を重複させないためです。

ウィンドウ関数 vs 自己結合

目標が同じ集合内の関連行からの行単位の値（累計、ランク、lag/lead、「キーごとの最新行」（row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1））である場合は、ウィンドウ関数を使用してください。1 回のスキャンと 1 回のソートで、テーブル同士の結合が置き換えられるためです。 2 番目の側に独自のフィルターやインデックスプローブが必要な場合にのみ、自己結合または LATERAL を使用してください（大規模テーブルからのキーごとのトップ N：JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3)、インデックスは (k, ts DESC) を使用）。 DISTINCT ON (k) ... ORDER BY k, ts DESC は、キーごとの最新行を取得するための簡潔な Postgres 形式です。

CTE

PG 12 以降、副作用のない非再帰的な CTE が 1 回だけ参照される場合、サブクエリと同様にインライン展開されます。デフォルトでは可読性を優先して記述してください。

• CTE が高コストで複数回参照される場合、またはプランナーが不適切な述語を CTE 内に押し込むのを防ぐ必要がある場合は、AS MATERIALIZED を追加します。
• 外部フィルターによって安価になる場合に、複数回参照される CTE のインライン展開を強制するには、AS NOT MATERIALIZED を追加します。
• データ変更 CTE は常に 1 回実行されます。最適化によって削除されることはありません。

pg_stat_statements トリアージループ

shared_preload_libraries に pg_stat_statements が必要です（再起動が必要）。CREATE EXTENSION も必要です。

1. total_exec_time DESC で並べ替えます（shared_blks_read、temp_blks_written、rows も確認してください）。5 ms のクエリが 4000 万回呼び出されると、30 秒の夜間クエリよりも影響が大きいです。
2. 上位 3 つを取得します。それぞれについて、実際のパラメータセット（統計は正規化されています）を取得し、手順のステップ 2 を実行します。
3. 修正、デプロイ後、そのクエリに対してのみ SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>) を実行し、代表的な時間枠を待って比較します。
4. 上位エントリが正当なコストになるまで繰り返します。そこで止めてください。 再現できないクエリの計画をキャッチするために、auto_explain（log_min_duration、log_analyze は控えめに）と組み合わせて使用してください。

PL/pgSQL は真に価値がある場合にのみ使用する

関数やプロシージャは、集合ベースでは記述できない行単位のロジックの往復を削減する場合、トリガーの不変条件を強制する場合、または複数ステートメントのトランザクションをカプセル化する場合に使用してください。1 つの UPDATE を発行するために行をループさせるために使用しないでください。代わりに 1 つの UPDATE ... FROM または INSERT ... SELECT として書き換えてください。純粋な関数には正しく IMMUTABLE/STABLE とマークしてください（誤ったラベルは誤ったキャッシュ結果を生んだり、インライン展開をブロックしたりします）。可能な限りプレーンな SQL 関数を優先して、プランナーがインライン展開できるようにしてください。

N+1 とページネーション

• N+1：アプリケーションまたは ORM コードからの親行ごとの 1 クエリ。1 つの WHERE id = ANY($1) バッチ、結合、または ORM の即時ロードオプションで修正してください。pg_stat_statements で検出：1 つの正規化されたステートメントで、calls が親行数に近い場合。
• OFFSET ページネーションはスキップされたすべての行を読み取って破棄するため、N ページ目のコストは O(N) になります。キーセットページネーションを使用してください：WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50、インデックスは (created_at DESC, id DESC) を使用します。タイブレーカー列は必須です。そうしないと行がスキップまたは重複します。キーセットでは N ページ目にジャンプできません。OFFSET は小さな限定されたセットにのみ使用してください。
• 大きなテーブルでの count(*) はフルスキャンになります。近似カウント（reltuples）を表示するか、SELECT count(*) FROM (SELECT 1 ... LIMIT 10001) で上限を設定してください。

バルク ETL に関する注意

行単位の挿入ではなく、COPY（または INSERT ... SELECT）を使用してバッチロードしてください。大規模な初期ロードの場合にのみセカンダリインデックスを削除または延期し、その後 ANALYZE を実行してください。大規模な UPDATE はテーブルを膨張させます。キー範囲でバッチ化し、バッチごとにコミットし、autovacuum が追いつくようにしてください。

他の方言（短縮版）

• BigQuery：コストはバイト課金であり、行数ではありません。日付/タイムスタンプ列でパーティション分割し、リテラルまたはパラメータでフィルタリングしてプルーニングを発生させてください。次にフィルタリングされる列でクラスタリングします。SELECT * はすべての列に課金されます。列を指定してください。実行前にドライランでバイト数を確認してください。LIMIT は課金バイト数を削減しません。
• Snowflake：プルーニングはマイクロパーティションの最小/最大メタデータを使用します。そのため、ロード時に値が自然に順序付けられる列でフィルタリングしてください。クエリプロファイルで「スキャンされたパーティション vs 合計」を確認します。非常に大きなテーブルで安定したフィルタパターンがある場合にのみクラスタリングキーを追加してください。再クラスタリングにはクレジットがかかるためです。溢れ（プロファイル内のローカル/リモート溢れ）に対してはウェアハウスサイズをスケールアップし、キューイング/並行性に対してはスケールアウト（マルチクラスター）してください。自動サスペンドは短めに設定してください。

現在のバージョンとドキュメントリンク：references/landscape-2026-11.md。実際の使用からの教訓：references/gotchas.md。