SQL パフォーマンス最適化 — LATERAL Top-N・式INDEXの応用

QUESTION 1

グループごとの上位N件 — LATERAL JOIN で「行数ぶんのソート」を消す

LATERALTop-N per groupウィンドウ関数index相性

前提知識 ▼

「各ユーザーの最新3件の注文」のような グループごとの上位N件 は実務で頻出ですが、書き方で性能が桁違いに変わります。代表的な3つの形のうち、データ量と index 設計に応じて選び分けられるかが肝です。

-- [NG-1] 相関サブクエリ：外側の行ごとに ORDER BY + LIMIT が走る（N+1の派生）
SELECT u.user_id,
  (SELECT array_agg(o.order_id) FROM (
     SELECT order_id FROM orders
     WHERE user_id = u.user_id ORDER BY ordered_at DESC LIMIT 3) o)
FROM users u;

-- [NG-2] ウィンドウ関数：orders 全行を読んでから PARTITION ソート → rn<=3 でフィルタ
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn
  FROM orders) x WHERE rn <= 3;

-- ✓ LATERAL：各ユーザーで index (user_id, ordered_at DESC) の先頭3件だけ読む
SELECT u.user_id, o.order_id, o.ordered_at
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, ordered_at FROM orders
  WHERE user_id = u.user_id ORDER BY ordered_at DESC LIMIT 3) o;

選び方：ユーザー数が少なく orders が巨大なら LATERAL（index で先頭から3件×ユーザー数）、全行を走査する集計と合わせるなら ウィンドウ関数。「相関 = 常に悪」ではなく、index の物理配置と LIMIT の組み合わせで評価します。

問題 ▼

users（10万行）と orders（5000万行、index (user_id, ordered_at DESC) あり）から、各ユーザーの最新3件の注文を取得してください。ただし「orders を全件走査せず、ユーザーごとに高々3行ずつしか読まない」形で書くこと。出力列は user_id, name, order_id, ordered_at（user_id 昇順、同一 user 内は ordered_at 降順）。注文0件のユーザーは結果から除外して構いません。

使用テーブル

- users

user_id	name
1	Sato
2	Suzuki
3	Tanaka

- orders（index: user_id, ordered_at DESC）

order_id	user_id	ordered_at
101	1	2026-05-01
102	1	2026-05-03
103	1	2026-05-05
104	1	2026-05-07
201	2	2026-04-20
202	2	2026-05-02
301	3	2026-05-04
302	3	2026-05-06
303	3	2026-05-08
304	3	2026-05-09
305	3	2026-05-10

期待出力 ▼

user_id	name	order_id	ordered_at
1	Sato	104	2026-05-07
1	Sato	103	2026-05-05
1	Sato	102	2026-05-03
2	Suzuki	202	2026-05-02
2	Suzuki	201	2026-04-20
3	Tanaka	305	2026-05-10
3	Tanaka	304	2026-05-09
3	Tanaka	303	2026-05-08

模範解答コード ▼

SELECT    u.user_id, u.name, o.order_id, o.ordered_at
FROM      users u
CROSS JOIN LATERAL (        -- u の各行から見える相関サブクエリのように振る舞う
  SELECT   order_id, ordered_at
  FROM     orders
  WHERE    user_id = u.user_id  -- 外側の u.user_id を参照可能（LATERAL の本質）
  ORDER BY ordered_at DESC    -- index (user_id, ordered_at DESC) の物理順と一致
  LIMIT    3
) o
ORDER BY u.user_id, o.ordered_at DESC;

/*
  実行順序:
  1. FROM users u   → 外側を1件ずつ取り出す
  2. LATERAL サブクエリ  → user_id ごとに index シークし最大3行
  3. CROSS JOIN     → u と o を結合（注文ゼロは除外）
  4. ORDER BY       → user_id 昇順・user 内は ordered_at 降順
  */

解説（テーブル変化・ポイント） ▼

SELECT             u.user_id, u.name, o.order_id, o.ordered_at
FROM               users u
CROSS JOIN LATERAL (
  SELECT   order_id, ordered_at
  FROM     orders
  WHERE    user_id = u.user_id
  ORDER BY ordered_at DESC
  LIMIT    3
) o
ORDER BY u.user_id, o.ordered_at DESC;

LEGEND ▼

データ取得・読込対象

1. 対象テーブル — users × orders（index: user_id, ordered_at DESC） ▼

