PostgreSQL インデックス設計 チュートリアル

データベースのパフォーマンスは、クエリの書き方以上に「インデックス設計」で決まります。

インデックス設計とは

データベースにおけるインデックス設計とは、膨大なデータから目的の情報を素早く見つけるための「索引」を、どのデータに対して、どのように作成するかを計画することです。インデックスを適切に設計すると、検索速度が劇的に向上しますが、闇雲に作成すると逆にシステムのパフォーマンスを下げてしまうこともあります。

インデックスの仕組み

索引がない場合（フルスキャン）
例えば、1,000ページある技術書から「インデックス」という単語を探したいとします。索引がなければ、1ページ目から順番に最後まで読んでいくしかありません。これをデータベースではフルテーブルスキャンと呼びます。データ量が増えるほど、探す時間は膨大になります。
索引がある場合（インデックススキャン）
巻末の索引で「あ行」の「インデックス」を探せば、「324ページにある」と即座にわかります。辞書本体を全部読まなくても、数ページめくるだけで目的の場所にたどり着けます。これがインデックスの役割です。

「設計」とは

全ての項目に索引をつければ良いのではないかと思うかもしれませんが、それではうまくいかない理由が3つあります。

• 書き込み速度の低下:
  データを追加・更新するたびに、索引も書き換える必要があります。索引が多すぎると、本編の更新よりも索引の更新に時間がかかってしまいます。
• 容量の消費:
  索引自体もデータです。作りすぎるとストレージを圧迫します。
• 効率の悪い索引:
  「性別」のように種類が少ない項目（男・女のみなど）に索引をつけても、結局半分くらいのデータを見ることになり、あまり速くなりません。

データベースに対して適切にインデックスを「設計」することが、パフォーマンスを向上させるために必要なのです。

インデックスを作成すべきパターン

レコード数の多いテーブル
レコード数の少ないテーブルではそもそもフルスキャンした方が高速な場合があります。1万レコード以下であればインデックスの効果は薄いです。しかし、サーバーのスペックなどにもよるため実測しながら決定することが望ましいです。

カーディナリティの高いカラム
カーディナリティとは「値の分散度」を示し、これがインデックスを作る際に最も重要な指針となります。特定の列について多くの種類の値を持っていればカーディナリティが高く、少なければカーディナリティが低いということを指します。例えば、性別や都道府県のようにパターン数が決まっている項目はインデックスの効果が望みにくいですが、社員番号やメールアドレスといった重複しない項目はインデックスの効果が高くなります。

SQLの抽出条件や結合条件に使用されるカラム
SQLを発行する際、以下のように使われるカラムにインデックスを作成することで効果を発揮します。

• WHERE句に使用されるカラム
• テーブル結合でON句に使用されるカラム
• ORDER BY句に使用されるカラム

逆に言えば検索条件や結合条件に利用されないカラムにインデックスを作成するのは無意味です。

事前準備：SQL演習環境の構築(任意)

SQL演習環境を作成する場合は以下の手順を実行してください。

Docker

1. Docker Desktopの準備: Docker Desktopをインストールし、起動状態（Running）にしておきます。
2. イメージの取得: 公式リポジトリ（Docker Hub）からPostgreSQLとDbGateのイメージを取得（pull）します。

docker image pull postgres:17.6
docker image pull dbgate/dbgate:6.6.3

3. コンテナの作成と起動: docker container runコマンドを使用し、環境変数やポートマッピング（-p 5432:5432）、バックグラウンド実行（-d）を指定してコンテナを起動します。

docker container run --name pg17 -e POSTGRES_USER=student -e POSTGRES_PASSWORD=secret123 -e POSTGRES_DB=playground -p 5432:5432 -d postgres:17.6

SQL演習用環境

1. リポジトリのクローン: 演習用教材のGitリポジトリ（https://github.com/TakeshiWada1980/DB-2025-PostgreSQL.git）をローカルに複製し、VSCodeで開きます。
2. 依存関係のインストール: npm iコマンドを実行し、プロジェクトに必要なライブラリをインストールします。
3. 環境変数の設定: プロジェクトルートに.envファイルを作成し、PostgreSQLへの接続文字列を記述します。

