필사 모드: インデックスを作ったのに使われない理由 — オプティマイザが正しく自分が間違っている場合

-- オプティマイザの選択
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..20117.00 rows=612430 width=88)
                    (actual time=0.014..184.221 rows=611884 loops=1)
  Filter: (status = 'completed')
  Rows Removed by Filter: 188116
  Buffers: shared hit=1211 read=10906
Execution Time: 231.774 ms

-- インデックスを強制した結果
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';
RESET enable_seqscan;

Index Scan using idx_orders_status on orders
  (cost=0.42..68914.33 rows=612430 width=88)
  (actual time=0.061..1042.883 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Buffers: shared hit=8842 read=598112
Execution Time: 1094.201 ms

-- SSD環境での現実的な値
ALTER SYSTEM SET random_page_cost = 1.1;
SELECT pg_reload_conf();

SELECT attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('created_at', 'user_id', 'status');

  attname   | n_distinct | correlation
------------+------------+-------------
 created_at |         -1 |    0.998304
 user_id    |      48210 |    0.003911
 status     |          6 |    0.412008

-- INCLUDEでカバリングインデックスを作る (PostgreSQL 11以上)
CREATE INDEX CONCURRENTLY idx_orders_status_covering
  ON orders (status) INCLUDE (id, total, created_at);

Index Only Scan using idx_orders_status_covering on orders
  (actual time=0.038..142.118 rows=611884 loops=1)
  Index Cond: (status = 'completed')
  Heap Fetches: 1204

-- 使われない: カラムに関数がかかっている
SELECT * FROM orders WHERE date(created_at) = '2026-07-26';

-- 使われる: 範囲条件に変えてカラムをそのまま残す
SELECT * FROM orders
WHERE created_at >= '2026-07-26'
  AND created_at <  '2026-07-27';

-- 使われない: カラム側に演算
SELECT * FROM order_items WHERE price * quantity > 100000;

-- 使われる: 生成カラムか式インデックスへ移す
CREATE INDEX idx_items_amount ON order_items ((price * quantity));

CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 使われる
SELECT * FROM users WHERE lower(email) = 'a@example.com';

-- 使われない: 式が違う
SELECT * FROM users WHERE lower(trim(email)) = 'a@example.com';

-- codeカラムがvarchar、インデックスもvarchar基準
EXPLAIN SELECT * FROM products WHERE code = 12345;

ERROR:  operator does not exist: character varying = integer

-- idはbigintなのにパラメータがnumericで入ってきた場合
EXPLAIN SELECT * FROM orders WHERE id = 1001::numeric;

Seq Scan on orders  (cost=0.00..24117.00 rows=4001 width=88)
  Filter: ((id)::numeric = '1001'::numeric)

-- 使われる: 接頭辞が固定されるのでインデックスの範囲探索が可能
SELECT * FROM users WHERE name LIKE 'kim%';

-- 使われない: 開始点が分からないので全体を見なければならない
SELECT * FROM users WHERE name LIKE '%kim%';

-- pg_trgm: 部分文字列検索をインデックスで処理する
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm
  ON users USING gin (name gin_trgm_ops);

EXPLAIN ANALYZE SELECT * FROM users WHERE name LIKE '%kim%';

Bitmap Heap Scan on users  (actual time=2.114..8.902 rows=412 loops=1)
  Recheck Cond: (name ~~ '%kim%'::text)
  ->  Bitmap Index Scan on idx_users_name_trgm  (actual time=1.884..1.884 rows=498 loops=1)
        Index Cond: (name ~~ '%kim%'::text)

CREATE INDEX idx_users_name_prefix
  ON users (name text_pattern_ops);

CREATE INDEX idx_orders_multi ON orders (user_id, status, created_at);

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '01012345678';

Bitmap Heap Scan on users  (actual time=0.061..0.064 rows=2 loops=1)
  Recheck Cond: ((email = 'a@b.com') OR (phone = '01012345678'))
  ->  BitmapOr  (actual time=0.052..0.052 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_users_email  (actual time=0.031..0.031 rows=1 loops=1)
        ->  Bitmap Index Scan on idx_users_phone  (actual time=0.019..0.019 rows=1 loops=1)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE cancelled_at IS NULL;

Index Scan using idx_orders_cancelled_at on orders
  (actual time=0.022..1.884 rows=304 loops=1)
  Index Cond: (cancelled_at IS NULL)

-- 最後のANALYZE時点とその後の変更量を確認
SELECT relname,
       n_live_tup,
       n_mod_since_analyze,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

 relname | n_live_tup | n_mod_since_analyze |    last_analyze     | last_autoanalyze
---------+------------+---------------------+---------------------+------------------
 orders  |     804112 |              611903 | 2026-07-19 03:12:44 | 2026-07-19 03:12:44

-- 大きなテーブルはテーブル単位で閾値を下げる
ALTER TABLE orders SET (
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

-- バッチロードの直後には明示的に更新する
ANALYZE orders;

-- 未処理の注文だけを検索するワークロード
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- サイズの比較
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders';

      indexrelname       |  size
-------------------------+---------
 idx_orders_status       | 42 MB
 idx_orders_pending      | 312 kB

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

 table_name | index_name                 | idx_scan |  size
------------+----------------------------+----------+--------
 orders     | idx_orders_updated_at      |        0 | 88 MB
 events     | idx_events_legacy_type     |        0 | 41 MB

-- 統計収集の開始時点を確認
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- 削除せず計画から外して観察したい場合 (カタログの直接修正。運用では推奨しない)
UPDATE pg_index SET indisvalid = false
WHERE indexrelid = 'idx_orders_updated_at'::regclass;

SELECT indexdef FROM pg_indexes WHERE indexname = 'idx_orders_updated_at';
DROP INDEX CONCURRENTLY idx_orders_updated_at;