---
note: ページ/B-tree/複合索引の列順と、照合順序が前方一致検索の索引に効く実害
created: 2026-08-14T10:00:00+09:00
---

# 第7回｜ストレージとインデックス＋照合順序の実害

> 行がディスク上でどう置かれ、B-tree がその居場所をどう指すのかを図で描けるようにする回。加えて、第1回で決めた照合順序（collation）が索引に効いてくる「実害」を自分の手で再現する。

## この回のねらい

テーブルは 8KB のページの集まりであり、索引はその中の1行を指すポインタの集まりである。この物理像を持てると、「なぜこの索引は効き、あの索引は効かないのか」を推測ではなく構造から説明できる。この回では、ヒープ・ページ・タプル・TID・B-tree の関係を押さえ、複合索引の列順・カバリング・部分索引を使い分け、最後に照合順序が前方一致検索（`LIKE 'abc%'`）に落とす影を実測で再現する。

## 到達目標

- ページ・ヒープ・タプル・TID の関係を、図または自分の言葉で説明できる。
- B-tree の根・内部・葉ノードの役割と、葉からヒープへの TID 参照を説明できる。
- 複合索引の列順を「等値 → 範囲」の原則で決められ、`EXPLAIN` で列順の良し悪しを読み分けられる。
- カバリング索引（`INCLUDE`）・部分索引を、いつ使うか判断できる。
- B-tree / GIN / GiST / BRIN の使い分けを、対象データで選べる。
- 非 C collation のままだと `LIKE 'abc%'` が Seq Scan に落ちる理由を説明でき、`COLLATE "C"` か `text_pattern_ops` で索引を効かせられる。
- 関数を掛けた列に索引が効かないことを理解し、式インデックスで解決できる。

## 前提と準備

第1回で作成した PostShop スキーマ（`customers` 約5万行、`orders` 約100万行、`order_items` 約300万行ほか）が投入済みであることを前提とする。バージョンは **PostgreSQL 16 系**を前提に書く。

計測の前に統計情報を最新化しておく。プランナは統計に基づいて索引を選ぶため、これを怠ると再現が揺れる。

```sql
ANALYZE;
```

`psql` では実行時間を出す `\timing` を有効にし、実行計画は `EXPLAIN (ANALYZE, BUFFERS)` で読む。`BUFFERS` はページ I/O 量を出すため、索引の効き目を数字で確かめられる。

この回の実測例には `EXPLAIN` の出力がそのまま登場する。計画の読み方そのものは[第8回](08-explain.md)で扱うので、ここでは次の語だけ意味を取れれば足りる。

| 出力に出る語 | この回で読み取れればよい意味 |
| --- | --- |
| `Seq Scan` | テーブルの全ページを順に読んだ。索引が使われていない |
| `Index Scan` / `Index Only Scan` | 索引をたどった。後者はヒープを読まずに完結した |
| `Bitmap Index Scan` / `Bitmap Heap Scan` | 索引で該当ページの地図を作り、ヒープをまとめ読みした |
| `Index Cond` | 索引をたどる時点で使えた条件。ここに条件が入るほど絞れている |
| `Filter` | 行を読んだ後に捨てるために使われた条件。索引では絞れていない |
| `Buffers: shared hit=N read=M` | 触れたブロック数。`hit` はキャッシュ命中、`read` はディスク読み |
| `Heap Fetches` | Index Only Scan がヒープを読みに行った回数。0 が理想 |

```text
postgres=# \timing on
Timing is on.
```

ページサイズは既定で 8192 バイト。次で確認できる。

```sql
SHOW block_size;   -- 8192
```

## この回で新しく出てくる用語

前半4つは物理構造、後半は索引を効かせるための道具立てである。いずれも本編で詳しく扱うが、先に一覧で押さえておく。回をまたいで使う語は[用語集](glossary.md)にもまとめている。