FROM users u, orders oorders は user_id でグルーピングされ、各 user_id 内で ordered_at が降順に並んだ B-tree index を持っています。これが LATERAL のキモ——「ユーザーごとの最新N件」が物理配置として既に存在します。

1 / 5

user_id	name	(orders 件数)
1	Sato	4件
2	Suzuki	2件
3	Tanaka	5件

users 3行 / orders 11行（実体は10万 × 5000万）

学習ポイント

LATERAL は「相関を許す JOIN」：通常の JOIN サブクエリは 外側の列を参照できませんが、LATERAL を付けると 外側の各行ごとに独立評価されます。これにより「ユーザーごとに LIMIT 3」のような行ごとの上位N件が JOIN の文法で書ける。サブクエリが各行で再評価されるコストは index がカバーするため、Top-N per group の最有力解になります。

「行数 × N」と「全件 + ソート」の損益分岐：外側の行数が小さく内側の母集合が大きい（本問の users 10万 vs orders 5000万）なら LATERAL の圧勝。逆に、orders に対して集約や複雑な分析を伴う場合はウィンドウ関数で1パスにする方が速いことも。EXPLAIN で Index Scan の rows × loops と Sort のメモリ/ディスク使用量を比較して判断します。

index の列順 = ソートの「ただ乗り」設計：本問が成立する核は (user_id, ordered_at DESC) という複合 index の列順です。先頭列で等値シーク、次列で範囲・ソートを担うという原則は応用編Q5で詳しく扱いますが、LATERAL もindex と ORDER BY の物理一致があってはじめて「3件読んで打ち切り」が可能になります。

アンチパターン

ウィンドウ関数を「とりあえず」適用する：「Top-N per group といえば ROW_NUMBER」と短絡すると、本問のように巨大な母集合を全件スキャンしてから捨てる形になります。とくに WHERE で先に絞れない場合はソート量が爆発しがち。母集合のサイズと出力サイズの比を最初に見積もる癖を。

LATERAL を「マジックワード」として使う：LATERAL サブクエリ内の WHERE / ORDER BY が index に乗らない形（関数で包む、降順 index がない、複数列でばらつく）だと、各行で Seq Scan が走って10万回のフルスキャンという最悪のN+1へ。EXPLAIN ANALYZE で必ず Index Scan + loops=外側行数 を確認してください。

実務コラム：3つの書き方の使い分けマップ

「Top-N per group」は、外側母集合の行数 / 内側の選択率 / 必要な分析の複雑さで書き分けます。(A) 相関サブクエリ: 単純な集約値1つを取りたいだけ（合計、最大）→ プランナがハッシュ化してくれる場合あり。(B) ウィンドウ関数: グループ全体の順位・累積・移動平均を伴う、または内側 ≪ 外側で全件読みが安いとき。(C) LATERAL: 外側が少なく、内側が巨大、かつ index と ORDER BY が一致する Top-N。本シリーズで貫いてきた「集合で1回」の原則は、「1回で済むなら集合化、行ごとの index 局所性が効くなら LATERAL」と読み替えると応用が利きます。関連する実務テーマとして、集合演算の落とし穴——NOT IN と NULL の三値論理です。

QUESTION 2

NOT IN と NULL の罠 — 副問合せに1行混じれば「全件消える」

NOT INNULLNOT EXISTSアンチジョイン

前提知識 ▼

基礎編で扱った 三値論理 の最も危険な発露が、NOT IN + NULL を含む副問合せです。x NOT IN (a, b, NULL) は内部的に x <> a AND x <> b AND x <> NULL。最後の比較がUNKNOWNになるため、AND 全体が TRUE になることが永遠にない——結果は必ず空です。クエリは成功し、ログにも何も出ません。

-- ✗ cancellations.user_id に NULL が1行でもあると、結果は常に空
SELECT u.* FROM users u
WHERE u.user_id NOT IN (SELECT user_id FROM cancellations);

-- ✓ NOT EXISTS：相関で「対応する行が存在しない」を判定。NULL に強い
SELECT u.* FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM cancellations c WHERE c.user_id = u.user_id);

-- ✓ アンチジョイン：LEFT JOIN + IS NULL（プランナにより NOT EXISTS と同等になることが多い）
SELECT u.* FROM users u
LEFT JOIN cancellations c ON c.user_id = u.user_id
WHERE c.user_id IS NULL;