DATABASE_URL="postgresql://student:secret123@localhost:5432/playground?schema=public"

4. コンテナの起動: npm run db:upコマンドを実行します。これにより、PostgreSQLとDbGate（DB管理ツール）のコンテナがまとめて起動します。

事前準備：データ生成 (init_data.sql)

まずは実験台となる100万件の注文履歴データを作成します。2章の演習でも同じ orders テーブルを使用します。

貼り付けて実行してください。

-- 100万件のダミーデータ生成スクリプト
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    region VARCHAR(50) NOT NULL
);
INSERT INTO orders (customer_id, order_date, status, total_amount, region)
SELECT 
    (random() * 10000)::INTEGER + 1,
    CURRENT_DATE - (random() * 365 * 3)::INTEGER,
    (ARRAY['pending', 'processing', 'shipped', 'delivered', 'cancelled'])[floor(random() * 5 + 1)],
    (random() * 10000)::DECIMAL(10, 2),
    (ARRAY['Tokyo', 'Osaka', 'Nagoya', 'Fukuoka', 'Sapporo'])[floor(random() * 5 + 1)]
FROM generate_series(1, 1000000);
ANALYZE orders;

SQLの意味は次の通りです。

customer_id: 10,000倍して整数化 → 1〜10,000のランダムな顧客ID

order_date: 今日を起点として0〜3年分の日付をランダムに減算
→ 過去3年程度のランダムな注文日

status: pending / processing / shipped / delivered / cancelled のいずれかをランダムに付与

total_amount: 0〜10,000円程度のランダムな金額

region: Tokyo / Osaka / Nagoya / Fukuoka / Sapporo のいずれかをランダムに付与

ANALYZE orders;

パフォーマンスの計測

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5000;

START TRANSACTION;
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
ANALYZE orders;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5000;
ROLLBACK;

1. B-Treeのデータ構造について

インデックスがない場合、DBは1枚ずつページをめくって探します（全件走査：Seq Scan）。100万件なら100万回のステップが必要です。

B-Treeインデックスは、データを木構造で整理します。100万件あっても、わずか数回の「枝分かれ」を辿るだけで目的のデータに辿り着きます。

B-Treeのデータ構造について、構成要素・検索処理・ノード分割とマージの観点で整理します。

B-Treeの構成要素

B-Treeは、ルートノード、内部ノード、リーフノードという3つのタイプのノードによって構成される階層的なデータ構造です。

階層構造の頂点に位置するのがルートノードであり、このノードは親を持たず、木構造の出発点となります。

階層の最下部に存在するリーフノードは、子ノードを持ちません。

内部ノードは、ルートノードとリーフノードの中間に配置されるノードで、効率的なデータ検索を実現するための案内役として機能します。

B-Treeの各ノードには、複数のキー値とポインタが保存されます。キー値は常にソート済みの状態で保持され、ノード内では昇順に配列されています。例として、従業員IDをキーとして利用する際には、ノード内のキー値は小さい値から順に配列されます。

各ノードに保存可能なキーの個数には、上限値と下限値が定められています。この上限値と下限値は、B-Treeの次数（degree）により決まります。次数mのB-Treeでは、各ノードは最小(m/2)-1個、最大(m-1)個のキーを保持できます。ただし、ルートノードについては例外的に、最小1個のキーを保持すればよいとされています。

ノード内のキー値は、その子ノードが保持するキー値の範囲を表します。あるキー値kを保持するノードの場合、その左側の子ノードにはkより小さい値のキーが、右側の子ノードにはkより大きい値のキーが保存されます。このルールはB-Tree全体において一貫して適用されています。

具体例として、ノードに[10, 20, 30]というキー値が保存されている場合、このノードは4つの子ノードを保持します。最左の子ノードには10未満のキー値、2番目の子ノードには10以上20未満のキー値、3番目の子ノードには20以上30未満のキー値、最右の子ノードには30以上のキー値が保存されます。