| 用語 | 一行での説明 |
|---|---|
| 索引（インデックス） | 表の全ページを読まずに目的の行へ到達するために、列の値と行の住所の対応を別に持たせた補助のデータ構造。B-tree・GIN・GiST・BRIN などの種類がある |
| ヒープ | テーブル本体のデータが格納されるファイル群 |
| ページ | ヒープを分割する固定長 8KB の単位。読み書きはこの単位で行われる |
| タプル | 1行の物理的な姿。ページの中に詰め込まれる |
| TID | 行の物理的な住所。（ページ番号, ページ内の行ポインタ番号）の組 |
| オペレータクラス（opclass） | 索引が値をどの演算子でどう比較するかを決める規則の組 |
| `IMMUTABLE` | 同じ引数なら常に同じ結果を返し、DBの中身を読まない関数につける宣言 |
| 可視性マップ | どのページが全トランザクションから可視かを記録した補助データ |
| `pg_trgm` | 文字列を3文字の断片に分けて、類似度・中間一致検索を可能にする拡張 |
| `VACUUM` | 不要になった古い行の領域を回収して再利用可能にする保守処理（[第12回](12-operations-monitoring.md)） |

## 本編

### ヒープ・ページ・タプル・TID

PostgreSQL のテーブル本体は**ヒープ**（heap）と呼ばれるファイル群で、内部は固定長 **8KB のページ**（ブロックとも呼ぶ）に分割される。1ページの中に、複数の**タプル**（＝行の物理的な姿）が詰め込まれる。

1ページの構造は次のようになっている。ページ先頭にヘッダがあり、そこから「行ポインタ配列」（ItemId、1件4バイト）が下方向へ伸びる。ページ末尾からはタプル本体が上方向へ積まれ、両者が中央の空き領域で出会う。行ポインタは「このページ内の何バイト目にタプルがあるか」を指す間接参照であり、この間接があるおかげで、ページ内でタプルが動いても外から見た住所を変えずに済む。

```text
オフセット 0（ページ先頭）
  |
  |  ページヘッダ（24B）
  |  行ポインタ配列 ItemId[] … ヘッダ直後から下へ伸長 ↓
  |
  |  空き領域（free space） … 両者はここで出会う
  |
  |  タプル本体 … ページ末尾から上へ伸長 ↑
  v
オフセット 8191（ページ末尾）
```

各領域の役割を表でも示す。

| 領域 | 位置 | 役割 |
| --- | --- | --- |
| ページヘッダ | 先頭（約24B） | ページ全体のメタ情報（空き領域境界など） |
| 行ポインタ配列 ItemId | ヘッダ直後、下方向へ | 各行の「ページ内オフセット＋長さ」。1件4B |
| 空き領域 | 中央 | 新規タプルが入る余地 |
| タプル本体 | 末尾、上方向へ | 行の実データ（タプルヘッダ＋NULLビットマップ＋各列値） |

行の物理的な住所を **TID**（Tuple ID）と呼ぶ。TID は **(ページ番号, ページ内の行ポインタ番号)** の組である。システム列 `ctid` で覗ける。

```sql
SELECT ctid, id, customer_id, ordered_at
FROM orders
ORDER BY id
LIMIT 5;
```

```text
 ctid  | id | customer_id |      ordered_at
-------+----+-------------+------------------------
 (0,1) |  1 |       41822 | 2023-04-01 09:12:33+09
 (0,2) |  2 |        1093 | 2023-04-01 09:15:02+09
 (0,3) |  3 |       28740 | 2023-04-01 09:19:47+09
 (0,4) |  4 |       50011 | 2023-04-01 09:23:10+09
 (0,5) |  5 |         882 | 2023-04-01 09:24:55+09
(5 rows)
```

`(0,1)` は「0番ページの1番目の行ポインタ」を意味する。索引が最終的に指すのはこの TID である。なお `ctid` は行の更新や `VACUUM FULL` で変わり得るので、アプリのキーには使わない（安定した住所は主キー `id`）。

### B-tree の構造

