PostgreSQLインデックス最適化：スロークエリからミリ秒レスポンスまで6つのキーチューニング戦略

数据库

クエリが47秒かかり、ボスがデータベースの交換を言い出す

注文リストのクエリが2秒から47秒になり、ユーザーが猛抗議。インデックスを追加したら、逆にクエリが遅くなった。EXPLAIN ANALYZEの出力が理解できず、問題がどこにあるかわからない。2026年、PostgreSQLインデックス最適化 はスロークエリからミリ秒レスポンスへの鍵——正しいインデックスタイプの選択、実行計画の理解、インデックス無効化の回避で、クエリパフォーマンスを1000倍向上できます。

本記事はPostgreSQLインデックス原理から出発し、6つのキーチューニング戦略を、インデックスタイプ選択から実行計画分析、部分インデックスから並行インデックス作成まで完全ガイドします。

PostgreSQLインデックスコア概念

概念 説明
B-treeインデックス デフォルトインデックスタイプ、等値クエリ、範囲クエリ、ソートに適する
Hashインデックス 等値クエリのみ、ソート情報なし、使用場面が限定
GINインデックス 転置インデックス、全文検索、JSONB、配列に適する
GiSTインデックス 汎用検索ツリー、地理空間データ、範囲タイプに適する
BRINインデックス ブロック範囲インデックス、物理的に順序付けられた大規模テーブルに適する、サイズ極小
部分インデックス（Partial Index） 条件を満たす行のみインデックス、サイズとメンテナンスコストを削減
カバリングインデックス（Covering Index） INCLUDE列で追加データを格納、Index-Only Scanを実現
並行インデックス作成（CONCURRENTLY） テーブルをロックせずにインデックスを作成、本番環境で必須

インデックスタイプ選択デシジョンツリー

クエリタイプ判定：
├── 等値クエリ（=） → B-tree（デフォルト）またはHash
├── 範囲クエリ（>、<、BETWEEN） → B-tree
├── 全文検索（tsvector） → GIN
├── JSONBクエリ（@>、?） → GIN
├── 配列包含（@>、&&） → GIN
├── 地理空間（ST_Contains） → GiST
├── 範囲重複（&&） → GiST
├── 物理順序大規模テーブル（時系列） → BRIN
└── あいまい検索（LIKE 'abc%'） → B-tree（プレフィックスマッチ）

問題分析：PostgreSQLインデックス最適化の5つの課題

  1. インデックス選択の難しさ：5つのインデックスタイプにそれぞれ適用場面があり、タイプ選択ミスでフルテーブルスキャンに
  2. インデックス無効化：関数変換、暗黙の型キャスト、OR条件でインデックスが使用不可に
  3. 実行計画の複雑さ：EXPLAIN ANALYZE出力が複雑、Seq Scan vs Index Scanの選択が不明
  4. インデックス膨張：頻繁なUPDATE/DELETEでインデックスが断片化、クエリパフォーマンスが低下
  5. 本番インデックス作成のテーブルロック：CREATE INDEXのデフォルトで書き込みロック、大規模テーブルのインデックス作成でサービス停止

ステップバイステップ：6つのキーチューニング戦略

戦略1：B-treeインデックス——最も一般的な最適化

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(12,2),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ
);

INSERT INTO orders (user_id, status, total_amount, created_at)
SELECT
    (random() * 100000)::bigint,
    (ARRAY['pending','paid','shipped','completed','cancelled'])[floor(random()*5+1)::int],
    (random() * 10000)::decimal(12,2),
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 5000000);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345;

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'pending' AND created_at > '2025-01-01'
ORDER BY created_at DESC LIMIT 50;

戦略2：GINインデックス——JSONBと全文検索

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    search_vector TSVECTOR
);

INSERT INTO products (name, attributes, tags)
SELECT
    'Product ' || i,
    jsonb_build_object(
        'color', (ARRAY['red','blue','green','black','white'])[floor(random()*5+1)::int],
        'size', (ARRAY['S','M','L','XL'])[floor(random()*4+1)::int],
        'price', (random() * 500)::numeric(10,2),
        'in_stock', random() > 0.3
    ),
    ARRAY[(ARRAY['electronics','clothing','food','toys'])[floor(random()*4+1)::int]]
FROM generate_series(1, 1000000) AS i;

CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
CREATE INDEX idx_products_tags ON products USING GIN (tags);

EXPLAIN ANALYZE
SELECT * FROM products WHERE attributes @> '{"color": "red", "in_stock": true}';

EXPLAIN ANALYZE
SELECT * FROM products WHERE tags @> ARRAY['electronics'];

