PostgreSQLのインデックスが効かない！よくある原因と確実に解決する方法

「インデックス作ったのに、クエリが全然速くならない…」 「EXPLAIN見たら、Seq Scanって出てる。なんで？」 「同じクエリなのに、時々インデックスが使われたり使われなかったり…」

こんな経験、ありませんか？

実は、インデックスを作っても使われない理由は山ほどあるんです。でも大丈夫！この記事を読めば、なぜインデックスが効かないのか、そしてどうすれば効くようになるのか、すべて分かります。

一緒に、インデックスの「罠」から脱出しましょう！

---

まずは確認：本当にインデックスが使われていない？

EXPLAIN ANALYZEで真実を見る

-- インデックスが使われているか確認
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

インデックスが使われていない場合：

Seq Scan on users  (cost=0.00..12834.00 rows=1 width=100)
  Filter: ((email)::text = 'test@example.com'::text)
  Rows Removed by Filter: 499999
Planning Time: 0.123 ms
Execution Time: 523.456 ms  -- 遅い！

インデックスが使われている場合：

Index Scan using idx_users_email on users  (cost=0.43..8.45 rows=1 width=100)
  Index Cond: ((email)::text = 'test@example.com'::text)
Planning Time: 0.087 ms
Execution Time: 0.234 ms  -- 速い！

「Seq Scan」が出たら要注意！全件スキャンしてます。

---

原因1：型の不一致（最も多い原因！）

文字列と数値の混同

-- テーブル定義
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,  -- INTEGER型
    amount DECIMAL(10,2)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ダメな例：文字列で検索してる！
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = '12345';  -- '12345'は文字列
-- 結果：Seq Scan（インデックス使われない）

-- 良い例：数値で検索
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;  -- 12345は数値
-- 結果：Index Scan（インデックス使われる）

暗黙の型変換の罠

-- VARCHARカラムに数値インデックス？
CREATE TABLE products (
    product_code VARCHAR(20),  -- VARCHAR型
    name VARCHAR(100)
);

CREATE INDEX idx_product_code ON products(product_code);

-- アプリケーションから数値が渡される場合
SELECT * FROM products WHERE product_code = 12345;  -- 型変換が発生
-- インデックスが効かない！

-- 解決策：明示的にキャスト
SELECT * FROM products WHERE product_code = '12345';
-- または
SELECT * FROM products WHERE product_code = 12345::VARCHAR;

---

原因2：関数や演算子の使用

カラムに関数を適用すると効かない

-- インデックスあり
CREATE INDEX idx_users_email ON users(email);

-- ダメな例：LOWER関数
EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- Seq Scan！インデックス効かない

-- 解決策1：関数インデックスを作成
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 解決策2：データ側を調整
SELECT * FROM users WHERE email = 'Test@Example.com';  -- 大文字小文字を合わせる

演算子の落とし穴

-- 日付カラムのインデックス
CREATE INDEX idx_orders_created ON orders(created_at);

-- ダメな例：カラムで計算
SELECT * FROM orders 
WHERE created_at + INTERVAL '1 day' > '2024-01-01';

-- 良い例：値側で計算
SELECT * FROM orders 
WHERE created_at > '2024-01-01'::DATE - INTERVAL '1 day';

---

原因3：LIKE検索の%位置

前方一致 vs 後方一致 vs 部分一致

CREATE INDEX idx_products_name ON products(name);

-- ○ 前方一致：インデックス効く
SELECT * FROM products WHERE name LIKE 'iPhone%';

-- × 後方一致：インデックス効かない
SELECT * FROM products WHERE name LIKE '%Pro';

-- × 部分一致：インデックス効かない
SELECT * FROM products WHERE name LIKE '%iPhone%';

-- 解決策：pg_trgmで部分一致も高速化
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
-- これで部分一致でもインデックスが効く！

text_pattern_opsで前方一致を最適化

-- 通常のインデックスより前方一致に特化
CREATE INDEX idx_products_name_pattern 
ON products(name text_pattern_ops);

-- LIKE検索が更に高速に
SELECT * FROM products WHERE name LIKE 'iPhone%';

---

原因4：データ量とコストの問題

テーブルが小さすぎる

-- 100件しかないテーブル
CREATE TABLE small_table (
    id INTEGER PRIMARY KEY,
    data VARCHAR(100)
);

CREATE INDEX idx_small_data ON small_table(data);

-- PostgreSQLの判断：「全件読んだ方が速い」
EXPLAIN SELECT * FROM small_table WHERE data = 'test';
-- Seq Scanになることが多い（これは正常な判断）

取得行数が多すぎる

-- 100万件中50万件を取得する場合
EXPLAIN ANALYZE
SELECT * FROM large_table 
WHERE status = 'active';  -- 全体の50%がactive

-- PostgreSQLの判断：
-- 「インデックス経由で50万回アクセスするより、
--  全件スキャンの方が速い」
-- 結果：Seq Scan