**索引**（インデックス）とは、表の全ページを読まずに目的の行へ到達するために、列の値とその行の住所（TID）の対応を別に持たせた補助のデータ構造である。値をどう並べて持つかで何種類かに分かれ、PostgreSQL の既定は **B-tree**（正確には B+tree の一種）である。B-tree は値でソートされた多段の木で、次の3種類のノードからなる。

- **根ノード（root）**: 木の入口。1つだけ。
- **内部ノード（branch）**: 「この値より小さければ左、大きければ右」という区切りキーと、子ページへのポインタを持つ。実データの住所は持たない。
- **葉ノード（leaf）**: ソートされた **索引キー値と、対応するヒープの TID** の組を持つ。葉どうしは左右に連結され、範囲スキャンで隣の葉へ辿れる。

検索は根から始まり、区切りキーを辿って葉に降り、葉のエントリが指す TID でヒープの該当ページを読む。大きなテーブルでも木の高さは3〜4段に収まるため、目的の1行に数回のページ参照で到達できる。これが「索引が速い」の正体である。

```text
root    [ ... | 40000 | ... ]
        |
        +-- branch [10000 | 20000]      … 40000 未満へ降りる枝
        |
        +-- branch [50000 | 60000]      … 40000 以上へ降りる枝
              |
              +-- leaf [41821->TID, 41822->(0,1), ...]
              |        |
              |        +-- TID (0,1) をたどってヒープの該当ページを読む
              |
              +-- leaf [41850->TID, 41851->TID, ...]   ← 葉どうしは左右に連結
```

葉から先が「TID をたどってヒープを読む」2段構えになっている点が要である。索引が**キー値だけ**を持ち、行の残りの列はヒープにしかないため、選択した列が索引に無ければ、索引で住所を得たあとに必ずヒープを読みにいく（これを避ける工夫が後述のカバリング索引）。

### 複合索引の列順は「等値 → 範囲」

複数列に対する **複合索引**（composite index）では、**列の順序**が効き目を左右する。原則は **等値条件で絞る列を先、範囲条件で絞る列を後**。理由は B-tree のソート順にある。`(customer_id, ordered_at)` の索引は、まず `customer_id` で並び、同じ `customer_id` の中で `ordered_at` が並ぶ。したがって「ある `customer_id` に等値で降り、その中で `ordered_at` の範囲を連続した一区間として舐める」ことができる。

次のクエリで、列順を入れ替えた2本の索引を比べる。

```sql
-- 良い順: 等値(customer_id) → 範囲(ordered_at)
CREATE INDEX idx_orders_good ON orders (customer_id, ordered_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at
FROM orders
WHERE customer_id = 12345
  AND ordered_at >= '2025-06-01';
```

```text
 Index Scan using idx_orders_good on orders  (cost=0.42..8.9 rows=6 width=16)
                                             (actual time=0.031..0.048 rows=7 loops=1)
   Index Cond: ((customer_id = 12345) AND (ordered_at >= '2025-06-01 00:00:00+09'))
   Buffers: shared hit=5
 Planning Time: 0.12 ms
 Execution Time: 0.070 ms
```

両条件が **Index Cond** に入り、`customer_id = 12345` の位置へ一度降りてから `ordered_at >= ...` の区間だけを読む。触れるページはごく少数（例: `Buffers: shared hit=5`）。

列順を逆にすると様相が変わる。

```sql
DROP INDEX idx_orders_good;

-- 悪い順: 範囲(ordered_at) → 等値(customer_id)
CREATE INDEX idx_orders_bad ON orders (ordered_at, customer_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at
FROM orders
WHERE customer_id = 12345
  AND ordered_at >= '2025-06-01';
```

```text
 Index Scan using idx_orders_bad on orders  (cost=0.42..17240..  rows=6 width=16)
                                            (actual time=0.20..96.4 rows=7 loops=1)
   Index Cond: ((ordered_at >= '2025-06-01 00:00:00+09') AND (customer_id = 12345))
   Buffers: shared hit=1893
 Planning Time: 0.12 ms
 Execution Time: 96.5 ms
```