このように、B-Treeは厳密なルールに基づいて構造化されており、これによって効率的なデータ検索が実現されています。

B-Treeにおける検索処理

B-Treeでのデータ検索は、ルートノードを起点として、目標とするデータが発見されるまで、あるいはデータの非存在が判明するまで、段階的に下層のノードへと移動していきます。この検索処理について、具体的な手順を説明します。

検索の最初のステップは、ルートノードでの照合です。ルートノードでは、検索対象のキー値と、ノード内に保存されているキー値を照合します。ノード内のキー値はソートされているため、二分探索を利用して効率的に照合を実施できます。

具体例として、従業員ID「22」を検索するケースを考えます。ルートノードに[10, 20, 30]というキー値が保存されているとすると、まず「22」と中央の値「20」を照合します。「22」は「20」より大きいため、次に「30」との照合を実施します。「22」は「30」より小さいため、3番目の子ノード(「20」と「30」の間に対応する子ノード)へ検索を進めます。

この処理は、子ノードにおいても同様に繰り返されます。各ノードでは、保存されているキー値との照合を実施し、適切な子ノードを選んで検索を継続します。例として、次のノードに[21, 22, 23]というキー値が保存されている場合、検索対象の「22」が発見されたため、検索はここで完了します。

検索対象のキー値が存在しないケースでは、検索はリーフノードまで到達します。リーフノードでキー値が発見できない場合、そのキー値はB-Tree内に存在しないことが判明します。

B-Treeの重要な性質として、すべてのパス(ルートノードからリーフノードまでの経路)の長さが等しいことが挙げられます。これにより、どのデータを検索するケースでも、ルートノードからリーフノードまでの移動回数は同一になります。この性質により、検索時間の予測が容易になり、安定したパフォーマンスを提供できます。

B-Treeの検索パフォーマンスは、木の高さに大きく依存します。例として、1000万件のデータを保存するB-Treeであっても、1ノードあたり100個のキーを保存できるケースでは、高さは4程度に抑えられます。これは、各レベルでの検索に必要な照合回数が最大でも7回程度(100個のキーに対する二分探索)であることを意味します。

-- 1. インデックスを作成（未作成の場合）
CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);
ANALYZE orders;

-- 2. 拡張機能を有効化
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- 3. インデックスのメタデータを見て、木の深さ(level)を確認する
SELECT * FROM bt_metap('idx_orders_customer_id');

B-Treeのノード分割とマージの処理

B-Treeでは、データの追加や削除に伴い、ノードの分割やマージが実施されます。これらの処理は、B-Treeのバランスを維持し、効率的な検索パフォーマンスを保つために重要な役割を担います。

ノードの分割は、新規データを追加する際にノードが最大容量に到達した場合に実施されます。例として、最大4つのキーを保存できるノードに、すでに4つのキー[10, 20, 30, 40]が保存されている状態で、新規キー「25」を追加するケースを考えます。このノードはすでに最大容量に到達しているため、分割が必要になります。

分割の処理は以下のように実施されます。まず、ノード内のキーを順序に従ってソートします。この例では[10, 20, 25, 30, 40]となります。次に、この5つのキーの中央値「25」を選択し、これを親ノードへ昇格させます。残りのキーのうち、「25」より小さいキー[10, 20]は元のノードに保持し、大きいキー[30, 40]は新しく生成したノードへ移動させます。

ノードのマージは、データの削除によりノードのキー数が最小容量を下回った場合に実施されます。例として、最小2つのキーが必要なノードで、キーを削除した結果、1つのキーしか残らなくなったケースです。

マージの処理では、まず隣接するノードを確認します。そのノードに十分な空き容量がある場合、両方のノードのキーを1つのノードに統合します。この際、親ノードから適切なキーを降格させ、マージ後のノードに含めます。

具体例として、2つの隣接するノードがあり、一方に[10]、もう一方に[30, 40]というキーが保存されており、親ノードに「20」というキーがあるケースでは、マージ後は1つのノードに[10, 20, 30, 40]というキーが保存されます。

