PostgreSQL インデックス確認の完全ガイド：隠れたパフォーマンス問題を発見して解決する方法

「クエリが遅いけど、インデックスは張ってあるはず…」 「どのインデックスが実際に使われているか分からない」 「無駄なインデックスでディスク容量を圧迫しているかも」

PostgreSQLを運用していて、こんな不安を感じたことはありませんか？

実は、インデックスは作っただけでは意味がありません。適切に使われているか、重複していないか、メンテナンスが必要でないか、定期的な確認が必要です。インデックスの状態を正しく把握できれば、クエリの実行時間を10倍から100倍高速化することも可能です。

この記事では、PostgreSQLのインデックスを確認するすべての方法から、問題の発見と解決まで、実例とクエリサンプルを交えながら徹底的に解説していきます。

---

🔍 インデックスの基本的な確認方法

方法1：psqlメタコマンドで素早く確認

\d コマンドでテーブル情報と一緒に確認

基本的な使い方：

-- テーブルの構造とインデックスを確認
\d users

-- 実行結果
                Table "public.users"
    Column    |           Type           | Nullable |
--------------+-------------------------+----------+
 user_id      | integer                 | not null |
 username     | character varying(50)   | not null |
 email        | character varying(100)  | not null |
 created_at   | timestamp               |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "idx_users_email" UNIQUE, btree (email)
    "idx_users_created" btree (created_at)
    "idx_users_username_lower" btree (lower((username)::text))

\di コマンドで全インデックス一覧

インデックスのみを表示：

-- データベース内の全インデックス
\di

-- 特定スキーマのインデックス
\di public.*

-- パターンマッチで絞り込み
\di *users*

-- 詳細情報も表示
\di+

実行結果の例：

                          List of relations
 Schema |            Name            | Type  |  Owner   |  Table  | Size  |
--------+---------------------------+-------+----------+---------+-------+
 public | idx_orders_user_date      | index | postgres | orders  | 21 MB |
 public | idx_products_category     | index | postgres | products| 8192 bytes |
 public | users_pkey                | index | postgres | users   | 240 kB |

方法2：システムカタログで詳細情報を取得

pg_indexesビューで確認

全インデックスの詳細一覧：

-- インデックスの定義を確認
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- 実行結果例
schemaname | tablename | indexname              | indexdef
-----------+-----------+-----------------------+------------------------------------------
public     | orders    | idx_orders_user_date  | CREATE INDEX idx_orders_user_date ON orders USING btree (user_id, order_date)
public     | orders    | orders_pkey           | CREATE UNIQUE INDEX orders_pkey ON orders USING btree (order_id)
public     | products  | idx_products_category | CREATE INDEX idx_products_category ON products USING btree (category)

より詳細な情報を取得

インデックスの全属性を確認：

SELECT 
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name,
    ix.indisunique as is_unique,
    ix.indisprimary as is_primary,
    ix.indisvalid as is_valid,
    pg_size_pretty(pg_relation_size(i.oid)) as index_size,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.oid
WHERE n.nspname = 'public'
ORDER BY 
    n.nspname,
    t.relname,
    i.relname,
    array_position(ix.indkey, a.attnum);

---

📊 インデックスの使用状況を分析

使われているインデックスの特定

pg_stat_user_indexesで使用統計を確認

インデックスの利用頻度を分析：

-- 使用回数順でインデックスをランキング
SELECT 
    schemaname,
    tablename,
    indexrelname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read_via_index,
    idx_tup_fetch as tuples_fetched_via_index,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    CASE 
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_scan < 10 THEN 'RARELY USED'
        WHEN idx_scan < 100 THEN 'OCCASIONALLY USED'
        WHEN idx_scan < 1000 THEN 'FREQUENTLY USED'
        ELSE 'HEAVILY USED'
    END as usage_category
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

実行結果の例：

tablename | indexrelname          | index_scans | index_size | usage_category
----------+----------------------+-------------+------------+----------------
orders    | idx_orders_user_date | 45823       | 21 MB      | HEAVILY USED
users     | users_pkey           | 12456       | 240 kB     | HEAVILY USED
products  | idx_products_name    | 89          | 156 kB     | OCCASIONALLY USED
audit_log | idx_audit_created    | 0           | 45 MB      | UNUSED