一見どちらも `Index Cond` に両条件が並ぶが、**先頭列が範囲**だと索引は `ordered_at >= '2025-06-01'` の位置にしか降りられない。以降は `ordered_at` が 6月以降のエントリを**すべて順に舐めながら**、各エントリで `customer_id = 12345` を突き合わせる。結果の7行は同じでも、途中で触るエントリ数が桁違いに増える（例: `Buffers: shared hit=1893`、`Execution Time` はミリ秒オーダー）。先頭に範囲列を置いた瞬間、後続列は「絞り込みの入口」ではなく「1件ずつの再チェック」に格下げされる。これが列順の実害である（実測 ms・行数は環境依存なので上の数字はあくまで例）。

原則をまとめる。

- 等値（`=`, `IN`）で使う列を左に寄せ、**範囲（`>=`, `<`, `BETWEEN`）は最後の1列だけ**にする。
- 範囲列の右に別の列を足しても、その列は seek には使われない（フィルタ扱い）。
- ついでに、`(customer_id, ordered_at)` は `WHERE customer_id = ? ORDER BY ordered_at` の**並べ替えも索引順で満たせる**ため、Sort ノードを省ける。

「全列にとりあえず索引」を貼るのは、この列順の意味を無視した典型的な失敗である。効くのは**先頭列から連続して条件に使える**索引だけで、余った索引は更新コストを増やすだけになる。

### カバリング索引（INCLUDE）

葉ノードから TID を得たあとにヒープを読む往復が、行数が多いと効いてくる。`SELECT` する列を索引の葉に**同梱**しておけば、ヒープを読まずに索引だけで答えられる。これが **Index Only Scan** で、`INCLUDE` で実現する。

```sql
-- 検索キーは (customer_id, ordered_at)、返す列 status を葉に同梱
CREATE INDEX idx_orders_cov
  ON orders (customer_id, ordered_at) INCLUDE (status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, ordered_at, status
FROM orders
WHERE customer_id = 12345
  AND ordered_at >= '2025-06-01';
```

```text
 Index Only Scan using idx_orders_cov on orders  (actual time=0.02..0.03 rows=7 loops=1)
   Index Cond: ((customer_id = 12345) AND (ordered_at >= '2025-06-01 00:00:00+09'))
   Heap Fetches: 0
   Buffers: shared hit=4
```

`INCLUDE` 列は**探索・並び替えには使われず、葉に値を持つだけ**の列である。`WHERE` に使うキー列は左側の `()` に、返すだけの列は `INCLUDE` に置く。`Heap Fetches: 0` ならヒープ往復ゼロで答えている。ただし Index Only Scan が効くには**可視性マップ**（どのページが全可視かの地図）が最新である必要があり、更新直後は `Heap Fetches` が増える。定期的な `VACUUM`（自動バキュームで足りることが多い）が前提になる。

### 部分索引（partial index）

条件に合う行だけを索引に載せるのが**部分索引**である。`orders.status` は大半が `delivered` で `pending` はごく一部、という偏りがあるとき、`pending` だけの索引は小さく速い。

```sql
CREATE INDEX idx_orders_pending
  ON orders (ordered_at)
  WHERE status = 'pending';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders
WHERE status = 'pending'
  AND ordered_at >= '2025-06-01';
```

クエリの `WHERE` が索引の述語（`status = 'pending'`）を**含む**とき、プランナはこの索引を使える。索引は `pending` の行だけを持つので本体が小さく、`delivered` の大量行を索引に載せる無駄がない。「稼働中の注文だけ速く引きたい」「論理削除フラグが false の行だけ」など、偏った検索対象に有効である。

### 索引の種類の使い分け

B-tree 以外にも用途特化の索引がある。対象データの形で選ぶ。名前はいずれも実装方式の略で、GIN は Generalized Inverted Index（汎用転置索引）、GiST は Generalized Search Tree（汎用検索木）、BRIN は Block Range Index（ブロック範囲索引）である。

| 種類 | 得意な検索 | 本スキーマでの例 |
| --- | --- | --- |
| **B-tree** | 等値・範囲・ソート・前方一致（前方一致にはオペレータクラスか collation の指定が要る。後述） | `orders(customer_id, ordered_at)`、主キー全般 |
| **Hash** | 等値（`=`）のみ | 使いどころは限定的。等値専用でも B-tree で足りることが多い |
| **GIN** | 「1つの値の中に複数要素」を含む検索。配列・`jsonb`・全文検索・`pg_trgm` によるあいまい/中間一致。`pg_trgm` は文字列を3文字の断片（trigram）に分けて索引する拡張で、これにより中間一致を「断片を含むか」の検索に変換できる | `events` の `jsonb` 属性検索、`products.name LIKE '%中間%'` を `pg_trgm` で |
| **GiST** | 範囲型・幾何・近傍検索（KNN: k-Nearest Neighbor）・排他制約 | 第6回の `tstzrange` × `EXCLUDE USING gist`（価格の有効期間の重複禁止） |
| **BRIN** | 物理順に自然に並ぶ巨大テーブルの範囲検索。索引が極小 | `events(occurred_at)`（追記のみで時刻順に積まれる1000万行） |

要点は、**前方一致・完全一致・範囲・ソートは B-tree、中間一致やあいまい検索は GIN + `pg_trgm`、追記型の巨大ログは BRIN、範囲型の重複禁止は GiST** という対応である。第6回で使った有効期間モデルの排他制約が GiST だったのは、`tstzrange` の「重なる（`&&`）」を索引で扱えるのが GiST だからである。

### 照合順序と索引 ― 前方一致検索の実害

ここがこの回の核心である。`text` 列に**既定の索引が張ってあっても**、`LIKE 'abc%'` の前方一致が **Seq Scan** に落ちることがある。原因は**照合順序**（collation）にある。

第1回で見たとおり、既定の collation（glibc の `ja_JP.UTF-8` や ICU の `ja-x-icu` など、C 以外）は**言語的な並び順**を定める。言語的順序は「基底文字 → アクセント → 大小文字」といった多段の重みで決まり、空白や記号の扱いも単純なバイト順とは一致しない。その結果、「`abc` で始まる文字列の集合」は collation 順では**連続した一区間にならない**。

B-tree で `LIKE 'abc%'` を索引にかけるには、内部で `col >= 'abc' AND col < 'abd'` のような**範囲条件へ変換**する必要がある。この変換が正しいのは、`abc` で始まる文字列が必ず `'abc'` 以上 `'abd'` 未満に**連続して並ぶ**ときだけ。C 以外の collation ではこの連続性が保証されないため、プランナは変換をあきらめ、既定索引を使わず全行を舐める。

対して **C collation** は**生のバイト列の比較**（memcmp）で並ぶ。バイト順なら「`abc`（バイト列）で始まる文字列」は必ず連続するので、範囲変換が成立し索引が効く。効かせる手は2つ。

まず、既定索引では効かないことを確認する（`customers.email` にはスキーマの `UNIQUE` 制約由来の既定 B-tree が既にある）。

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM customers
WHERE email LIKE 'user1%';   -- プレフィックスは実データに合わせて選ぶ
```

```text
 Seq Scan on customers  (actual time=0.03..21.4 rows=... loops=1)
   Filter: (email ~~ 'user1%'::text)
   Rows Removed by Filter: 49xxx
   Buffers: shared hit=...