これらの分割とマージの処理は、必要に応じて親ノードにも伝播していきます。つまり、親ノードでも同様の分割やマージが実施される可能性があります。この伝播は、必要に応じてルートノードまで継続されます。

1.1 B+Treeの構造とメリット

これまで、B-Treeについての説明をしましたが、データベースにおいて主流なのは「B+Tree」というものです。B+TreeはB-Treeをさらにデータベースの検索（特に範囲検索）に特化させた進化形です。

B+Treeとは

B+Treeは、B-Treeの構造をベースにしつつ、「データの持ち方」と「ノード間のつながり」を改良したデータ構造です。現在のリレーショナルデータベース（MySQLのInnoDB、PostgreSQLなど）のインデックスにおいて、最も一般的に採用されています。

B-Treeとの違い

B-TreeとB+Treeの最大の違いは、「サテライトデータ（実際のレコードやポインタ）」をどこに配置するかにあります。

• データはリーフノード（最下層）にのみ集約: B-Treeでは各内部ノードもデータを持っていましたが、B+Treeでは内部ノードは「検索用のキー（枝分かれの指標）」のみを保持し、実際のデータ（またはデータへのポインタ）はすべて最下層のリーフノードに格納します。
• リーフノード同士が連結リストでつながっている: これが実務上の最大の利点です。隣り合うリーフノードがポインタで結ばれているため、ツリーを昇り降りすることなく、横方向にデータをスキャンできます。
• キーの重複: 内部ノードにあるキーはあくまで「道しるべ」なので、同じキー値がリーフノードにも存在します。

データベース設計におけるメリット

なぜB-TreeではなくB+Treeが選ばれるのか、その理由は運用の効率性にあります。

1. 範囲検索（Range Scan）が圧倒的に速い
   B-Treeで「10から20までのデータを取得する」場合、何度もツリーを上下に移動（再帰的な探索）する必要があります。B+Treeなら、「10」を一度見つけたら、あとはリーフノードを横にたどるだけで「20」まで辿り着けます。これが SELECT * FROM table WHERE age BETWEEN 20 AND 30 といったクエリが高速な理由です。
2. キャッシュ効率（入出力効率）が良い
   内部ノードにデータを持たないため、1つのノード（ディスクブロック）により多くの「キー」を詰め込むことができます。分岐数が増えることでツリーの高さが低くなり、1回のディスクI/Oで読み込めるインデックス情報が増えるため、検索性能が安定します。
3. 全走査（Full Scan）の簡便さ
   全データを走査する場合、ツリー全体をなぞる必要はなく、リーフノードの端から端までをなぞるだけで済みます。

2. 複合インデックスの設計と最適化

2.1 複合インデックスを使用する理由

単一カラムインデックスの限界

データベースで検索を行う際、多くの場合、複数の条件を組み合わせて検索します。例えば、ECサイトで「東京都内で、配送済みの、2024年1月以降の注文」を検索する場合、region、status、order_dateという3つの条件が必要です。

もし各カラムに個別のインデックスがあったとしても、PostgreSQLは基本的に1つのクエリで1つのインデックスしか使用しません（Bitmap Index Scanを除く）。つまり、regionのインデックスだけを使って該当する行を絞り込んだ後、残りの条件は実際のテーブルデータを読み込んでフィルタリングする必要があります。

複合インデックスを使用することで、複数の条件を同時にインデックス内で処理でき、テーブル本体へのアクセスを最小限に抑えられます。

複合インデックスの内部構造

複合インデックスは、指定した順序でカラムを連結したキーとして構築されます。例えば (region, status, order_date) というインデックスは、内部的に以下のように整列されています:

('Fukuoka', 'cancelled', '2024-01-15')
('Fukuoka', 'cancelled', '2024-01-20')
('Fukuoka', 'delivered', '2024-01-10')
('Fukuoka', 'delivered', '2024-01-18')
('Fukuoka', 'pending', '2024-01-05')
('Osaka', 'cancelled', '2024-01-12')
('Osaka', 'delivered', '2024-01-08')
...
('Tokyo', 'shipped', '2024-01-25')
('Tokyo', 'shipped', '2024-01-30')