未使用インデックスの発見

無駄なインデックスを特定

削除候補のインデックス：

-- 30日以上使われていないインデックス
WITH index_usage AS (
    SELECT 
        s.schemaname,
        s.tablename,
        s.indexrelname,
        s.idx_scan,
        pg_size_pretty(pg_relation_size(s.indexrelid)) as index_size,
        pg_relation_size(s.indexrelid) as size_bytes,
        COALESCE(
            DATE_PART('day', NOW() - pg_stat_get_last_vacuum_time(c.oid)),
            999
        ) as days_since_last_vacuum
    FROM pg_stat_user_indexes s
    JOIN pg_index i ON s.indexrelid = i.indexrelid
    JOIN pg_class c ON s.indexrelid = c.oid
    WHERE NOT i.indisprimary  -- プライマリキーは除外
    AND NOT i.indisunique      -- ユニーク制約も除外
)
SELECT 
    schemaname,
    tablename,
    indexrelname,
    idx_scan,
    index_size,
    days_since_last_vacuum,
    'DROP INDEX ' || schemaname || '.' || indexrelname || ';' as drop_command
FROM index_usage
WHERE idx_scan = 0
AND days_since_last_vacuum > 30
ORDER BY size_bytes DESC;

インデックスの効率性分析

インデックスのヒット率を測定

キャッシュヒット率とインデックス効率：