```

既定の一意索引があるのに `Seq Scan`。`email ~~ 'user1%'` が `Filter` に落ち、全行を読んでいる。

**手段(a): 索引に `COLLATE "C"` を付ける。**

```sql
CREATE INDEX idx_email_c ON customers (email COLLATE "C");

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM customers
WHERE email LIKE 'user1%';
```

```text
 Bitmap Heap Scan on customers  (actual time=0.05..0.31 rows=... loops=1)
   Filter: (email ~~ 'user1%'::text)
   ->  Bitmap Index Scan on idx_email_c  (actual time=0.03..0.03 rows=... loops=1)
         Index Cond: ((email >= 'user1'::text) AND (email < 'user2'::text))
```

`Index Cond` が `>= 'user1' AND < 'user2'` という**範囲に変換**され、C collation の索引が使われている（この索引はバイト順で並ぶため範囲変換が成立する）。`LIKE` 自体は `Filter` で最終確認されるが、走査対象は範囲内に絞られている。

**手段(b): `text_pattern_ops` オペレータクラスで索引を作る。** オペレータクラス（opclass）とは、その索引が値をどの演算子でどう比較するかを決める規則の組である。同じ B-tree でも、どの opclass を指定するかで並び順の基準そのものを差し替えられる。

```sql
CREATE INDEX idx_email_pat ON customers (email text_pattern_ops);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM customers
WHERE email LIKE 'user1%';
```

`text_pattern_ops` は「collation を無視して文字（バイト）単位で比較する」演算子クラスで、列の既定 collation が何であってもパターンマッチの範囲変換を成立させる。EXPLAIN では手段(a)と同様に、範囲へ変換された `Index Cond` でこの索引が使われる。

両手段の注意点は同じで、**役割が分かれる**こと。

| 索引 | `LIKE 'abc%'`（前方一致） | 通常の `<`, `>`, `ORDER BY`（既定 collation） | 等値 `=` |
| --- | --- | --- | --- |
| 既定 opclass（非 C collation） | 効かない | 効く | 効く |
| `COLLATE "C"` 索引 | 効く | 効かない（並び順が違う） | 効く |
| `text_pattern_ops` 索引 | 効く | 効かない | 効く |

つまりパターン検索と通常の範囲/整列の両方を索引で速くしたいなら、**既定 opclass の索引と、`text_pattern_ops`（または `COLLATE "C"`）の索引を別々に張る**。1つの列に用途別の索引を複数持てる。

### 関数を掛けた列に索引が効かない ― 式インデックス

`WHERE lower(email) = 'foo@example.com'` のように**列に関数を掛ける**と、`email` の索引は使えない。索引が持つのは `email` の値であって `lower(email)` の値ではないからだ。解決は**式インデックス**（expression index）。式そのものに索引を張る。

```sql
-- 大文字小文字を無視した完全一致・前方一致の両対応
CREATE INDEX idx_email_lower_pat
  ON customers (lower(email) text_pattern_ops);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM customers