性能面でも：PostgreSQL は NOT EXISTS や LEFT JOIN ... IS NULL を Anti Join に変換します。一方 NOT IN は NULL 安全性のために毎行 NULL チェックが必要で、最適化の手数が落ちる傾向。正しさと速さが同じ方向に向きます。

問題 ▼

退会記録テーブル cancellations（一部の行で user_id が NULL のことがある）を用いて、退会していないユーザーを抽出してください。ただし cancellations.user_id に NULL が混じっていても正しく動き、かつ Anti Join に展開される形で書くこと。出力列は user_id, name（user_id 昇順）。

使用テーブル

- users

user_id	name
1	Sato
2	Suzuki
3	Tanaka
4	Ito
5	Kato

- cancellations

cancel_id	user_id
91	2
92	4
93	NULL

期待出力 ▼

user_id	name
1	Sato
3	Tanaka
5	Kato

模範解答コード ▼

SELECT    u.user_id, u.name
FROM      users u
WHERE     NOT EXISTS (           -- 「対応する行が存在しない」の集合論的記述
  SELECT  1                      -- EXISTS は値を見ない（行の有無だけを判定）
  FROM    cancellations c
  WHERE   c.user_id = u.user_id  -- NULL 同士は等しくならない → NULL行は自然に無視
)
ORDER BY u.user_id;

/*
  実行順序:
  1. FROM users u    → 各 user を走査
  2. NOT EXISTS の評価  → 相関検索で1件でも見つかれば除外
  3. WHERE 通過        → キャンセルなしの user が残る
  4. SELECT          → user_id, name を確定
  5. ORDER BY        → user_id 昇順に整列
  */

解説（テーブル変化・ポイント） ▼

SELECT     u.user_id, u.name
FROM       users u
WHERE      NOT EXISTS (
  SELECT  1
  FROM    cancellations c
  WHERE   c.user_id = u.user_id
)
ORDER BY   u.user_id;

LEGEND ▼

データ取得・読込対象

除外・非表示データ

1. 対象テーブル — users と cancellations（NULL を1行含む） ▼

FROM users u, cancellations ccancellations.user_id は退会者の外部キーですが、データ取り込みの不備やシステム連携で NULL が紛れ込むことが実務では珍しくありません。この1行の存在が NOT IN をサイレントに壊します。

1 / 4

users.user_id	name	→ cancellations.user_id
1	Sato	—
2	Suzuki	2
3	Tanaka	—
4	Ito	4
5	Kato	—
—	—	NULL（cancel_id=93）

users 5行 / cancellations 3行（うち1行は user_id=NULL）

学習ポイント

NOT IN は副問合せに NULL が混じる可能性を常に疑う：列定義が NOT NULL 制約付きなら NOT IN でも安全ですが、外部キー先・履歴テーブル・分析テーブルはしばしば NULL を許可します。「今は NULL がないから大丈夫」も明日のスキーマ変更で壊れる。常時 NULL 安全な NOT EXISTS をデフォルトにすると事故が減ります。

3つの書き方の論理的等価性とプランナの扱い：NOT IN / NOT EXISTS / LEFT JOIN ... IS NULL はNULLが絡まなければ論理的に等価ですが、プランナの最適化機会が異なります。PostgreSQL は NOT EXISTS と LEFT JOIN ... IS NULL をHash Anti Join に変換しやすく、NOT IN は NULL 補正のため SubPlan のまま残ることが多い——正しさだけでなく性能でも有利です。

EXISTS / NOT EXISTS は「行の有無」だけを問う：SELECT 1 でも SELECT * でも SELECT NULL でも結果は同じ——値ではなく行の存在だけが判定対象です。プランナは 最初の1行を見つけた時点で打ち切り（Semi/Anti Join の本質）ます。COUNT(*) > 0 のような書き方は全件数えてから比較するので避けるのが定石。

アンチパターン

「NOT IN で 0 件 = 該当者なし」と早合点する：本来3人いるはずの結果が0行なのを「正常に該当なしだった」と誤判断するのが最悪のシナリオ。レポートが空白になっても気付かれず、業務判断を誤ります。NOT IN を使うなら副問合せ側に WHERE user_id IS NOT NULL を必ず付与するか、列に NOT NULL 制約を保証してください。