この構造から分かるように、第1カラムでまず大きくグループ化され、その中で第2カラムがソートされ、さらにその中で第3カラムがソートされるという階層的な構造になっています。

2.2 カラム順序の決定原則

複合インデックスで最も重要なのはカラムの順序です。順序を間違えると、インデックスが全く使われない、あるいは効率が大幅に低下する可能性があります。

原則1: 等価条件(=)を優先的に先頭に配置する

検索条件には「等価条件」（column = value）と「範囲条件」（column > value、BETWEEN、LIKE 'prefix%'）があります。

等価条件は、インデックス内で該当する範囲を一点に絞り込むことができます。例えば region = 'Tokyo' という条件は、インデックス内の「Tokyoで始まる部分」だけを対象にします。
一方、範囲条件は、該当する範囲全体をスキャンする必要があります。order_date >= '2024-01-01' という条件は、該当日以降のすべての行を確認しなければなりません。

-- 良い例: 等価条件(status)を先頭に
CREATE INDEX idx_good ON orders(status, order_date);
ANALYZE orders;

-- このクエリは効率的
SELECT * FROM orders 
WHERE status = 'shipped'  -- 等価条件でまず絞り込み
  AND order_date >= '2024-01-01';  -- 残りの範囲を検索

「idx_good」を使用すると、インデックス内で「statusが'shipped'」の部分だけを特定し、その中でorder_dateの条件に合う行を探します。

-- 悪い例: 範囲条件(order_date)を先頭に
CREATE INDEX idx_bad ON orders(order_date, status);
ANALYZE orders;

-- 同じクエリでも非効率
SELECT * FROM orders 
WHERE status = 'shipped'
  AND order_date >= '2024-01-01';

「idx_bad」を使用すると、order_date >= '2024-01-01' の範囲全体をスキャンし、その後各行でstatus = 'shipped'かどうかを確認する必要があります。

-- 既存インデックスを削除し、悪い順序で作成
DROP INDEX IF EXISTS idx_good;
DROP INDEX IF EXISTS idx_bad;
CREATE INDEX idx_bad ON orders(order_date, status);
ANALYZE orders;

-- 実行計画と実行時間を確認
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'shipped' AND order_date >= '2024-01-01';

-- 悪いインデックスを削除し、良い順序で作成
DROP INDEX idx_bad;
CREATE INDEX idx_good ON orders(status, order_date);
ANALYZE orders;

-- 同じクエリで実行計画と実行時間を確認
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'shipped' AND order_date >= '2024-01-01';

原則2: 選択性(多重度)が高いカラムを優先

選択性とは、「そのカラムの条件でどれだけデータを絞り込めるか」を示す指標です。選択性が高いほど、少ない行数に絞り込めます。

選択性 = 該当する行数 / 全体の行数
値が小さいほど選択性が高く、効率的に絞り込めます。

カーディナリティが高いカラムは、通常、選択性も高くなります。

例: customer_id: 10,000種類の値（カーディナリティ高）→ 各値で約100行に絞り込める
status: 5種類の値（カーディナリティ低）→ 各値で約20万行に絞り込める

100万行のテーブルで customer_id = 5000 を検索すると約100行に絞り込めますが、status = 'shipped' では約20万行が該当します。

-- カーディナリティの確認
SELECT 
    COUNT(DISTINCT customer_id) as customer_cardinality,
    COUNT(DISTINCT status) as status_cardinality,
    COUNT(DISTINCT region) as region_cardinality,
    COUNT(*) as total_rows
FROM orders;

適用例:

-- 選択性を考慮した順序
CREATE INDEX idx_selective ON orders(customer_id, status, order_date);
ANALYZE orders;
-- customer_id(高選択性) → status(低選択性) → order_date(範囲)