-- インデックスの効率性を総合評価
WITH index_stats AS (
    SELECT 
        n.nspname as schema_name,
        t.relname as table_name,
        i.relname as index_name,
        idx.idx_scan,
        idx.idx_tup_read,
        idx.idx_tup_fetch,
        pg_relation_size(i.oid) as index_size_bytes,
        pg_relation_size(t.oid) as table_size_bytes,
        COALESCE(idx.idx_scan, 0) as scans,
        COALESCE(idx.idx_tup_read, 0) as tuples_read,
        COALESCE(idx.idx_tup_fetch, 0) as tuples_fetched
    FROM pg_index ix
    JOIN pg_class t ON t.oid = ix.indrelid
    JOIN pg_class i ON i.oid = ix.indexrelid
    JOIN pg_namespace n ON n.oid = t.relnamespace
    LEFT JOIN pg_stat_user_indexes idx ON idx.indexrelid = i.oid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT 
    schema_name,
    table_name,
    index_name,
    scans as scan_count,
    pg_size_pretty(index_size_bytes) as index_size,
    pg_size_pretty(table_size_bytes) as table_size,
    ROUND((index_size_bytes::numeric / NULLIF(table_size_bytes, 0)) * 100, 2) as index_table_ratio,
    CASE 
        WHEN scans = 0 THEN 0
        ELSE ROUND((tuples_fetched::numeric / NULLIF(tuples_read, 0)) * 100, 2)
    END as selectivity_percent,
    CASE 
        WHEN scans = 0 THEN 'Never Used'
        WHEN tuples_read = 0 THEN 'No Reads'
        WHEN (tuples_fetched::numeric / NULLIF(tuples_read, 0)) < 0.01 THEN 'Poor Selectivity'
        WHEN (tuples_fetched::numeric / NULLIF(tuples_read, 0)) < 0.1 THEN 'Low Selectivity'
        WHEN (tuples_fetched::numeric / NULLIF(tuples_read, 0)) < 0.5 THEN 'Medium Selectivity'
        ELSE 'Good Selectivity'
    END as efficiency_rating
FROM index_stats
ORDER BY scan_count DESC;

---

🔧 特殊なインデックスの確認

部分インデックスの確認

条件付きインデックスを特定：

-- 部分インデックス（WHERE句付き）を確認
SELECT 
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    pg_get_expr(ix.indpred, ix.indrelid) as where_condition,
    pg_size_pretty(pg_relation_size(i.oid)) as index_size
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE ix.indpred IS NOT NULL
ORDER BY n.nspname, t.relname;

-- 実行結果例
schema_name | table_name | index_name              | where_condition          | index_size
------------|------------|------------------------|--------------------------|------------
public      | orders     | idx_orders_pending     | (status = 'pending')     | 5 MB
public      | users      | idx_users_active       | (deleted_at IS NULL)     | 120 kB

関数インデックスの確認

式インデックスを特定：

-- 関数・式インデックスの詳細
SELECT 
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    pg_get_indexdef(i.oid) as index_definition,
    pg_size_pretty(pg_relation_size(i.oid)) as index_size
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE pg_get_indexdef(i.oid) LIKE '%(%(%'  -- 関数を含む
ORDER BY n.nspname, t.relname;

-- 実行結果例
schema_name | table_name | index_name                | index_definition
------------|------------|--------------------------|------------------------------------------
public      | users      | idx_users_email_lower    | CREATE INDEX ... ON users (lower(email))
public      | products   | idx_products_name_gin    | CREATE INDEX ... USING gin (to_tsvector('english', name))

複合インデックスの分析

マルチカラムインデックスの確認：

-- 複合インデックスとカラム順序
SELECT 
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    ARRAY_AGG(a.attname ORDER BY array_position(ix.indkey, a.attnum)) as columns,
    pg_size_pretty(pg_relation_size(i.oid)) as index_size,
    idx.idx_scan as scan_count
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
LEFT JOIN pg_stat_user_indexes idx ON idx.indexrelid = i.oid
WHERE array_length(ix.indkey, 1) > 1  -- 複数カラム
GROUP BY n.nspname, t.relname, i.relname, i.oid, idx.idx_scan
ORDER BY scan_count DESC NULLS LAST;

---

🚀 パフォーマンス問題の発見

肥大化したインデックスの検出

BLOAT（肥大化）の確認：

-- インデックスの肥大化率を計算
WITH btree_index_atts AS (
    SELECT 
        nspname,
        indexclass.relname as index_name,
        indexclass.relpages,
        indrelid,
        indexrelid,
        indexclass.reltuples,
        indnatts,
        array_agg(atttypid::regtype) as atttypes
    FROM pg_index
    JOIN pg_class indexclass ON pg_index.indexrelid = indexclass.oid
    JOIN pg_namespace ON indexclass.relnamespace = pg_namespace.oid
    JOIN pg_attribute ON indexrelid = attrelid
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND indexclass.relpages > 0
    AND indisvalid
    GROUP BY 1,2,3,4,5,6,7
),
index_bloat AS (
    SELECT 
        nspname,
        index_name,
        relpages,
        ROUND((
            CASE WHEN relpages > 0 
            THEN (bs * (relpages))::numeric 
            ELSE 0 END
        ) / 1024 / 1024, 2) as actual_size_mb,
        ROUND((
            CASE WHEN reltuples > 0
            THEN (bs * (CEIL(reltuples * 
                (6 + ma - (CASE WHEN ma%2 = 0 THEN 24 ELSE 23 END) + 
                (SELECT SUM(
                    CASE 
                    WHEN atttypes[i] IN ('int2'::regtype, 'int4'::regtype, 'int8'::regtype,
                                        'float4'::regtype, 'float8'::regtype, 'oid'::regtype)
                    THEN ma - (CASE WHEN ma%2 = 0 THEN 24 ELSE 23 END)
                    ELSE 2 + ma
                    END
                ) FROM generate_series(1, indnatts) i
                )) / (bs - 20)::numeric))
            ELSE 0 END
        ) / 1024 / 1024, 2) as expected_size_mb
    FROM btree_index_atts,
    LATERAL (SELECT current_setting('block_size')::numeric AS bs, 24 AS ma) AS constants
)
SELECT 
    nspname as schema,
    index_name,
    actual_size_mb,
    expected_size_mb,
    actual_size_mb - expected_size_mb as bloat_mb,
    ROUND(((actual_size_mb - expected_size_mb) / NULLIF(actual_size_mb, 0)) * 100, 2) as bloat_percentage
FROM index_bloat
WHERE actual_size_mb > 10  -- 10MB以上のインデックスのみ
AND ((actual_size_mb - expected_size_mb) / NULLIF(actual_size_mb, 0)) > 0.2  -- 20%以上の肥大化
ORDER BY bloat_mb DESC;

重複インデックスの発見

同じカラムに複数のインデックス：

-- 重複の可能性があるインデックスを検出
WITH index_columns AS (
    SELECT 
        n.nspname as schema_name,
        t.relname as table_name,
        i.relname as index_name,
        ARRAY_AGG(a.attname ORDER BY array_position(ix.indkey, a.attnum)) as columns,
        ix.indisunique as is_unique,
        ix.indisprimary as is_primary
    FROM pg_index ix
    JOIN pg_class t ON t.oid = ix.indrelid
    JOIN pg_class i ON i.oid = ix.indexrelid
    JOIN pg_namespace n ON n.oid = t.relnamespace
    JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    GROUP BY n.nspname, t.relname, i.relname, ix.indisunique, ix.indisprimary
)
SELECT 
    ic1.schema_name,
    ic1.table_name,
    ic1.index_name as index1,
    ic1.columns as columns1,
    ic2.index_name as index2,
    ic2.columns as columns2,
    CASE 
        WHEN ic1.columns = ic2.columns THEN 'EXACT DUPLICATE'
        WHEN ic1.columns @> ic2.columns THEN 'INDEX1 COVERS INDEX2'
        WHEN ic1.columns <@ ic2.columns THEN 'INDEX2 COVERS INDEX1'
        ELSE 'PARTIAL OVERLAP'
    END as relationship
FROM index_columns ic1
JOIN index_columns ic2 
    ON ic1.schema_name = ic2.schema_name 
    AND ic1.table_name = ic2.table_name
    AND ic1.index_name < ic2.index_name
WHERE (ic1.columns @> ic2.columns OR ic1.columns <@ ic2.columns OR ic1.columns && ic2.columns)
ORDER BY ic1.schema_name, ic1.table_name, ic1.index_name;

---

📈 インデックスの健全性チェック

壊れたインデックスの検出

無効なインデックスを確認：

-- 無効化されたインデックス
SELECT 
    n.nspname as schema_name,
    t.relname as table_name,
    i.relname as index_name,
    ix.indisvalid as is_valid,
    ix.indisready as is_ready,
    ix.indislive as is_live,
    pg_size_pretty(pg_relation_size(i.oid)) as index_size,
    CASE 
        WHEN NOT ix.indisvalid THEN 'INVALID - Needs REINDEX'
        WHEN NOT ix.indisready THEN 'NOT READY - Build in progress'
        WHEN NOT ix.indislive THEN 'NOT LIVE - Being dropped'
        ELSE 'OK'
    END as status
FROM pg_index ix
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE NOT ix.indisvalid 
   OR NOT ix.indisready 
   OR NOT ix.indislive
ORDER BY n.nspname, t.relname;

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

最後のVACUUM/ANALYZEを確認：

-- インデックスメンテナンスの状況
SELECT 
    schemaname,
    tablename,
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    COALESCE(
        DATE_PART('day', NOW() - pg_stat_get_last_vacuum_time(indexrelid)),
        999
    ) as days_since_vacuum,
    COALESCE(
        DATE_PART('day', NOW() - pg_stat_get_last_analyze_time(indexrelid)), 
        999
    ) as days_since_analyze,
    CASE 
        WHEN DATE_PART('day', NOW() - pg_stat_get_last_vacuum_time(indexrelid)) > 30 
        THEN 'NEEDS VACUUM'
        WHEN DATE_PART('day', NOW() - pg_stat_get_last_analyze_time(indexrelid)) > 7
        THEN 'NEEDS ANALYZE'
        ELSE 'OK'
    END as maintenance_status
FROM pg_stat_user_indexes
ORDER BY days_since_vacuum DESC;

---

💡 実行計画でインデックス使用を確認

EXPLAINでインデックス利用を検証

基本的な実行計画の確認

インデックスが使われているか確認：

-- 実行計画を確認
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users 
WHERE email = 'user@example.com';

-- 実行結果（インデックスが使われている場合）
Index Scan using idx_users_email on public.users
  Output: user_id, username, email, created_at
  Index Cond: (users.email = 'user@example.com'::text)
  Buffers: shared hit=4
Planning Time: 0.087 ms
Execution Time: 0.025 ms

-- 実行結果（インデックスが使われていない場合）
Seq Scan on public.users
  Output: user_id, username, email, created_at
  Filter: (users.email = 'user@example.com'::text)
  Rows Removed by Filter: 99999
  Buffers: shared hit=1425
Planning Time: 0.065 ms
Execution Time: 45.234 ms

インデックスが使われない原因の特定

よくある原因と対処法：

-- 1. データ型の不一致
-- 悪い例（型変換でインデックス無効）
EXPLAIN SELECT * FROM orders WHERE order_id = '1000';  -- 文字列で比較

-- 良い例（正しい型で比較）
EXPLAIN SELECT * FROM orders WHERE order_id = 1000;  -- 数値で比較

-- 2. 関数の適用
-- 悪い例（関数でインデックス無効）
EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 良い例（関数インデックスを作成）
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 3. LIKEの前方一致以外
-- 悪い例（中間一致でインデックス無効）
EXPLAIN SELECT * FROM products WHERE name LIKE '%laptop%';

-- 良い例（前方一致なら使える）
EXPLAIN SELECT * FROM products WHERE name LIKE 'laptop%';

-- または全文検索インデックス
CREATE INDEX idx_products_name_gin ON products USING gin(to_tsvector('english', name));

---

🛠️ インデックスの問題を解決

REINDEXによる再構築

肥大化したインデックスの修復：

-- 単一インデックスの再構築
REINDEX INDEX idx_orders_user_date;

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

-- 並行処理で再構築（ロックを最小化）
REINDEX INDEX CONCURRENTLY idx_orders_user_date;

-- 進捗を確認
SELECT 
    pid,
    datname,
    query,
    state,
    wait_event,
    pg_blocking_pids(pid) as blocked_by,
    EXTRACT(EPOCH FROM (NOW() - query_start))::INT as duration_seconds
FROM pg_stat_activity
WHERE query LIKE '%REINDEX%'
AND pid != pg_backend_pid();

不要インデックスの削除

安全な削除手順：

-- 削除前の影響確認スクリプト
DO $$
DECLARE
    v_index_name TEXT := 'idx_audit_created';
    v_scan_count BIGINT;
BEGIN
    -- 使用回数を確認
    SELECT idx_scan INTO v_scan_count
    FROM pg_stat_user_indexes
    WHERE indexrelname = v_index_name;
    
    IF v_scan_count = 0 THEN
        RAISE NOTICE 'Index % has never been used. Safe to drop.', v_index_name;
    ELSE
        RAISE WARNING 'Index % has been used % times!', v_index_name, v_scan_count;
    END IF;
END $$;

-- 削除実行
DROP INDEX IF EXISTS idx_audit_created;

-- または無効化して様子見
UPDATE pg_index 
SET indisvalid = false 
WHERE indexrelid = 'idx_audit_created'::regclass;

---

📊 モニタリングとアラート

定期的なチェックスクリプト

日次インデックス健全性レポート：

CREATE OR REPLACE FUNCTION index_health_report()
RETURNS TABLE(
    category TEXT,
    issue TEXT,
    details TEXT,
    action_required TEXT
) AS $$
BEGIN
    -- 未使用インデックス
    RETURN QUERY
    SELECT 
        'UNUSED INDEX'::TEXT,
        indexrelname::TEXT,
        ('Size: ' || pg_size_pretty(pg_relation_size(indexrelid)) || 
         ', Last used: Never')::TEXT,
        ('Consider: DROP INDEX ' || schemaname || '.' || indexrelname)::TEXT
    FROM pg_stat_user_indexes
    WHERE idx_scan = 0
    AND schemaname = 'public';
    
    -- 肥大化インデックス
    RETURN QUERY
    WITH bloated AS (
        SELECT 
            i.indexrelname,
            pg_relation_size(i.indexrelid) as actual_size,
            GREATEST(8192, pg_relation_size(i.indexrelid) * 0.3) as expected_size
        FROM pg_stat_user_indexes i
    )
    SELECT 
        'BLOATED INDEX'::TEXT,
        indexrelname::TEXT,
        ('Bloat: ' || pg_size_pretty((actual_size - expected_size)::BIGINT))::TEXT,
        ('Consider: REINDEX INDEX ' || indexrelname)::TEXT
    FROM bloated
    WHERE actual_size > expected_size * 1.5;
    
    -- 無効インデックス
    RETURN QUERY
    SELECT 
        'INVALID INDEX'::TEXT,
        i.relname::TEXT,
        'Index is marked as invalid'::TEXT,
        ('Required: REINDEX INDEX ' || i.relname)::TEXT
    FROM pg_index ix
    JOIN pg_class i ON i.oid = ix.indexrelid
    WHERE NOT ix.indisvalid;
    
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- レポート実行
SELECT * FROM index_health_report();

自動アラート設定

問題のあるインデックスを通知：

-- cronで定期実行するスクリプト
CREATE OR REPLACE FUNCTION check_index_issues()
RETURNS TEXT AS $$
DECLARE
    v_issues TEXT := '';
    v_count INTEGER := 0;
BEGIN
    -- 50MB以上の未使用インデックスをチェック
    SELECT COUNT(*), STRING_AGG(indexrelname || ' (' || 
           pg_size_pretty(pg_relation_size(indexrelid)) || ')', ', ')
    INTO v_count, v_issues
    FROM pg_stat_user_indexes
    WHERE idx_scan = 0
    AND pg_relation_size(indexrelid) > 50 * 1024 * 1024;
    
    IF v_count > 0 THEN
        RETURN 'WARNING: ' || v_count || ' large unused indexes found: ' || v_issues;
    END IF;
    
    RETURN 'OK: No index issues detected';
END;
$$ LANGUAGE plpgsql;

---

🎯 ベストプラクティス

インデックス確認のチェックリスト

定期メンテナンスで確認すべき項目：

-- 包括的なインデックス診断クエリ
WITH index_summary AS (
    SELECT 
        n.nspname as schema_name,
        t.relname as table_name,
        i.relname as index_name,
        idx.idx_scan as scan_count,
        pg_size_pretty(pg_relation_size(i.oid)) as index_size,
        pg_relation_size(i.oid) as size_bytes,
        ix.indisunique as is_unique,
        ix.indisprimary as is_primary,
        ix.indisvalid as is_valid,
        array_length(ix.indkey, 1) as column_count,
        CASE 
            WHEN idx.idx_scan = 0 THEN 'UNUSED'
            WHEN idx.idx_scan < 100 THEN 'RARELY_USED'
            ELSE 'ACTIVE'
        END as usage_status
    FROM pg_index ix
    JOIN pg_class t ON t.oid = ix.indrelid
    JOIN pg_class i ON i.oid = ix.indexrelid
    JOIN pg_namespace n ON n.oid = t.relnamespace
    LEFT JOIN pg_stat_user_indexes idx ON idx.indexrelid = i.oid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT 
    usage_status,
    COUNT(*) as index_count,
    pg_size_pretty(SUM(size_bytes)) as total_size,
    ROUND(AVG(column_count), 2) as avg_columns,
    SUM(CASE WHEN is_primary THEN 1 ELSE 0 END) as primary_keys,
    SUM(CASE WHEN is_unique AND NOT is_primary THEN 1 ELSE 0 END) as unique_indexes,
    SUM(CASE WHEN NOT is_valid THEN 1 ELSE 0 END) as invalid_indexes
FROM index_summary
GROUP BY usage_status
ORDER BY 
    CASE usage_status
        WHEN 'UNUSED' THEN 1
        WHEN 'RARELY_USED' THEN 2
        ELSE 3
    END;

推奨される確認頻度

環境別の確認スケジュール：

環境	基本確認	詳細分析	REINDEX
本番環境	毎日	週1回	月1回
ステージング	週1回	月1回	必要時
開発環境	月1回	必要時	必要時

---

📚 まとめ：インデックスの確認で性能問題を防ぐ

PostgreSQLのインデックス確認の要点：

✅ psqlメタコマンドとシステムビューで素早く確認 ✅ pg_stat_user_indexesで使用状況を分析 ✅ 未使用・重複・肥大化したインデックスを定期的に発見 ✅ EXPLAIN ANALYZEで実際の使用を検証 ✅ 定期的なメンテナンスで健全性を保つ

インデックスは作成するだけでなく、継続的な監視とメンテナンスが重要です。

今すぐ実践すべき3つのアクション：

1. \di+ コマンドで全インデックスのサイズを確認
2. pg_stat_user_indexesで未使用インデックスを特定
3. EXPLAIN ANALYZEで重要なクエリのインデックス使用を確認

これらの確認作業を習慣化することで、データベースのパフォーマンスを最適な状態に保てます！

---

この記事が役立ったら、データベース管理で苦労している仲間にもシェアしてください。一緒にPostgreSQLマスターを目指しましょう！