COUNT(*) で存在判定する：(SELECT COUNT(*) FROM cancellations WHERE user_id = u.user_id) = 0 と書くと、該当行を全部数え終わるまで打ち切れません。EXISTS は1行見つけた瞬間に終了——典型的に何桁も速くなります。「あるかないか」を聞くだけなら EXISTS、と覚えると揺らぎません。

実務コラム：「NULL を許容する列」設計と防御的SQLの境界

本問は「NULL が混じる可能性のある列をどう扱うか」という、スキーマ設計とクエリ防御の協働の話でもあります。外部キー列に NOT NULL 制約を付けるだけで NOT IN の罠は消えますが、現実の業務テーブルでは「未確定」「不明」を表現する NULL が必要なことも多い。原則は 「NULL を許すなら、それを意識した演算子を選ぶ」——比較は IS DISTINCT FROM、存在判定は NOT EXISTS、結合は LEFT JOIN ... IS NULL。クエリ側の防御と、スキーマ側の制約は相補的な二重防御として設計してください。次の Q3 では「正しいけれど index に乗らない式」——Sargable の話に進みます。

QUESTION 3

Sargable と式インデックス — 列に関数をかけても遅くしない設計

Sargable式インデックス関数索引Index Scan

前提知識 ▼

Sargable（Search ARGument ABLE）とは、WHERE 条件が index 走査の引数として使える形であることを指します。原則は単純で、「列を関数で包むと index が使えない」。WHERE LOWER(email) = 'a@x.com' と書いた瞬間、email の通常 index は無効化されます——index は 素の email 値 でソートされており、LOWER した値 でソートされた構造ではないからです。

-- ✗ 列を関数で包む → 通常の (email) index は使えず Seq Scan
WHERE LOWER(email) = 'user@example.com'

-- ✗ 暗黙の型変換も列を包む扱いになる（CAST が裏で挿入される）
WHERE created_at::date = '2026-05-01'
WHERE phone_number = 8012345678   -- phone_number が VARCHAR の場合

-- [OK-A] 式 index：式そのものを索引化（クエリの式形と一致させる）
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- [OK-B] クエリを書き換えて列を裸にする（半開区間など）
WHERE created_at >= '2026-05-01' AND created_at < '2026-05-02'

判定法：クエリの WHERE 式と index の式が 文字通り同じ形 でなければプランナは使えません。LOWER(email) の式 index は LOWER(email) = ? には効きますが email ILIKE ? には効かない——「式の形が一致する」までが Sargableです。

問題 ▼

ユーザー登録時の大文字小文字を無視したメールアドレス検索を実装してください。ストレージは email 列（VARCHAR、混在ケースで登録される可能性あり）。ユーザー数100万件、検索は1秒間に100回。クエリと index の両方を提示し、検索クエリは email = 'User@Example.COM' のような任意のケースの入力に対して動くこと。出力列は user_id, email, name。

使用テーブル

- users（100万行、index 検討中）

user_id	email	name
1	sato@example.com	Sato
2	Suzuki@Example.com	Suzuki
3	TANAKA@example.com	Tanaka
4	ito@example.com	Ito
...	...（100万件）	...

期待出力 ▼

user_id	email	name
2	Suzuki@Example.com	Suzuki

模範解答コード ▼

CREATE INDEX idx_users_email_lower  -- 1. 式インデックス：LOWER(email) という「式そのもの」を索引化
ON users (LOWER(email));

-- 2. クエリ：式の形を index と一致させ、入力側にも LOWER を適用
SELECT user_id, email, name
FROM   users
WHERE  LOWER(email) = LOWER('Suzuki@Example.com');

/*
  実行順序:
  1. 解析        → WHERE の式が式 index と一致するか判定
  2. プラン選択     → 等値条件で Index Scan を採用
  3. index 探索  → 入力を1回計算し B-tree を二分探索
  4. ヒープアクセス   → 該当行の email / name を取得
  5. SELECT    → 生の email を返す
  */

解説（テーブル変化・ポイント） ▼

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

SELECT user_id, email, name
FROM   users
WHERE  LOWER(email) = LOWER('Suzuki@Example.com');

LEGEND ▼

データ取得・読込対象

1. 対象テーブル — users.email は混在ケースで保存されている ▼

FROM usersemail 列にはユーザーが入力したそのままのケースが保存されています。表示上はケースを保ちたい一方、検索はケース非依存でしたい——この要件が「式の正規化」を呼びます。

1 / 5

