第7章: インデックスと実行計画 — B+tree・GIN・BRIN・EXPLAIN ANALYZE

-- GIN: 全文検索インデックス(PostgreSQL tsvector)
CREATE INDEX idx_articles_fts ON articles
USING GIN (to_tsvector('japanese', title || ' ' || body));

SELECT * FROM articles
WHERE to_tsvector('japanese', title || ' ' || body) @@ to_tsquery('SQL');

-- BRIN: 時系列巨大テーブルで神(ログが書かれた順=timestampの順なので)
CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at);
-- 物理ブロックごとにmin/maxを持つだけのごく小さなインデックス
-- 10億行でも数MBで済み、時系列範囲検索が劇的に速くなる

-- 部分インデックス(Partial Index): 条件付きで小さく保つ
CREATE INDEX idx_active_users ON users (email)
WHERE status = 'active';
-- statusがactiveなユーザーのみインデックス化 → サイズが激減

-- 関数インデックス: 式に対してインデックス
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

-- ❌ 関数適用でインデックスが効かない
CREATE INDEX idx_email ON users (email);
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';  -- Seq Scan

-- ✅ 関数インデックスに置き換える
CREATE INDEX idx_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';  -- Index Scan

-- ❌ 複合インデックス(a, b)で b だけを条件にしてもダメ
CREATE INDEX idx_ab ON t (a, b);
SELECT * FROM t WHERE b = 1;  -- Seq Scan (a が条件に無いから)

-- ✅ 左端の列を含める or (b, a) の順で作り直す
SELECT * FROM t WHERE a = 1 AND b = 2;  -- Index Scan

-- PostgreSQLでの典型的な読み方
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.name;

-- 出力例(抜粋):
-- HashAggregate  (cost=1234.56..1456.78 rows=100 width=40)
--                (actual time=10.2..12.5 rows=98 loops=1)
--   Group Key: u.name
--   ->  Hash Left Join  (cost=...)
--                       (actual time=...)
--         Hash Cond: (o.user_id = u.id)
--         ->  Seq Scan on orders o  (...)
--         ->  Hash  (...)
--               ->  Index Scan using idx_users_created ON users u
--                     Index Cond: (created_at >= '2026-01-01')