PostgreSQL インデックス完全ガイド – 基本から高速化テクニックまで

データベース・SQL

「SELECT文が遅すぎて実用にならない…」

「テーブルが大きくなるにつれて検索が重くなってきた」

「インデックスを張ったのに速くならない」

「どこにインデックスを張ればいいか分からない」

こんな悩みを解決します！

適切なインデックスを張ることで、クエリの実行速度は数十倍〜数百倍速くなります。

しかし、むやみにインデックスを張ると、逆にパフォーマンスが悪化することも…

この記事では、PostgreSQLのインデックスについて、基本から実践的な最適化テクニックまで、豊富な実例とともに解説していきます！

スポンサーリンク

インデックスの基本を理解しよう

インデックスとは？

インデックスは**データベースの「索引」**です。本の巻末の索引と同じように、データを高速に検索するための仕組みです。

-- インデックスなしの検索（全件スキャン）
-- 100万件のテーブルで実行時間: 500ms
SELECT * FROM users WHERE email = 'user@example.com';

-- インデックスありの検索（インデックススキャン）
-- 同じクエリで実行時間: 2ms（250倍高速！）
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';

インデックスの仕組み

-- テーブル構造の例
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    category VARCHAR(100),
    price DECIMAL(10,2),
    created_at TIMESTAMP
);

-- インデックスなし：全行を順番に確認（シーケンシャルスキャン）
-- データ: [1,りんご], [2,バナナ], [3,みかん], ... [1000000,ぶどう]
-- 検索: 最初から最後まで全て確認

-- インデックスあり：B-treeで高速アクセス
-- インデックス構造:
--         [500000]
--        /         \
--   [250000]      [750000]
--   /      \      /      \
-- [データ] [データ] [データ] [データ]
-- 検索: 数回の比較で到達

PostgreSQLのインデックス種類と使い分け

1. B-tree（デフォルト）

最も一般的で万能なインデックス

-- 基本的な作成方法
CREATE INDEX idx_name ON table_name (column_name);

-- 明示的にB-treeを指定
CREATE INDEX idx_products_price ON products USING btree (price);

-- 適している検索条件
-- =, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 200;
SELECT * FROM products WHERE category IN ('食品', '飲料');

-- ソートにも有効
SELECT * FROM products ORDER BY price;

2. Hash インデックス

等価検索に特化（PostgreSQL 10以降で改善）

-- Hashインデックスの作成
CREATE INDEX idx_users_email_hash ON users USING hash (email);

-- 適している検索（等価検索のみ）
SELECT * FROM users WHERE email = 'user@example.com';

-- 適さない検索（範囲検索は不可）
-- SELECT * FROM users WHERE email LIKE 'user%';  -- 使えない
-- SELECT * FROM users WHERE email > 'a';          -- 使えない

3. GiST（Generalized Search Tree）

幾何データや全文検索に有効

-- 幾何データ用のGiSTインデックス
CREATE INDEX idx_locations_coords ON locations USING gist (coordinates);

-- 範囲型データ用
CREATE INDEX idx_events_duration ON events USING gist (tsrange(start_time, end_time));

-- 使用例：範囲の重なり検索
SELECT * FROM events 
WHERE tsrange(start_time, end_time) && tsrange('2024-01-01', '2024-01-31');

4. GIN（Generalized Inverted Index）

配列、JSON、全文検索に最適

-- 配列カラムへのGINインデックス
CREATE INDEX idx_posts_tags ON posts USING gin (tags);

-- JSONBカラムへのGINインデックス
CREATE INDEX idx_products_attributes ON products USING gin (attributes);

-- 全文検索用
CREATE INDEX idx_articles_content ON articles USING gin (to_tsvector('japanese', content));

-- 使用例
SELECT * FROM posts WHERE tags @> ARRAY['PostgreSQL'];
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM articles WHERE to_tsvector('japanese', content) @@ to_tsquery('japanese', 'データベース');

5. SP-GiST（Space-Partitioned GiST）

特殊なデータ構造に有効

-- 電話番号や階層データ用
CREATE INDEX idx_phones_number ON phones USING spgist (phone_number);

-- IPアドレス用
CREATE INDEX idx_logs_ip ON access_logs USING spgist (inet(ip_address));

6. BRIN（Block Range Index）

巨大テーブルの順序性のあるデータに有効

-- 時系列データに最適（サイズが非常に小さい）
CREATE INDEX idx_logs_created_at ON logs USING brin (created_at);

-- 使用例：日付範囲検索
SELECT * FROM logs WHERE created_at >= '2024-01-01';

インデックスを張るべき場所の見極め方

WHERE句で頻繁に使われるカラム