user_id	email（生の値）	LOWER(email)（正規化）
1	sato@example.com	sato@example.com
2	Suzuki@Example.com	suzuki@example.com
3	TANAKA@example.com	tanaka@example.com
4	ito@example.com	ito@example.com

100万行のうち4行を表示

学習ポイント

Sargable の核心は「式の形の一致」：プランナは魔法ではなく 文字レベルで式を照合します。LOWER(email) の式 index は LOWER(email) = ? で使えますが、email ILIKE ? や email = ? COLLATE "C" では使えません。「index のキー式 = クエリの WHERE 式」が成立しているかを EXPLAIN で必ず確認する習慣を。

暗黙の型変換は静かな Sargable 破壊者：WHERE phone_number = 8012345678（列は VARCHAR）と書くと、PostgreSQL は phone_number::numeric = 8012345678 と解釈する場合があり、列を関数で包んだのと同じ結果に。created_at::date = '2026-05-01' も同様。リテラル側を列の型に合わせる、または半開区間に書き換えるのが定石です。

選択肢は「式 index」だけではない：PostgreSQL なら CITEXT 型（大文字小文字を区別しない文字列型）に列を変えれば、通常の = 検索が自動でケース非依存に。列の正規化形を別カラムに保存（マテリアライズド）して通常 index を貼る、という設計もあります。式 index は「既存スキーマを変えずに導入できる」のが利点ですが、書き込み時に式が評価されるコストがある点は留意。

アンチパターン

index を「貼ったから安心」と思い込む：「email に index あります」と聞いても、クエリが LOWER(email) = ... ならその index は1ミリも使われません。新規 index 追加時は必ず EXPLAIN (ANALYZE, BUFFERS) で Index Scan / Index Only Scan の表示と Buffers の Hit/Read を確認。「貼った事実」ではなく「使われた証拠」で完了とする。

クエリと index の式形が乖離していく：運用中に「TRIM(LOWER(email)) にしたい」「LOWER(email || domain) にしたい」とクエリだけ書き換えると、式 index が静かに使われなくなるサイレント劣化が発生。式 index とアプリのクエリはペアで設計・変更し、CI で EXPLAIN の回帰テストを走らせる運用が理想です。

実務コラム：Sargable は「思想」であって「呪文」ではない

Sargable は「列を裸にしてリテラル側を加工する」という、ごく単純な思想に集約されます。本問の LOWER は氷山の一角で、日付の DATE_TRUNC / 文字列の SUBSTRING / 数値の ROUND も全て同じ罠を生みます。原則は3つ——(1) 列に関数をかけない、(2) 暗黙キャストを避ける、(3) どうしても式が必要なら式 index でクエリと完全一致させる。これに EXPLAIN による「使われた証拠」の習慣を組み合わせると、index が「貼ってあるのに効かない」現場あるあるから卒業できます。関連する実務テーマとして、index があっても性能が出ない代表例——OFFSET ページネーションです。

QUESTION 4

OFFSETの罠とKeysetページネーション — 100ページ目を1ページ目と同じ速さで

OFFSETKeysetページングseek法複合タプル比較

前提知識 ▼

ページネーションを OFFSET n LIMIT k で書くのは直感的ですが、OFFSET は「読み捨て」です。100ページ目（OFFSET 9900） を取るために、DB は 条件を満たす行を9900件読んでから捨てて、ようやく次の100件を返します。ページが深くなるほど線形に遅くなる——これが Deep Pagination 問題です。

-- ✗ OFFSET は「読み飛ばし」ではなく「読んで捨てる」
SELECT * FROM articles
ORDER BY created_at DESC, id DESC
OFFSET 9900 LIMIT 100;
-- 実行コスト: 9900 + 100 = 10000 行を読む（しかも整列後に）

-- ✓ Keyset（seek法）：「前ページの最後のキー」より小さい行を index で seek
SELECT * FROM articles
WHERE (created_at, id) < ('2026-05-04 10:00', 501)  -- ← タプル比較で「次のページ」を表現
ORDER BY created_at DESC, id DESC
LIMIT 100;
-- 実行コスト: 何ページ目でも 100 行（index 上で先頭から seek するだけ）

タプル比較の意味：(a, b) < (X, Y) は a < X OR (a = X AND b < Y) と等価です。同じ created_at の行が複数あっても、(created_at, id) の辞書順で一意な「次の位置」が決まる——これが Keyset の核心です。