-- このクエリは効率的
SELECT * FROM orders
WHERE customer_id = 5000    -- まず100行程度に絞り込み
  AND status = 'shipped'    -- さらに20行程度に
  AND order_date >= '2024-01-01';  -- 最終的に数行に

SELECT 
    COUNT(DISTINCT customer_id) as customer_cardinality,
    COUNT(DISTINCT status) as status_cardinality,
    COUNT(*) as total_rows
FROM orders;

-- statusを先頭にしたインデックス（選択性が低い）
DROP INDEX IF EXISTS idx_selective;
DROP INDEX IF EXISTS idx_low_selective;
CREATE INDEX idx_low_selective ON orders(status, customer_id);
ANALYZE orders;

EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE customer_id = 5000 AND status = 'shipped' AND order_date >= '2024-01-01';

-- customer_idを先頭にしたインデックス（選択性が高い）
DROP INDEX idx_low_selective;
CREATE INDEX idx_selective ON orders(customer_id, status, order_date);
ANALYZE orders;

EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE customer_id = 5000 AND status = 'shipped' AND order_date >= '2024-01-01';

原則3: 使用頻度を考慮（最左プレフィックスルール）

すべてのクエリが全てのカラムを使用するとは限りません。インデックスの最左プレフィックスルールにより、インデックスの左側（先頭）のカラムだけでも利用できます。

最左プレフィックスルールとは: (A, B, C) という複合インデックスは、以下の検索条件で利用可能です:
A / A, B / A, B, C。
B、C、B, Cは利用できません（または部分的にしか利用できません）。

実用例:

CREATE INDEX idx_multi_use ON orders(customer_id, order_date, status);
ANALYZE orders;

-- ✓ インデックスを完全に利用
SELECT * FROM orders WHERE customer_id = 100 AND order_date >= '2024-01-01' AND status = 'shipped';

-- ✓ インデックスを部分的に利用（customer_id, order_date）
SELECT * FROM orders WHERE customer_id = 100 AND order_date >= '2024-01-01';

-- ✓ インデックスを部分的に利用（customer_idのみ）
SELECT * FROM orders WHERE customer_id = 100;

-- ✗ インデックスを利用できない
SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- ✗ インデックスを利用できない
SELECT * FROM orders WHERE status = 'shipped';

したがって、最も頻繁に単独で検索されるカラムを先頭に配置すると、複数のクエリパターンに対応できます。

DROP INDEX IF EXISTS idx_multi_use;
CREATE INDEX idx_multi_use ON orders(customer_id, order_date, status);
ANALYZE orders;

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date >= '2024-01-01';

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';

原則4: ソート(ORDER BY)との関連性

複合インデックスは、カラムの順序でソート済みのデータ構造です。この特性を活用すると、ORDER BY句の処理を高速化できます。

-- インデックス作成
CREATE INDEX idx_sort ON orders(customer_id, order_date DESC);
ANALYZE orders;

-- このクエリではソート処理が不要（インデックスがすでにソート済み）
SELECT * FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC
LIMIT 10;

EXPLAINの結果に「Sort」ステップが現れなければ、インデックスのソート順序が活用されています。

注意点: PostgreSQLでは、インデックス作成時に各カラムのASC/DESCを指定できます。

CREATE INDEX idx_custom_sort ON orders(region ASC, order_date DESC, total_amount DESC);
ANALYZE orders;

これにより、ORDER BY region ASC, order_date DESC, total_amount DESC というソート順序に最適化されます。

-- ORDER BYと整合していないインデックス、またはインデックスなしで計測
DROP INDEX IF EXISTS idx_sort;
CREATE INDEX idx_customer_only ON orders(customer_id);
ANALYZE orders;

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC
LIMIT 10;

-- order_date DESCを含むインデックスでORDER BYが最適化される
DROP INDEX idx_customer_only;
CREATE INDEX idx_sort ON orders(customer_id, order_date DESC);
ANALYZE orders;

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC
LIMIT 10;

2.3 複合インデックス設計の実践的アプローチ

