「SELECTが遅すぎて、レポート生成に10分もかかる…」
「WHERE句を使った検索が、なぜこんなに時間がかかるの？」
「インデックスを作りたいけど、どこに作ればいいか分からない」
こんな悩み、インデックスを正しく使えば一瞬で解決します！
実際、適切なインデックスを作るだけで、クエリの実行時間が10秒から0.1秒になることも珍しくありません。まさにデータベースの魔法です。
この記事を読めば、インデックスの作り方から最適化まで、すべてマスターできます！
インデックスを超簡単に理解する
本の索引に例えると
インデックスは、まさに本の巻末にある「索引」と同じです。
本で「PostgreSQL」という単語を探す時：
- 索引なし：1ページ目から全部読む（遅い）
- 索引あり：索引で「PostgreSQL…234ページ」を見て直行（速い）
データベースも同じ：
- インデックスなし：全データをスキャン（フルスキャン）
- インデックスあり：目的のデータに直接アクセス
インデックスの効果を数字で実感
-- 100万件のユーザーテーブル
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255),
name VARCHAR(100),
created_at TIMESTAMP
);
-- インデックスなしで検索
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- 実行時間: 523.456 ms
-- インデックスを作成
CREATE INDEX idx_users_email ON users(email);
-- インデックスありで検索
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- 実行時間: 0.456 ms （1000倍速い！）基本編：インデックスの作成方法
基本構文
CREATE INDEX インデックス名 ON テーブル名 (カラム名);実践例1：単一カラムインデックス
-- 商品テーブル
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(200),
category VARCHAR(50),
price DECIMAL(10,2),
stock INTEGER,
created_at TIMESTAMP
);
-- よく検索されるカラムにインデックスを作成
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_price ON products(price);実践例2：複合インデックス（複数カラム）
-- 複数カラムを組み合わせた検索が多い場合
CREATE INDEX idx_products_category_price ON products(category, price);
-- この順番が重要！
-- WHERE category = 'electronics' AND price < 10000 -- 速い
-- WHERE price < 10000 -- このインデックスは使われない
-- WHERE category = 'electronics' -- 速い（最初のカラムだけでもOK）実践例3：部分インデックス（条件付き）
-- アクティブなユーザーだけにインデックス
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;
-- 在庫がある商品だけにインデックス
CREATE INDEX idx_available_products ON products(name)
WHERE stock > 0;インデックスの種類と使い分け
1. B-treeインデックス（デフォルト）
最も一般的で万能なインデックス：
-- 明示的にB-treeを指定（省略してもB-treeになる）
CREATE INDEX idx_btree ON users USING btree(email);得意な検索：
- 等価検索（=）
- 範囲検索（<、>、BETWEEN）
- ソート（ORDER BY）
- NULL検索
使用例：
-- 日付の範囲検索
CREATE INDEX idx_orders_date ON orders(order_date);
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';2. Hashインデックス
等価検索に特化：
CREATE INDEX idx_hash ON users USING hash(user_id);特徴：
- 等価検索（=）のみ対応
- B-treeより少しだけ速い
- でも制限が多いので、通常はB-treeで十分
3. GINインデックス（全文検索）
配列や全文検索に最適：
-- タグの配列にインデックス
CREATE INDEX idx_tags ON articles USING gin(tags);
-- 全文検索用
CREATE INDEX idx_search ON articles USING gin(to_tsvector('japanese', content));
-- 使用例
SELECT * FROM articles WHERE tags @> ARRAY['PostgreSQL'];
SELECT * FROM articles WHERE to_tsvector('japanese', content) @@ to_tsquery('japanese', 'データベース');4. GiSTインデックス（地理空間）
地理データや範囲型に最適：
-- 位置情報
CREATE INDEX idx_location ON stores USING gist(location);
-- 使用例：1km以内の店舗を検索
SELECT * FROM stores
WHERE location <-> point(35.6812, 139.7671) < 1;5. BRINインデックス（巨大テーブル）
時系列データなど、物理的に順序があるデータ：
-- ログテーブル（日付順に挿入される）
CREATE INDEX idx_logs_brin ON access_logs USING brin(created_at);メリット：
- インデックスサイズが極小（B-treeの1/100以下）
- 巨大テーブルでも高速
いつインデックスを作るべき？作らないべき？
インデックスを作るべき場合 ✅
-- 1. WHERE句でよく使うカラム
SELECT * FROM users WHERE email = 'user@example.com';
→ CREATE INDEX idx_users_email ON users(email);
-- 2. JOIN条件に使うカラム
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;
→ CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 3. ORDER BYで使うカラム
SELECT * FROM products ORDER BY price DESC;
→ CREATE INDEX idx_products_price ON products(price DESC);
-- 4. GROUP BYで使うカラム
SELECT category, COUNT(*) FROM products GROUP BY category;
→ CREATE INDEX idx_products_category ON products(category);インデックスを作らない方がいい場合 ❌
-- 1. 更新が頻繁なテーブル（INSERT/UPDATE/DELETEが多い）
-- インデックスの更新コストが高い
-- 2. カラムの値の種類が少ない（カーディナリティが低い）
-- 例：性別（男/女）、有効フラグ（true/false）
-- → 部分インデックスを検討
-- 3. テーブルが小さい（1000件以下）
-- フルスキャンの方が速い場合も
-- 4. ほとんど検索しないカラム
-- 作っても無駄パフォーマンス改善の実例
ケース1：遅いクエリを100倍高速化
-- 注文履歴テーブル（500万件）
CREATE TABLE order_history (
id SERIAL PRIMARY KEY,
user_id INTEGER,
product_id INTEGER,
order_date DATE,
amount DECIMAL(10,2),
status VARCHAR(20)
);
-- 問題のクエリ（10秒かかる）
EXPLAIN ANALYZE
SELECT * FROM order_history
WHERE user_id = 12345
AND order_date >= '2024-01-01'
ORDER BY order_date DESC;
-- 実行計画を見ると...
-- Seq Scan on order_history (cost=... rows=500000)
-- 全件スキャンしてる！
-- 解決策：複合インデックスを作成
CREATE INDEX idx_order_history_user_date
ON order_history(user_id, order_date DESC);
-- 結果：0.1秒に短縮！（100倍高速化）ケース2：LIKE検索の最適化
-- 商品名の前方一致検索が遅い
SELECT * FROM products WHERE name LIKE 'iPhone%';
-- text_patternインデックスで高速化
CREATE INDEX idx_products_name_pattern
ON products(name text_pattern_ops);
-- 部分一致検索（%iPhone%）の場合
-- pg_trgmエクステンションを使用
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm
ON products USING gin(name gin_trgm_ops);インデックスの運用・管理
インデックスの使用状況を確認
-- どのインデックスが使われているか
SELECT
schemaname,
tablename,
indexname,
idx_scan AS 使用回数,
idx_tup_read AS 読み取り行数,
idx_tup_fetch AS 取得行数,
pg_size_pretty(pg_relation_size(indexrelid)) AS サイズ
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;不要なインデックスを見つける
-- 全く使われていないインデックス
SELECT
schemaname || '.' || tablename AS テーブル,
indexname AS インデックス名,
pg_size_pretty(pg_relation_size(indexrelid)) AS サイズ
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- 一度も使われていない
AND indexrelname NOT LIKE '%_pkey' -- 主キー以外
ORDER BY pg_relation_size(indexrelid) DESC;インデックスの再構築（断片化解消）
-- オンラインで再構築（REINDEXより安全）
CREATE INDEX CONCURRENTLY idx_new ON products(name);
DROP INDEX idx_old;
ALTER INDEX idx_new RENAME TO idx_products_name;
-- または、REINDEX（テーブルロックに注意）
REINDEX INDEX idx_products_name;インデックスの肥大化チェック
-- インデックスの肥大化率を確認
SELECT
schemaname || '.' || tablename AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
ROUND(100 * (1 - (pg_stat_get_live_tuples(indexrelid)::FLOAT /
NULLIF(pg_stat_get_tuples_inserted(indexrelid), 0)))) AS bloat_percent
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 1000000 -- 1MB以上
ORDER BY pg_relation_size(indexrelid) DESC;よくある失敗と対策
失敗1：インデックスが使われない
-- 型の不一致
CREATE INDEX idx_user_id ON orders(user_id); -- integerカラム
SELECT * FROM orders WHERE user_id = '123'; -- 文字列で検索
-- 解決：型を合わせる
SELECT * FROM orders WHERE user_id = 123;
-- 関数を使った検索
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 解決：関数インデックスを作成
CREATE INDEX idx_email_lower ON users(LOWER(email));失敗2：インデックスの作りすぎ
-- 悪い例：似たようなインデックスが複数
CREATE INDEX idx1 ON products(category);
CREATE INDEX idx2 ON products(category, price);
CREATE INDEX idx3 ON products(category, name);
-- 良い例：必要最小限に絞る
CREATE INDEX idx_products_category_price ON products(category, price);
-- categoryだけの検索も、category+priceの検索も、これ1つでカバー失敗3：CONCURRENTLYを忘れる
-- 危険：本番環境でテーブルロック！
CREATE INDEX idx_large_table ON large_table(column);
-- 安全：ロックを最小限に
CREATE INDEX CONCURRENTLY idx_large_table ON large_table(column);
-- 時間はかかるが、サービスを止めない実用的なインデックス戦略
Eコマースサイトの例
-- 商品検索の最適化
CREATE INDEX idx_products_search
ON products(category, is_active, price)
WHERE is_active = true;
-- 注文検索の最適化
CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at DESC);
-- 在庫管理の最適化
CREATE INDEX idx_inventory_low_stock
ON products(stock, name)
WHERE stock < 10;ログ分析システムの例
-- 時系列データにBRINインデックス
CREATE INDEX idx_logs_time_brin
ON access_logs USING brin(timestamp);
-- IPアドレス検索
CREATE INDEX idx_logs_ip
ON access_logs USING hash(ip_address);
-- エラーログだけに部分インデックス
CREATE INDEX idx_error_logs
ON access_logs(timestamp, message)
WHERE log_level = 'ERROR';まとめ：インデックスマスターへの道
今日学んだ重要ポイント：
✅ インデックスは「本の索引」と同じ仕組み
✅ 適切なインデックスで100倍高速化も可能
✅ B-treeが万能、特殊用途にGIN/GiST/BRIN
✅ WHERE/JOIN/ORDER BY/GROUP BYに作成すべき
✅ 複合インデックスは順番が重要
✅ CONCURRENTLYで本番環境でも安全作成
✅ 使われないインデックスは定期的に削除
インデックスは「諸刃の剣」です。
適切に使えばクエリが劇的に速くなりますが、作りすぎると更新が遅くなります。大切なのは、実行計画を見て、本当に必要な場所に作ること。
今日から、遅いクエリを見つけたら、まずインデックスを疑ってください。
きっと、あなたもインデックスの魔法に魅了されるはずです！
Happy Indexing!
次のステップにおすすめ：
- PostgreSQLの実行計画（EXPLAIN）完全解説
- パーティショニングとインデックスの組み合わせ
- pg_stat_statementsでスロークエリ分析
コメント