問題 ▼

articles（100万行、複合 index (created_at DESC, id DESC) あり）を 新しい順 にページングします。前のページの最後の記事が (created_at='2026-05-04 10:00', id=501) だったとき、次の100件を取得するクエリを書いてください。OFFSET を使わず、何ページ目でも同じ性能で動く形にすること。出力列は id, title, created_at。

使用テーブル

- articles（100万行、index: created_at DESC, id DESC）

id	title	created_at
510	新着A	2026-05-04 15:00
505	新着B	2026-05-04 12:00
501	記事X	2026-05-04 10:00
499	記事Y	2026-05-04 10:00
498	記事Z	2026-05-04 09:00
490	過去A	2026-05-03 18:00
...	...（100万件）	...

期待出力 ▼

id	title	created_at
499	記事Y	2026-05-04 10:00
498	記事Z	2026-05-04 09:00
490	過去A	2026-05-03 18:00
...	...（最大100件）	...

模範解答コード ▼

SELECT   id, title, created_at
FROM     articles
WHERE    (created_at, id) < ('2026-05-04 10:00', 501)
ORDER BY created_at DESC, id DESC                      -- ↑ 複合タプル比較：辞書順で「次の位置」を表現
LIMIT    100;                                          -- ↑ index (created_at DESC, id DESC) と物理順が一致

/*
  実行順序:
  1. WHERE のタプル比較を分解       → 辞書順条件に変換
  2. プランナは Index Scan を選択  → index 上で seek
  3. 走査                    → index の物理順そのままに読む（ソートなし）
  4. ヒープアクセス               → id, title, created_at を取得
  5. LIMIT                 → 打ち切り（OFFSET なし）
  */

解説（テーブル変化・ポイント） ▼

SELECT   id, title, created_at
FROM     articles
WHERE    (created_at, id) < ('2026-05-04 10:00', 501)
ORDER BY created_at DESC, id DESC
LIMIT    100;

LEGEND ▼

データ取得・読込対象

1. 対象テーブル — articles と複合 index (created_at DESC, id DESC) ▼

FROM articlesindex の物理順は created_at 降順 → 同値なら id 降順。これは「新着順に並べた一覧表」そのもの。ページングは この一覧表の「どこから読むか」 を決める問題に還元されます。

1 / 5

index 物理順	id	title	created_at
1（先頭）	510	新着A	2026-05-04 15:00
2	505	新着B	2026-05-04 12:00
3	501	記事X（前ページ末尾）	2026-05-04 10:00
4	499	記事Y	2026-05-04 10:00
5	498	記事Z	2026-05-04 09:00
6	490	過去A	2026-05-03 18:00

articles 100万行（index で物理整列済み）

学習ポイント

OFFSET は読み捨て、Keyset は seek：OFFSET n は 「条件を満たす行を n + LIMIT 件読んで、先頭の n 件を破棄」するという文字通りの動作。ページ深度に対して線形にコストが増えるのは index があっても変わりません。Keyset は 「次の位置」を WHERE で直接表現するので、何ページ目でも O(log N) + LIMIT。ページ深度の影響を受けないのが真価です。

タプル比較は「複数列の辞書順」の言語表現：(created_at, id) < (X, Y) は冗長な OR / AND の組合せを1行で書けるうえ、プランナが複合 index の列順と直接対応付けやすい形です。ソート列の組と index の列順を一致させ、その同じ組をタプル比較に使う——この3点が揃った瞬間、ページングは「先頭から N 件読むだけ」の問題に縮約されます。

カーソルは状態：UI/API設計と一体で考える：Keyset は「次ページのカーソル（前ページ末尾キー）」をクライアントが保持する設計です。無限スクロールや cursor-based API（GitHub・Stripe・Twitter等）はこの方式。一方「N ページ目に直接ジャンプ」や総ページ数表示が必須な業務UIでは Keyset は不向きで、その場合はOFFSET の上限を浅く制限するか、マテリアライズしたページ番号を持つ等の妥協が必要です。

アンチパターン

「ORDER BY created_at」だけで Keyset を書く：同じ created_at の行が複数あると、WHERE created_at < ? だけでは同一秒内の行をスキップしたり重複したりします。必ず 一意になるまでキーを足す（多くは (ソート列, 主キー)）。タプルが一意でないページングは、たまたま動いているだけです。

