✍️ 필사 모드: DBインデックス完全ガイド 2025: B-Tree, Hash, GIN, BRIN, 複合インデックス, 実行計画解析

SELECT * FROM users WHERE email = 'alice@example.com';

1億行 x 100 nanosecond = 10秒

1億行 -> log2(10^8) ~= 27回比較
27 x 100 ns = 2.7 microsecond

                [50]
               /    \
          [25]      [75]
         /    \    /    \
      [10]  [40] [60]  [90]

PostgreSQL B-Tree page (8KB)
+----------------------------------+
| Header                           |
+----------------------------------+
| Item Pointer 1 -> key1 + ctid    |
| Item Pointer 2 -> key2 + ctid    |
+----------------------------------+
| Free Space                       |
+----------------------------------+
| Items (low to high)              |
+----------------------------------+

CREATE INDEX idx_users_email ON users(email);

CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- 配列
CREATE INDEX idx_tags ON posts USING gin(tags);

-- JSONB
CREATE INDEX idx_metadata ON products USING gin(metadata);

-- 全文検索
CREATE INDEX idx_content_fts ON articles USING gin(to_tsvector('english', content));

"redis"    -> [row1, row5, row100, ...]
"kafka"    -> [row3, row7, row50, ...]
"postgres" -> [row1, row2, row100, ...]

SELECT * FROM posts WHERE tags @> ARRAY['redis'];
SELECT * FROM posts WHERE tags @> ARRAY['redis', 'database'];
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';

CREATE INDEX idx_locations ON places USING gist(location);
CREATE INDEX idx_periods ON events USING gist(period);

CREATE INDEX idx_logs_created ON logs USING brin(created_at);

CREATE INDEX idx_ips ON connections USING spgist(ip_addr);

CREATE INDEX idx_users_country_age ON users(country, age);

WHERE country = 'KR'
WHERE country = 'KR' AND age = 30
WHERE country = 'KR' AND age > 25

WHERE age = 30    -- countryが先頭
WHERE age > 25

WHERE country = 'KR' AND age > 25 AND created_at > '2024-01-01'

CREATE INDEX idx_users_search ON users(country, created_at, age);

WHERE country = 'KR' AND age BETWEEN 25 AND 35

CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

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

SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@example.com');

-- PostgreSQL 11+
CREATE INDEX idx_users_country_email ON users(country) INCLUDE (email, name);

SELECT email, name FROM users WHERE country = 'KR';

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE country = 'KR' AND age > 25;

Index Scan using idx_users_country_age on users
  (cost=0.43..2547.39 rows=10000 width=120)
  (actual time=0.025..15.234 rows=9847 loops=1)
  Index Cond: ((country)::text = 'KR'::text)
  Filter: (age > 25)
  Rows Removed by Filter: 153
  Buffers: shared hit=1234 read=56
Planning Time: 0.123 ms
Execution Time: 15.567 ms

-- 悪い
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- 良い
CREATE INDEX idx_email_lower ON users(LOWER(email));

-- 悪い: varchar列に整数
SELECT * FROM users WHERE phone = 12345678;
-- 良い
SELECT * FROM users WHERE phone = '12345678';

-- 悪い
SELECT * FROM users WHERE name LIKE '%alice%';
-- 良い
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);

CREATE INDEX idx_active ON users(id) WHERE deleted_at IS NULL;

ANALYZE users;

CREATE INDEX idx_users_country_age ON users(country, age);
-- 悪い: country欠落
SELECT * FROM users WHERE age > 25;

-- 一部DBで悪い
SELECT * FROM orders WHERE order_id = 12345;
-- 良い
SELECT * FROM orders WHERE order_id = 12345::bigint;

SELECT * FROM products
WHERE category = 'electronics'
  AND price BETWEEN 100 AND 500
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX idx_products_search ON products(category, price, created_at DESC);

SELECT u.*, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
GROUP BY u.id;

CREATE INDEX idx_orders_user_id ON orders(user_id);

CREATE INDEX idx_events_metadata ON events USING gin(metadata);

SELECT
  schemaname || '.' || relname AS table,
  indexrelname AS unused_index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan AS index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

REINDEX INDEX CONCURRENTLY idx_users_email;

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;