-- よくあるクエリパターンをリストアップ
-- Q1: 顧客の注文履歴（頻度: 高）
WHERE customer_id = ? AND order_date >= ?

-- Q2: ステータス別の地域検索（頻度: 中）
WHERE region = ? AND status = ?

-- Q3: 期間集計（頻度: 低）
WHERE order_date BETWEEN ? AND ?
GROUP BY ...

SELECT 
    COUNT(DISTINCT customer_id) as customer_distinct,
    COUNT(DISTINCT region) as region_distinct,
    COUNT(DISTINCT status) as status_distinct,
    COUNT(DISTINCT order_date) as date_distinct,
    COUNT(*) as total,
    -- 選択性の推定
    COUNT(*) / COUNT(DISTINCT customer_id)::float as avg_rows_per_customer,
    COUNT(*) / COUNT(DISTINCT region)::float as avg_rows_per_region,
    COUNT(*) / COUNT(DISTINCT status)::float as avg_rows_per_status
FROM orders;

-- Q1向け: customer_idが高選択性で等価条件
CREATE INDEX idx_customer_history ON orders(customer_id, order_date DESC);

-- Q2向け: regionとstatusの両方が等価条件、regionの選択性がやや高い
CREATE INDEX idx_region_status ON orders(region, status);
ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE customer_id = 100 
  AND order_date >= '2024-01-01'
ORDER BY order_date DESC 
LIMIT 10;

2.4 複合インデックスの注意点

注意点1: インデックスが多すぎるコスト

インデックスは検索を高速化しますが、以下のコストがかかります。

• ストレージコスト: インデックス自体がディスク容量を消費
• 書き込みコスト: INSERT/UPDATE/DELETE時にインデックスも更新が必要
• メンテナンスコスト: 定期的なVACUUMやREINDEXが必要

-- テーブルとインデックスのサイズ確認
SELECT 
    pg_size_pretty(pg_relation_size('orders')) as table_size,
    pg_size_pretty(pg_indexes_size('orders')) as indexes_size,
    pg_size_pretty(pg_total_relation_size('orders')) as total_size;

推奨: 1つのテーブルに対して5〜10個程度のインデックスが目安です。それ以上増やす場合は、本当に必要か慎重に検討してください。

注意点2: カラム順序の変更は別インデックス

(A, B) と (B, A) はまったく異なるインデックスです。両方作成すると、ストレージと更新コストが2倍になります。どちらの順序が必要かを明確にし、本当に両方必要な場合だけ作成してください。

注意点3: NULL値の扱い

PostgreSQLでは、NULLもインデックスに含まれます（他のRDBMSと異なる場合あり）。IS NULLやIS NOT NULLの検索にもインデックスが使えます。

-- NULLを含む検索でもインデックスが有効
SELECT * FROM orders WHERE cancelled_at IS NOT NULL;

ただし、ほとんどの値がNULLの場合は、部分インデックスの使用を検討してください。

-- NULL以外だけをインデックス化（サイズ削減）
CREATE INDEX idx_cancelled ON orders(cancelled_at)
WHERE cancelled_at IS NOT NULL;

2.5 演習問題集

-- クエリA: 地域と期間での集計
SELECT region, COUNT(*), SUM(total_amount)
FROM orders
WHERE region = 'Tokyo' 
  AND order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY region;

-- クエリB: 顧客の最近の注文検索
SELECT * FROM orders
WHERE customer_id = 1234 
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY order_date DESC
LIMIT 10;

-- クエリAを実行
EXPLAIN ANALYZE
SELECT region, COUNT(*), SUM(total_amount)
FROM orders
WHERE region = 'Tokyo' 
  AND order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY region;

-- クエリBを実行
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 1234 
  AND order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY order_date DESC
LIMIT 10;

-- クエリA用(等価条件のregionを先頭に)
CREATE INDEX idx_a ON orders(region, order_date);

-- クエリB用(customer_idを先頭に、order_dateで範囲検索とソート)
CREATE INDEX idx_b ON orders(customer_id, order_date DESC);