WHERE と ORDER BY と index の列順が三者三様：WHERE (a, b) < (X, Y) なのに ORDER BY b DESC, a DESC、index は (a, c)——どれか1つでもズレるとseek が効かず Sort + Filter に転落します。Keyset は「タプル比較の列順 = ORDER BY の列順 = index の列順」の三位一体が大前提です。

実務コラム：ページネーション設計の意思決定フロー

「OFFSET か Keyset か」はUI要件で決まる技術選択です。(1) 無限スクロール / 一方向の閲覧（SNSタイムライン、検索結果）→ Keyset 一択。深さに上限がなく、ユーザーは「次へ」しか押さない。(2) ページ番号UI / 任意ページへのジャンプ（管理画面、検索結果の上位数十ページ）→ OFFSET でも可。ただし「最大100ページまで」のような深度制限を設けるのが現実解。(3) ハイブリッド（先頭の数ページは OFFSET、深部は Keyset）も有効。重要なのは、ページネーションは「DB の都合」ではなく「ユーザー体験の制約」で設計するということ。次の Q5 では、本問でも前提にした「複合 index の列順」と、ヒープアクセスすら消す Index-Only Scan の世界に踏み込みます。

QUESTION 5

複合インデックスの列順とINCLUDE — Index-Only Scanで「ヒープを読まない」

複合index列順INCLUDEIndex-Only Scan

前提知識 ▼

複合インデックスの設計には3つの黄金律があります。(1) 等値で絞る列を先頭、(2) 範囲・ソート列を次、(3) 出力に必要な列は INCLUDE。これが揃うと、プランナはヒープ（テーブル本体）を一切読まず index だけで結果を組み立てられます——これが Index-Only Scan。実務でのI/O削減効果は桁が変わります。

-- 想定クエリ: 「pending タスクのうち、ある日付以降の title 一覧（新しい順、100件）」
SELECT title FROM tasks
WHERE status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC LIMIT 100;

-- [NG-A] 列順が逆：(created_at, status) では status の絞り込みが遅れる
CREATE INDEX ... ON tasks (created_at, status);

-- [NG-B] title を含まない：index で絞れてもヒープを毎回見に行く（Index Scan 止まり）
CREATE INDEX ... ON tasks (status, created_at);

-- ✓ 等値→範囲・ソートの列順 + INCLUDE で出力列を index に同居
CREATE INDEX ... ON tasks (status, created_at DESC) INCLUDE (title);

INCLUDE とキー列の違い：INCLUDE 列はキーとしては使われない（ソート・検索の対象外）が、index のリーフに値だけ同梱される。「出力に必要だが絞り込みには使わない列」を入れるための専用機能です（PostgreSQL 11+）。

問題 ▼

巨大な tasks（5000万行、status は'pending'が約1%）テーブルに対し、次のクエリを Index-Only Scan で実行できる複合インデックスを1本だけ設計してください。クエリは変更不可。EXPLAIN で Index Only Scan と表示され、Heap Fetches が 0 になる形を目指します。

SELECT title FROM tasks
WHERE  status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT 100;

使用テーブル

- tasks（5000万行 / status 偏り: 'pending'≈1%, 'done'≈99%）

task_id	status	created_at	title	assignee
1	done	2026-04-01	...	...
2	pending	2026-05-03	請求書確認	Sato
3	pending	2026-05-02	在庫棚卸	Suzuki
4	done	2026-05-01	...	...
5	pending	2026-05-05	ABテスト集計	Tanaka

期待出力 ▼

Limit  (cost=...) (actual rows=100 loops=1)
  -> Index Only Scan using idx_tasks_pending_covering on tasks
        Index Cond: ((status = 'pending') AND (created_at >= '2026-05-01'))
        Heap Fetches: 0 ← ヒープアクセスゼロ
Buffers: shared hit=N read=M (index ページのみ)

模範解答コード ▼

CREATE INDEX idx_tasks_pending_covering  -- 設計: status（等値）→ created_at DESC（範囲＋ORDER BY）→ title（INCLUDE で同梱）
ON tasks (status, created_at DESC)
INCLUDE (title);

-- クエリ（変更不可・参考）:
SELECT title FROM tasks
WHERE  status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT  100;

/*
  実行順序（プランナの戦略）:
  1. WHERE の等値 (status='pending') を index 先頭列で絞り込み
  2. その区間内で created_at DESC が物理順に並ぶ
  3. ORDER BY created_at DESC  → ソートなし
  4. INCLUDE (title) により、各エントリに title 値が同梱されている
  5. LIMIT 100 で打ち切り
  */