-- よく実行されるクエリを分析
-- pg_stat_statementsを有効化
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 遅いクエリを特定
SELECT 
    query,
    calls,
    mean_exec_time,
    total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

-- インデックスが必要な例
-- このクエリが頻繁に実行される場合
SELECT * FROM orders WHERE customer_id = 12345;
-- → customer_idにインデックスを張る
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

JOIN条件で使われるカラム

-- JOINで使われるカラムにインデックス
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- 効率的なJOIN
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = 12345;

ORDER BYで使われるカラム

-- ソート用のインデックス
CREATE INDEX idx_products_created_at_desc ON products (created_at DESC);

-- 複合インデックスでソートを高速化
CREATE INDEX idx_posts_user_created ON posts (user_id, created_at DESC);

-- このクエリが高速化される
SELECT * FROM posts 
WHERE user_id = 100 
ORDER BY created_at DESC 
LIMIT 10;

複合インデックスの設計

カラムの順序が重要

-- 複合インデックスの作成
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

-- このインデックスが有効なクエリ
SELECT * FROM orders WHERE status = 'pending';  -- ✓ 使える
SELECT * FROM orders WHERE status = 'pending' AND order_date = '2024-01-01';  -- ✓ 使える

-- このインデックスが無効なクエリ
SELECT * FROM orders WHERE order_date = '2024-01-01';  -- ✗ 使えない（最初のカラムがない）

-- カラム順序の最適化ルール
-- 1. カーディナリティ（値の種類）が低いカラムを先に
-- 2. 等価検索のカラムを範囲検索より先に
CREATE INDEX idx_products_category_price ON products (category, price);
-- category（種類少ない）→ price（種類多い）

カバリングインデックス

-- クエリに必要な全カラムを含むインデックス
CREATE INDEX idx_orders_covering 
ON orders (customer_id, order_date, total_amount)
INCLUDE (status, shipping_address);

-- Index Only Scanが可能になる（テーブルアクセス不要）
SELECT customer_id, order_date, total_amount, status 
FROM orders 
WHERE customer_id = 12345;

部分インデックス（Partial Index）

条件付きインデックスで容量削減

-- アクティブユーザーのみのインデックス
CREATE INDEX idx_users_email_active 
ON users (email) 
WHERE status = 'active';

-- NULL以外の値にのみインデックス
CREATE INDEX idx_products_sale_price 
ON products (sale_price) 
WHERE sale_price IS NOT NULL;

-- 特定の期間のデータのみ
CREATE INDEX idx_orders_recent 
ON orders (customer_id, order_date) 
WHERE order_date >= '2024-01-01';

-- 使用例
SELECT * FROM users WHERE email = 'test@example.com' AND status = 'active';
-- → 部分インデックスが使われる

式インデックス（Expression Index）

関数や計算結果にインデックス

-- 小文字変換したメールアドレスにインデックス
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 使用例
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- JSON要素にインデックス
CREATE INDEX idx_products_json_category 
ON products ((attributes->>'category'));

-- 計算結果にインデックス
CREATE INDEX idx_orders_total_with_tax 
ON orders ((total_amount * 1.1));

-- 日付の年月でインデックス
CREATE INDEX idx_logs_year_month 
ON logs (DATE_TRUNC('month', created_at));

インデックスの効果を確認する方法

EXPLAIN ANALYZEで実行計画を確認

-- 実行計画の確認
EXPLAIN ANALYZE
SELECT * FROM products WHERE price > 100;

-- 結果の見方
-- Seq Scan: 全件スキャン（インデックス未使用）
-- Index Scan: インデックススキャン（高速）
-- Index Only Scan: インデックスのみで完結（最速）

-- 詳細な分析
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 12345;

-- 実行時間の比較
-- インデックス作成前
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Execution Time: 523.456 ms

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

-- インデックス作成後
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Execution Time: 0.123 ms（4000倍高速化！）

pg_stat_user_indexesで使用状況確認

-- インデックスの使用回数を確認
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 使われていないインデックスを検出
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

インデックスのメンテナンス

インデックスの再構築

-- インデックスの肥大化を確認
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as size,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- インデックスの再構築（ロックあり）
REINDEX INDEX idx_users_email;

-- テーブル全体のインデックスを再構築
REINDEX TABLE users;

-- 同時実行可能な再構築（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 新しいインデックスを作成して切り替え（古いバージョン）
CREATE INDEX CONCURRENTLY idx_users_email_new ON users(email);
DROP INDEX idx_users_email;
ALTER INDEX idx_users_email_new RENAME TO idx_users_email;

VACUUM と ANALYZE