ALTER TABLE products ADD COLUMN search_vector TSVECTOR
    GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;

CREATE INDEX idx_products_search ON products USING GIN (search_vector);

EXPLAIN ANALYZE
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'product & 999')
ORDER BY ts_rank(search_vector, to_tsquery('english', 'product & 999')) DESC
LIMIT 20;

戦略3：BRINインデックス——時系列大規模テーブル

CREATE TABLE sensor_data (
    id BIGSERIAL,
    device_id INT NOT NULL,
    temperature DECIMAL(5,2),
    humidity DECIMAL(5,2),
    recorded_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (recorded_at);

CREATE TABLE sensor_data_2026_q1 PARTITION OF sensor_data
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE sensor_data_2026_q2 PARTITION OF sensor_data
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE sensor_data_2026_q3 PARTITION OF sensor_data
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE sensor_data_2026_q4 PARTITION OF sensor_data
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');

INSERT INTO sensor_data (device_id, temperature, humidity, recorded_at)
SELECT
    (random() * 100)::int,
    (15 + random() * 25)::decimal(5,2),
    (30 + random() * 50)::decimal(5,2),
    '2026-01-01'::timestamptz + (random() * interval '365 days')
FROM generate_series(1, 20000000);

CREATE INDEX idx_sensor_brin_recorded ON sensor_data
    USING BRIN (recorded_at) WITH (pages_per_range = 32);

CREATE INDEX idx_sensor_brin_device ON sensor_data
    USING BRIN (device_id) WITH (pages_per_range = 32);

EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE recorded_at BETWEEN '2026-03-01' AND '2026-03-15'
ORDER BY recorded_at;

SELECT
    pg_size_pretty(pg_relation_size('idx_sensor_brin_recorded')) AS brin_size,
    pg_size_pretty(pg_relation_size('sensor_data')) AS table_size;

戦略4：部分インデックスとカバリングインデックス

CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';

CREATE INDEX idx_orders_covering ON orders(user_id)
INCLUDE (status, total_amount, created_at);

EXPLAIN ANALYZE
SELECT user_id, status, total_amount, created_at
FROM orders WHERE user_id = 12345;

CREATE INDEX idx_orders_active_user ON orders(user_id, created_at DESC)
INCLUDE (status, total_amount)
WHERE status IN ('pending', 'paid', 'shipped');

EXPLAIN ANALYZE
SELECT user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 12345
  AND status IN ('pending', 'paid', 'shipped')
ORDER BY created_at DESC
LIMIT 20;

戦略5：EXPLAIN ANALYZE深掘り

EXPLAIN (ANYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, o.created_at
FROM orders o
JOIN (
    SELECT user_id, MAX(created_at) AS last_order
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
) latest ON o.user_id = latest.user_id AND o.created_at = latest.last_order
WHERE o.total_amount > 1000
ORDER BY o.total_amount DESC
LIMIT 50;

SELECT pg_stat_user_indexes.schemaname,
       pg_stat_user_indexes.relname AS table_name,
       pg_stat_user_indexes.indexrelname AS index_name,
       pg_stat_user_indexes.idx_scan AS index_scans,
       pg_stat_user_indexes.idx_tup_read AS tuples_read,
       pg_stat_user_indexes.idx_tup_fetch AS tuples_fetched,
       pg_indexes.indexdef AS index_definition
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE pg_stat_user_indexes.relname = 'orders'
ORDER BY pg_stat_user_indexes.idx_scan ASC;

SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan AS scans, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

戦略6：並行インデックス作成とメンテナンス

CREATE INDEX CONCURRENTLY idx_orders_updated_at ON orders(updated_at);

CREATE INDEX CONCURRENTLY idx_products_name_trgm ON products
    USING GIN (name gin_trgm_ops);

REINDEX INDEX CONCURRENTLY idx_orders_user_id;

VACUUM ANALYZE orders;

SELECT indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
       pg_size_pretty(pg_relation_size('orders')) AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

CREATE OR REPLACE FUNCTION maintain_indexes()
RETURNS void AS $$
DECLARE
    idx_record RECORD;
    bloat_ratio NUMERIC;
BEGIN
    FOR idx_record IN
        SELECT schemaname, relname, indexrelname, indexrelid
        FROM pg_stat_user_indexes
        WHERE schemaname = 'public'
    LOOP
        SELECT COALESCE(
            (pg_relation_size(idx_record.indexrelid)::numeric /
             NULLIF(pg_relation_size(
                (SELECT relfilenode FROM pg_class WHERE oid = idx_record.indexrelid)
             ), 0)) * 100,
            0
        ) INTO bloat_ratio;

        IF bloat_ratio > 50 THEN
            EXECUTE format('REINDEX INDEX CONCURRENTLY %I', idx_record.indexrelname);
            RAISE NOTICE 'Reindexed % (bloat: %%%)', idx_record.indexrelname, bloat_ratio;
        END IF;
    END LOOP;

    ANALYZE;
END;
$$ LANGUAGE plpgsql;

落とし穴ガイド

落とし穴1：インデックス列での関数使用でインデックスが無効化

-- ❌ 誤り：インデックス列に関数を使用、PostgreSQLがB-treeインデックスを使用できない
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-15';

-- ✅ 正しい：関数変換の代わりに範囲クエリを使用
SELECT * FROM orders
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16';

落とし穴2：暗黙の型キャストでインデックスが無効化

-- ❌ 誤り：varchar列とtextの比較で暗黙キャストが発生する可能性
SELECT * FROM orders WHERE status = 'pending'::text;

-- ✅ 正しい：比較型が列型と一致することを確認
SELECT * FROM orders WHERE status::text = 'pending';
-- またはより良い方法：同じ型を直接使用
SELECT * FROM orders WHERE status = 'pending';

落とし穴3：LIKE '%abc%'でB-treeインデックスが使用不可

-- ❌ 誤り：先行ワイルドカードでB-treeインデックスが使用不可
SELECT * FROM products WHERE name LIKE '%phone%';

-- ✅ 正しい：pg_trgm GINインデックスであいまい検索をサポート
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
SELECT * FROM products WHERE name % 'phone';

落とし穴4：OR条件でインデックス選択が困難に

-- ❌ 誤り：OR条件でオプティマイザがインデックスを放棄する可能性
SELECT * FROM orders WHERE user_id = 123 OR status = 'pending';

-- ✅ 正しい：UNION ALLでクエリを分割
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 123;

落とし穴5：過剰インデックスで書き込みパフォーマンスが低下

-- ❌ 誤り：全列にインデックスを作成、INSERT/UPDATEパフォーマンスが急減
CREATE INDEX idx_orders_col1 ON orders(col1);
CREATE INDEX idx_orders_col2 ON orders(col2);
CREATE INDEX idx_orders_col3 ON orders(col3);
CREATE INDEX idx_orders_col4 ON orders(col4);
CREATE INDEX idx_orders_col5 ON orders(col5);

-- ✅ 正しい：クエリで実際に使用する列のみインデックスを作成、未使用インデックスを定期的にクリーンアップ
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders' AND idx_scan = 0;

エラートラブルシューティング

# エラーメッセージ 原因 解決方法
1 could not create unique index, duplicate key ユニークインデックス列に重複値 先に重複を除去してからインデックス作成、またはCREATE UNIQUE INDEX ... WHEREを使用
2 index row size exceeds maximum GINインデックスの行が大きすぎる gin_pending_list_limitを使用またはデータを最適化
3 concurrent index creation failed CONCURRENTLY作成中にテーブルが変更された リトライ、長時間トランザクションのブロックを確認
4 cannot create index on partitioned table パーティションテーブルで直接インデックス作成不可 各パーティションに個別にインデックス作成、またはPG11+のパーティションインデックスを使用
5 operator does not exist: jsonb @> text JSONBクエリ演算子の型不一致 右側もjsonb型であることを確認：'{"key":"val"}'::jsonb
6 function gin_trgm_ops does not exist pg_trgm拡張が未インストール CREATE EXTENSION pg_trgm;
7 out of memory 大規模テーブルのインデックス作成でメモリ不足 maintenance_work_memを増加、またはCONCURRENTLYを使用
8 relation already exists インデックス名の競合 IF NOT EXISTSを使用またはインデックス名を変更
9 access exclusive lock インデックス作成中のテーブルロック CREATE INDEX CONCURRENTLYでロックを回避
10 index "xxx" is not valid CONCURRENTLY作成失敗後、インデックスが無効 DROP INDEX xxx;後、再作成

高度な最適化

1. インデックス使用率モニタリングダッシュボード

CREATE OR REPLACE VIEW index_health_dashboard AS
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    CASE
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_scan < 100 THEN 'LOW_USAGE'
        ELSE 'ACTIVE'
    END AS health_status,
    COALESCE(
        ROUND(
            idx_tup_fetch::numeric / NULLIF(idx_scan, 0),
            2
        ),
        0
    ) AS avg_tuples_per_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY
    CASE health_status
        WHEN 'UNUSED' THEN 0
        WHEN 'LOW_USAGE' THEN 1
        ELSE 2
    END,
    pg_relation_size(indexrelid) DESC;

SELECT * FROM index_health_dashboard LIMIT 20;

2. スロークエリ自動キャプチャとインデックス提案

ALTER SYSTEM SET log_min_duration_statement = 1000;
ALTER SYSTEM SET auto_explain.log_min_duration = 1000;
ALTER SYSTEM SET auto_explain.log_analyze = true;
SELECT pg_reload_conf();

CREATE TABLE slow_query_log (
    id BIGSERIAL PRIMARY KEY,
    query_text TEXT NOT NULL,
    duration_ms NUMERIC NOT NULL,
    plan_text TEXT,
    captured_at TIMESTAMPTZ DEFAULT NOW(),
    suggested_index TEXT
);

CREATE OR REPLACE FUNCTION capture_slow_query()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.duration_ms > 5000 THEN
        INSERT INTO slow_query_log (query_text, duration_ms, suggested_index)
        VALUES (
            NEW.query_text,
            NEW.duration_ms,
            CASE
                WHEN NEW.query_text ~* 'WHERE\s+\w+\s*=' THEN
                    'Consider index on equality column'
                WHEN NEW.query_text ~* 'ORDER BY' THEN
                    'Consider index on sort column'
                WHEN NEW.query_text ~* 'LIKE' THEN
                    'Consider pg_trgm GIN index'
                ELSE 'Review EXPLAIN ANALYZE'
            END
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. インデックス膨張検出と自動メンテナンス

CREATE OR REPLACE FUNCTION check_index_bloat(
    p_schema TEXT DEFAULT 'public',
    p_bloat_threshold NUMERIC DEFAULT 30
)
RETURNS TABLE(
    table_name TEXT,
    index_name TEXT,
    index_size TEXT,
    bloat_pct NUMERIC,
    action TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        schemaname::TEXT,
        indexrelname::TEXT,
        pg_size_pretty(pg_relation_size(indexrelid))::TEXT,
        COALESCE(
            ROUND(
                100.0 * (pg_relation_size(indexrelid) -
                    COALESCE(pg_stat_user_indexes.idx_tup_fetch, 0) * 8
                ) / NULLIF(pg_relation_size(indexrelid), 0),
                1
            ),
            0
        ),
        CASE
            WHEN COALESCE(
                ROUND(
                    100.0 * (pg_relation_size(indexrelid) -
                        COALESCE(pg_stat_user_indexes.idx_tup_fetch, 0) * 8
                    ) / NULLIF(pg_relation_size(indexrelid), 0),
                    1
                ),
                0
            ) > p_bloat_threshold
            THEN 'REINDEX CONCURRENTLY ' || quote_ident(indexrelname)
            ELSE 'OK'
        END::TEXT
    FROM pg_stat_user_indexes
    WHERE schemaname = p_schema
    ORDER BY pg_relation_size(indexrelid) DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM check_index_bloat('public', 30);

比較分析

次元 B-tree Hash GIN GiST BRIN
等値クエリ ⭐高速 ⭐高速 ⚠️低速 ⚠️低速 ⚠️大まか
範囲クエリ ⭐高速 ❌非対応 ❌非対応 ⚠️限定 ⚠️大まか
ソート ⭐対応 ❌非対応 ❌非対応 ❌非対応 ❌非対応
全文検索 ❌非対応 ❌非対応 ⭐最適 ⚠️可能 ❌非対応
JSONB ❌限定 ❌非対応 ⭐最適 ⚠️可能 ❌非対応
地理空間 ❌非対応 ❌非対応 ⚠️限定 ⭐最適 ❌非対応
インデックスサイズ 中 小 大 大 極小
構築速度 高速 高速 低速 低速 極高速
メンテナンスコスト 低 低 高 高 極低
対応データ量 M~B M M~B M B+

まとめ：PostgreSQLインデックス最適化は「インデックスを追加する」だけではありません——正しいタイプの選択が前提、実行計画の理解が鍵、インデックス無効化の回避がベースライン。6つのキー戦略は段階的に構築：1）B-tree基礎最適化、2）GINでJSONB/全文検索、3）BRINで時系列大規模テーブル、4）部分・カバリングインデックスでサイズ削減、5）EXPLAIN ANALYZE深掘り、6）並行インデックス作成と自動メンテナンス。コア原則：インデックスは多いほど良いわけではない——各インデックスに書き込みコストがある。pg_stat_user_indexesで未使用インデックスを定期的にクリーンアップ、CONCURRENTLYでテーブルロックを回避。

オンラインツール推奨

ブラウザローカルツールを無料で試す →

#PostgreSQL#索引优化#慢查询#数据库性能#SQL调优#2026#执行计划