解説（テーブル変化・ポイント） ▼

CREATE INDEX idx_tasks_pending_covering
ON tasks (status, created_at DESC)
INCLUDE (title);

SELECT   title
FROM     tasks
WHERE    status = 'pending' AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT    100;

LEGEND ▼

データ取得・読込対象

除外・非表示データ

1. 対象テーブル — tasks 5000万行（status は'pending'≈1%） ▼

FROM tasksテーブル本体（ヒープ）は task_id 順や挿入順で乱雑に並んでいます。クエリが要求するのは「pending かつ最近の100件の title」——この要件をいかに小さなI/Oで満たすかが設計のすべてです。

1 / 5

task_id	status	created_at	title	assignee
1	done	2026-04-01	...	...
2	pending	2026-05-03	請求書確認	Sato
3	pending	2026-05-02	在庫棚卸	Suzuki
4	done	2026-05-01	...	...
5	pending	2026-05-05	ABテスト集計	Tanaka

ヒープ 5000万行 — このうち pending は約50万行

学習ポイント

列順の黄金律：等値 → 範囲・ソート → INCLUDE：複合 index は左端プレフィックス則（先頭から連続的にしか使えない）に従います。原則は (1) 等値で絞る列を先頭（最も選択率を稼げる）、(2) 範囲条件・ORDER BY 列を次（同じ列なら一気に処理）、(3) 出力に必要なだけの列を INCLUDE。この順序を外すと「index は貼ったのに効かない」現象が起きます。

INCLUDE はキー列ではない — そこが価値：INCLUDE 列はB-tree のキーとしてソート対象に入りません。これにより (a) index が太らない（キーが増えると比較・分岐が増える）、(b) 値の変更が頻繁な列でも index 構造が壊れない、(c) UNIQUE 制約を維持したまま追加列を運べる といった利点があります。「絞り込みには使わないが、出力には必要」な列の専用置き場です。

Index-Only Scan の前提：Visibility Map が新鮮であること：PostgreSQL の Index-Only Scan は 「この行はすべてのトランザクションから見えるか」を Visibility Map で判定します。最近更新された行は判定不能でヒープを見に行く（Heap Fetches が増える）。VACUUM（特に VACUUM ANALYZE）が Visibility Map を更新するため、更新が多いテーブルほど autovacuum の調整が効いてくるのが実務の肝です。

アンチパターン

「全部キー列に入れれば速い」と長大な複合 index：キー列を増やすほど B-tree が太り、書込みコストが増え、キャッシュ効率が落ちます。さらに左端プレフィックス則のため、先頭列を含まないクエリでは使えない。出力用列は INCLUDE、絞り込み・整列に使わない列はそもそも入れないのが原則。1本の index に詰め込まずクエリ別に最小限の index を設計する方が結果的に軽くなります。

DESC を index に明示しないまま「逆順」を期待する：PostgreSQL は B-tree を双方向に走査できるので多くの場合は ASC index で DESC 取り出しも効きますが、複数列で昇順・降順が混在するクエリ（例: ORDER BY a ASC, b DESC）では index 側で (a, b DESC) と明示しないとソートが必要になります。「だいたい同じ」では通らない局面があるので、ORDER BY の向きと index 定義は必ず文字通り一致させるのが安全です。

実務コラム：応用編3の総まとめ — 「index を設計する」から「クエリと一体で設計する」へ

Q1〜応用を貫くテーマは、「クエリ・データ・index は三位一体の設計対象である」ということです。Q1（LATERAL）は クエリ形と index 列順の物理一致、Q2（NOT EXISTS）は NULL を含むデータ形に合った演算子選択、Q3（式 index）は クエリの式と index の式の字句一致、Q4（Keyset）は タプル比較・ORDER BY・index 列順の三者一致、Q5（INCLUDE）は 絞込み列・ソート列・出力列の役割分担——どれも「クエリだけ」「index だけ」では完結しません。基礎編3の原則（Sargable・半開区間・Semi Join・部分 index）と合わせれば、EXPLAIN を読み解いてプランナと対話する下地が揃います。続編は実行計画読解・統計情報・並列処理・パーティショニングといった「プランナの内側」へ。コードの先にある DB エンジンの挙動を覗きにいきましょう。