-- 解決策：部分インデックス
CREATE INDEX idx_active_only ON large_table(id) 
WHERE status = 'active';

---

原因5：統計情報が古い

自動VACUUMが追いついていない

-- 大量データ投入後、統計情報が更新されていない
INSERT INTO products SELECT * FROM products_import;  -- 100万件追加

-- 統計情報を手動更新
ANALYZE products;

-- または自動VACUUM設定を調整
ALTER TABLE products SET (autovacuum_analyze_scale_factor = 0.01);

統計情報の確認

-- テーブルの統計情報を確認
SELECT 
    schemaname,
    tablename,
    n_live_tup AS 推定行数,
    n_dead_tup AS 削除済み行数,
    last_analyze AS 最終ANALYZE,
    last_autoanalyze AS 最終自動ANALYZE
FROM pg_stat_user_tables
WHERE tablename = 'products';

---

原因6：複合インデックスの順番

カラムの順番が重要

-- 複合インデックス（順番重要！）
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- ○ 効く：最初のカラムから使用
SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND order_date = '2024-01-01';

-- × 効かない：2番目のカラムだけ
SELECT * FROM orders WHERE order_date = '2024-01-01';

-- 解決策：別のインデックスを作成
CREATE INDEX idx_orders_date ON orders(order_date);

---

原因7：NULL値の扱い

NULLの検索

-- IS NULLでインデックスが効かない場合がある
CREATE INDEX idx_users_deleted_at ON users(deleted_at);

-- 効きにくいクエリ
SELECT * FROM users WHERE deleted_at IS NULL;

-- 解決策：部分インデックス
CREATE INDEX idx_active_users ON users(id) 
WHERE deleted_at IS NULL;

-- または、NULLを含む複合インデックス
CREATE INDEX idx_users_status ON users(deleted_at, id) 
WHERE deleted_at IS NULL;

---

原因8：不適切な設定値

random_page_costの調整

-- SSDを使っている場合は調整が必要
SHOW random_page_cost;  -- デフォルト: 4.0

-- SSD用に調整（セッションレベル）
SET random_page_cost = 1.1;

-- システム全体で調整
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

effective_cache_sizeの設定

-- メモリキャッシュサイズの設定
SHOW effective_cache_size;

-- 適切な値に調整（メモリの50-75%程度）
ALTER SYSTEM SET effective_cache_size = '8GB';

---

トラブルシューティングの手順

ステップ1：インデックスの存在確認

-- テーブルのインデックス一覧
\d+ テーブル名

-- または
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

ステップ2：実行計画の確認

-- 実際のデータで確認
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE email = 'test@example.com';

ステップ3：強制的にインデックスを使わせる

-- テスト用：Seq Scanを無効化
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
SET enable_seqscan = ON;  -- 必ず戻す！

-- これでもSeq Scanなら、インデックスに根本的な問題あり

---

よくあるケースの解決策まとめ

ケース1：大文字小文字を区別しない検索

-- 問題：emailの大文字小文字を区別しない
-- 解決策：
CREATE INDEX idx_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = LOWER('Test@Example.com');

ケース2：日本語の全文検索

-- 解決策：pg_trgmまたはpg_bigm
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_content_gin ON articles USING gin(content gin_trgm_ops);

SELECT * FROM articles WHERE content LIKE '%PostgreSQL%';

ケース3：範囲検索が遅い

-- 問題：日付範囲の検索が遅い
-- 解決策：BRINインデックス（時系列データ）
CREATE INDEX idx_logs_brin ON access_logs USING brin(created_at);

-- またはパーティショニングを検討

---

プロのデバッグテクニック

pg_stat_user_indexesで使用状況確認

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS スキャン回数,
    idx_tup_read AS 読み取り行数,
    idx_tup_fetch AS 取得行数
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY idx_scan;

コスト見積もりの確認

-- なぜSeq Scanを選んだか理解する
EXPLAIN (SETTINGS ON)
SELECT * FROM users WHERE email = 'test@example.com';

---

まとめ：インデックスを確実に効かせるチェックリスト

インデックスが効かない時の確認ポイント：

✅ 型は一致してる？（文字列 vs 数値） ✅ 関数使ってない？（LOWER、UPPER、計算） ✅ LIKE検索で%が先頭にない？ ✅ データ量は十分？（100件以下は効かないことも） ✅ 統計情報は最新？（ANALYZE実行） ✅ 複合インデックスの順番は正しい？ ✅ NULL値の扱いは適切？ ✅ 設定値は最適？（random_page_cost等）

インデックスが効かない問題は、必ず原因があります。

この記事のチェックリストを一つずつ確認すれば、きっと原因が見つかるはず。諦めずに、EXPLAIN ANALYZEとにらめっこしてください。

インデックスが効いた瞬間の「やった！100倍速くなった！」という感動を、ぜひ体験してください！

---

困った時の参考記事：

• EXPLAIN ANALYZEの読み方完全ガイド
• PostgreSQLのコスト計算の仕組み
• パフォーマンスチューニングの実践テクニック