-- クエリC: ステータスと金額による絞り込み
SELECT * FROM orders
WHERE status IN ('processing', 'shipped')
  AND total_amount >= 5000
  AND region = 'Tokyo'
ORDER BY order_date DESC
LIMIT 100;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE status IN ('processing', 'shipped')
  AND total_amount >= 5000
  AND region = 'Tokyo'
ORDER BY order_date DESC
LIMIT 100;

CREATE INDEX idx_c ON orders(region, status, order_date DESC);

-- 対象クエリ
SELECT order_id, total_amount 
FROM orders 
WHERE customer_id = 5000;

-- 解答: 検索条件(customer_id)に加え、取得列(order_id, total_amount)も含める
CREATE INDEX idx_covering ON orders(customer_id, order_id, total_amount);
ANALYZE orders;

-- 対象クエリ
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE LOWER(region) = 'tokyo';

-- 解答: カラムそのものではなく、式に対してインデックスを貼る
CREATE INDEX idx_lower_region ON orders(LOWER(region));
ANALYZE orders;

-- 対象クエリ
SELECT * FROM orders 
WHERE status = 'cancelled' 
  AND total_amount >= 10000;

-- 比較用：通常のインデックス
CREATE INDEX idx_full ON orders(status, total_amount);
ANALYZE orders;

-- ★ここに解答のインデックスを作成★

-- サイズ比較（解答のインデックス名に置き換えて実行）
SELECT 
    pg_size_pretty(pg_relation_size('idx_full')) as full_index_size,
    pg_size_pretty(pg_relation_size('【あなたの作成したインデックス名】')) as partial_index_size;

-- 解答: WHERE句付きのインデックスを作成する
CREATE INDEX idx_partial ON orders(total_amount) WHERE status = 'cancelled';
ANALYZE orders;

-- サイズ比較
SELECT 
    pg_size_pretty(pg_relation_size('idx_full')) as full_index_size,
    pg_size_pretty(pg_relation_size('idx_partial')) as partial_index_size;

-- 注文明細テーブル（約300万件）の作成
DROP TABLE IF EXISTS order_items;
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO order_items (order_id, product_name, quantity)
SELECT 
    (random() * 1000000)::INTEGER + 1,
    'Product-' || (floor(random() * 100) + 1)::TEXT,
    (floor(random() * 10) + 1)::INTEGER
FROM generate_series(1, 3000000);
ANALYZE order_items;

-- 対象クエリ
EXPLAIN ANALYZE
SELECT o.order_date, o.customer_id
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_name = 'Product-50';

-- 解答: 結合キー(order_id)と検索条件(product_name)へのインデックス
-- order_items側: product_nameで絞り込み、order_idで結合する
CREATE INDEX idx_items_product_order ON order_items(product_name, order_id);
ANALYZE order_items;

-- orders側: order_idは主キーなので既にインデックスがあるため作成不要

-- 対象クエリ
SELECT status, total_amount 
FROM orders 
WHERE customer_id = 1234 
ORDER BY order_date DESC;

-- 解答: キー部には検索・ソート列のみを指定し、付加情報はINCLUDEに逃がす
CREATE INDEX idx_include ON orders(customer_id, order_date DESC)
INCLUDE (status, total_amount);
ANALYZE orders;

2.6 まとめ: 複合インデックス設計のチェックリスト

複合インデックスを設計する際は、以下の順序で検討してください。

• クエリパターンの特定: どのWHERE条件が頻繁に使われるか
• 等価条件の識別: =で検索されるカラムを先頭候補に
• 選択性の評価: カーディナリティが高いカラムを優先
• 範囲条件の配置: 範囲検索やLIKEは後方に
• ソート要件の確認: ORDER BYとの整合性
• 最左プレフィックスの活用: 複数のクエリパターンをカバー
• 実測による検証: EXPLAIN ANALYZEで効果を確認
• コストとの比較: ストレージと更新コストを考慮

この原則に従うことで、効率的で保守しやすい複合インデックスを設計できます。

本コンテンツの作成時間：約18時間(2026/02/21)