WHERE lower(email) LIKE 'user1%';
```

クエリの式（`lower(email)`）が索引の式と一致するとき、プランナはこの索引を使える。`text_pattern_ops` を併せて指定したので、`lower(email)` に対する前方一致も範囲変換で効く。式インデックスは「常に同じ関数を通して検索する列」（正規化したメール、`date_trunc` した日付など）に有効だが、その関数が **IMMUTABLE** であることが条件になる。`IMMUTABLE` とは「同じ引数なら常に同じ結果を返し、DB の中身も設定も読まない」と宣言された関数のことで、そうでなければ索引に格納した値が後から食い違いうるため索引を張れない。`lower()` は IMMUTABLE だが、`now()` やセッションのタイムゾーンに依存する変換はそうではない。

## ハンズオン

### 課題1: 複合索引の列順で実測差を出す

`orders` に対し `WHERE customer_id = ? AND ordered_at >= ?` を、列順の異なる2索引で比べ、走査量の差を数字で見る。

やること: 良い順の索引を作って計測 → 落として悪い順を作り直して計測 → `Buffers` と `Execution Time` を比較する。

**想定解答**

```sql
-- 1) 等値→範囲（良い順）
CREATE INDEX idx_orders_good ON orders (customer_id, ordered_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at FROM orders
WHERE customer_id = 12345 AND ordered_at >= '2025-06-01';
-- Index Scan、Index Cond に両条件、Buffers 数ページ、実行 0.x ms（例）

-- 2) 範囲→等値（悪い順）に貼り替え
DROP INDEX idx_orders_good;
CREATE INDEX idx_orders_bad ON orders (ordered_at, customer_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at FROM orders
WHERE customer_id = 12345 AND ordered_at >= '2025-06-01';
-- Index Scan だが Buffers が桁違いに多い、実行が数十 ms（例）
```

なぜ解けるか: 良い順は `customer_id = 12345` の位置へ一度降り、その中の `ordered_at >= '2025-06-01'` を連続区間として読む。悪い順は `ordered_at` が6月以降のエントリを全部舐めながら `customer_id` を1件ずつ照合するため、返す行数が同じでも走査エントリ数が跳ね上がる。`Buffers: shared hit` の差がその走査量の差を表す。計測後は不要な索引を `DROP INDEX idx_orders_bad;` で片付ける。

### 課題2: 照合順序で LIKE の実行計画を作り分ける

同じ `email LIKE 'abc%'` が、索引の作り方で Seq Scan にも Index/Bitmap Scan にもなることを再現する。

やること: (1) 既定索引だけの状態で Seq Scan を確認、(2) `COLLATE "C"` 索引で効かせる、(3) `text_pattern_ops` 索引でも効かせる、(4) `lower(email)` を掛けた検索が効かない→式インデックスで解決、を EXPLAIN で並べる。プレフィックスは実データに存在するものを選ぶ。

**想定解答**

```sql
-- (1) 既定の一意索引はあるが LIKE 前方一致では使われない
EXPLAIN SELECT id FROM customers WHERE email LIKE 'user1%';
--   -> Seq Scan（Filter: email ~~ 'user1%'）

-- (2) COLLATE "C" 索引で効く
CREATE INDEX idx_email_c ON customers (email COLLATE "C");
EXPLAIN SELECT id FROM customers WHERE email LIKE 'user1%';
--   -> Bitmap/Index Scan（Index Cond: email >= 'user1' AND email < 'user2'）

-- (3) text_pattern_ops 索引でも効く
DROP INDEX idx_email_c;
CREATE INDEX idx_email_pat ON customers (email text_pattern_ops);
EXPLAIN SELECT id FROM customers WHERE email LIKE 'user1%';
--   -> Bitmap/Index Scan（範囲に変換された Index Cond）

-- (4) 関数を掛けると上の索引は効かない → 式インデックス
EXPLAIN SELECT id FROM customers WHERE lower(email) LIKE 'user1%';
--   -> Seq Scan（email の索引は lower(email) を持たない）
CREATE INDEX idx_email_lower_pat ON customers (lower(email) text_pattern_ops);
EXPLAIN SELECT id FROM customers WHERE lower(email) LIKE 'user1%';
--   -> Index Scan（lower(email) の式索引が使われる）
```

なぜ解けるか: 非 C collation では「`user1` で始まる文字列」が並び順で連続しないため、既定索引は前方一致を範囲に変換できず Seq Scan になる。`COLLATE "C"` はバイト順、`text_pattern_ops` は collation 非依存のバイト比較で、いずれも連続性が保証され範囲変換が成立する。`lower(email)` は列の値と別物なので、式そのものに索引を張って初めて一致する。

## よくあるつまずきと対処

| 症状 | 原因 | 対処 |
| --- | --- | --- |
| 全列に索引を貼ったのに遅い | 複合索引の列順を無視。範囲列を先頭に置いた／条件と先頭列が噛み合わない | 等値で絞る列を先、範囲は最後の1列。`EXPLAIN` で `Index Cond` に両条件が入るか確認 |
| `lower(col)` や `col::date` で検索して Seq Scan | 列に関数を掛けると素の列の索引は使えない | 式インデックス `CREATE INDEX ... (lower(col))` を張る。関数は IMMUTABLE であること |
| `LIKE 'abc%'` が Seq Scan に落ちる | 非 C collation の既定索引は前方一致を範囲変換できない | 索引を `COLLATE "C"` か `text_pattern_ops` で作る。通常の範囲/整列も要るなら既定索引と併用 |
| `LIKE '%abc%'`（中間一致）が効かない | 前方一致でないので B-tree では原理的に無理 | `pg_trgm` 拡張＋GIN 索引を使う（別テーマ） |
| `INCLUDE` したのにヒープを読む（`Heap Fetches` が多い） | 可視性マップが古く Index Only Scan が完全に効かない | `VACUUM`（自動バキューム）を待つ／更新直後の計測は避ける |

エラーではなく「動くが遅い」形で出るのがこの回のつまずきの厄介なところで、`EXPLAIN` を見て初めて気づく。索引を作ったら必ず計画を確認する癖をつける。

## 到達度チェックリスト

- [ ] ページ・ヒープ・タプル・TID の関係を図または言葉で説明できる。
- [ ] `ctid` が何を表すか説明でき、`SELECT ctid` で確認した。
- [ ] B-tree の根・内部・葉の役割と、葉→ヒープの TID 参照を説明できる。
- [ ] 複合索引を「等値→範囲」で設計でき、列順の良し悪しを `EXPLAIN` で読み分けた。
- [ ] `INCLUDE` によるカバリング索引と部分索引を、使う場面ごと説明できる。
- [ ] B-tree / GIN / GiST / BRIN の使い分けを対象データで選べる。
- [ ] 非 C collation で `LIKE 'abc%'` が Seq Scan に落ちる理由を説明でき、`COLLATE "C"` と `text_pattern_ops` の両方で効かせた。
- [ ] 関数を掛けた列に式インデックスを張って効かせた。

## 宿題（次回までの自習）

1. `orders(customer_id, ordered_at)` と `orders(ordered_at, customer_id)` で、`WHERE customer_id = ? AND ordered_at BETWEEN ? AND ?` の `EXPLAIN (ANALYZE, BUFFERS)` を取り、`Buffers` と実行時間を before/after 表にまとめる。
2. `products.name` に対する `LIKE '%キーワード%'`（中間一致）を、`CREATE EXTENSION pg_trgm;` と GIN 索引で速くできるか試す。前方一致との違いを一言で説明する。
3. `events(occurred_at)`（1000万行・時刻順の追記型）に BRIN 索引を張り、月範囲の集計で B-tree とサイズ・速度をどう比べるか予想を書く（第13回のパーティションと BRIN の相性に接続する）。

## 次回への接続

索引を作り分けても、実際に**使われているか**はプランナ次第である。[第8回](08-explain.md)では `EXPLAIN (ANALYZE, BUFFERS)` を虫眼鏡として、「なぜ索引が使われない／Seq Scan が選ばれる」を統計情報とコスト見積もりから言語化する。この回で作った良い索引・悪い索引が、そこで格好の教材になる。照合順序そのものの決め方は[第1回](01-setup-encoding.md)に、範囲型 × GiST の排他制約は[第6回](06-modeling.md)に戻って確認できる。

## 参考

- PostgreSQL 公式ドキュメント「Indexes」章（Index Types / Multicolumn Indexes / Indexes and ORDER BY / Combining Multiple Indexes / Unique Indexes / Indexes on Expressions / Partial Indexes / Index-Only Scans and Covering Indexes / Examining Index Usage）。
- 同「Operator Classes and Operator Families」章（`text_pattern_ops`・`varchar_pattern_ops`）。
- 同「Collation Support」章、および「Locale Support」章（C ロケールとパターンマッチの関係）。
- 同「Database Physical Storage」章（Page Layout / Heap タプルの構造）。
- 追加索引の内部: 「B-Tree Indexes」「GIN Indexes」「GiST Indexes」「BRIN Indexes」各章。