-- 統計情報の更新
ANALYZE users;

-- 不要な領域の回収と統計更新
VACUUM ANALYZE users;

-- 自動VACUUMの設定確認
SELECT name, setting 
FROM pg_settings 
WHERE name LIKE 'autovacuum%';

インデックスのアンチパターンと対策

アンチパターン1：むやみな全カラムインデックス

-- ❌ 悪い例：全カラムにインデックス
CREATE INDEX idx_users_col1 ON users(col1);
CREATE INDEX idx_users_col2 ON users(col2);
CREATE INDEX idx_users_col3 ON users(col3);
-- ... 全カラム

-- ✅ 良い例：必要なカラムのみ
-- 実際に使われるクエリを分析してから作成
CREATE INDEX idx_users_search ON users(email, status)
WHERE status = 'active';

アンチパターン2：カーディナリティが低いカラム

-- ❌ 効果が薄い例
CREATE INDEX idx_users_gender ON users(gender);  -- 値が2種類しかない

-- ✅ 改善案：部分インデックスや複合インデックス
CREATE INDEX idx_users_gender_created ON users(gender, created_at);

アンチパターン3：頻繁に更新されるカラム

-- ❌ 更新が遅くなる例
CREATE INDEX idx_products_view_count ON products(view_count);  -- 頻繁に更新

-- ✅ 改善案：必要性を再検討、または非同期更新
-- ビューカウントは別テーブルで管理
CREATE TABLE product_stats (
    product_id INTEGER,
    view_count INTEGER
);

パフォーマンスチューニングの実践例

遅いクエリの改善

-- 改善前：3秒かかるクエリ
SELECT p.*, u.name as user_name, COUNT(c.id) as comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.created_at >= '2024-01-01'
  AND p.status = 'published'
GROUP BY p.id, u.name
ORDER BY p.created_at DESC
LIMIT 20;

-- 改善策1：適切なインデックスを作成
CREATE INDEX idx_posts_status_created ON posts(status, created_at DESC);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- 改善策2：カバリングインデックス
CREATE INDEX idx_posts_covering ON posts(status, created_at DESC, user_id)
INCLUDE (title, content);

-- 改善策3：マテリアライズドビューの活用
CREATE MATERIALIZED VIEW post_summary AS
SELECT 
    p.*,
    u.name as user_name,
    COUNT(c.id) as comment_count
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
GROUP BY p.id, u.name;

CREATE INDEX idx_post_summary_created ON post_summary(created_at DESC);

-- 結果：3秒 → 50ms（60倍高速化）

インデックス設計のベストプラクティス

1. 開発環境での検証

-- 本番と同じデータ量でテスト
-- データ生成
INSERT INTO users (email, name, created_at)
SELECT 
    'user' || i || '@example.com',
    'User ' || i,
    CURRENT_DATE - (random() * 365)::INTEGER
FROM generate_series(1, 1000000) i;

-- インデックスの効果測定
\timing on
SELECT * FROM users WHERE email = 'user50000@example.com';
-- Time: 234.567 ms

CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'user50000@example.com';
-- Time: 0.234 ms

2. インデックス戦略

-- 優先順位を決めて作成
-- 1. PRIMARY KEY（自動作成）
-- 2. FOREIGN KEY
-- 3. よく使うWHERE句
-- 4. JOIN条件
-- 5. ORDER BY
-- 6. その他

-- インデックス作成スクリプト例
DO $$
DECLARE
    idx_exists BOOLEAN;
BEGIN
    -- インデックスの存在確認
    SELECT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE indexname = 'idx_orders_customer_id'
    ) INTO idx_exists;
    
    -- 存在しない場合のみ作成
    IF NOT idx_exists THEN
        CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);
        RAISE NOTICE 'Index created: idx_orders_customer_id';
    END IF;
END $$;

まとめ – インデックスでPostgreSQLを爆速に！

PostgreSQLのインデックスについて、基本から実践まで解説してきました。

重要ポイント：

  • 適切なインデックスでクエリは数百倍高速化
  • B-treeが万能、用途に応じて他の型も活用
  • WHERE、JOIN、ORDER BYに注目
  • 複合インデックスはカラム順序が重要
  • 定期的なメンテナンスで性能維持

インデックス設計のステップ：

  1. 遅いクエリを特定（EXPLAIN ANALYZE）
  2. 適切なインデックス型を選択
  3. 作成して効果を測定
  4. 使用状況を監視
  5. 定期的にメンテナンス

インデックスは諸刃の剣です。適切に使えば劇的な高速化を実現できますが、無計画に作ると逆効果になることも。

この記事を参考に、最適なインデックス設計を実現してください！

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました