DB 인덱스 마스터 가이드 2025: B-Tree, Hash, GIN, BRIN, 복합 인덱스, 실행 계획 분석

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

1억 행 × 100 nanosecond = 10초

1억 행 → log₂(10⁸) ≈ 27회 비교
27회 × 100 ns = 2.7 microsecond

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

PostgreSQL B-Tree 페이지 (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, ...]

-- 'redis' 태그가 있는 모든 포스트
SELECT * FROM posts WHERE tags @> ARRAY['redis'];

-- 'redis'와 'database' 모두 있는 포스트
SELECT * FROM posts WHERE tags @> ARRAY['redis', 'database'];

-- JSONB
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';

-- 지리 정보 (PostGIS)
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가 leading)
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

WHERE country IN ('KR', 'JP') 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);
-- 또는
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

CREATE UNIQUE INDEX idx_user_device ON sessions(user_id, device_id);

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

rows=10000 (estimated)
actual rows=9847

rows=100 (estimated)
actual rows=100000  -- 1000배 차이!

-- ❌ 인덱스 안 씀
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';

-- ❌ B-Tree 못 씀
SELECT * FROM users WHERE name LIKE '%alice%';

-- ✅ pg_trgm + GIN 인덱스
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);

-- ❌ 일부 DB에서 안 씀
SELECT * FROM users WHERE deleted_at IS NULL;

-- ✅ 부분 인덱스
CREATE INDEX idx_active ON users(id) WHERE deleted_at IS NULL;

-- ❌ 비효율적일 수 있음
SELECT * FROM users WHERE country = 'KR' OR country = 'JP';

-- ✅ IN 사용
SELECT * FROM users WHERE country IN ('KR', 'JP');

-- 또는 UNION
SELECT * FROM users WHERE country = 'KR'
UNION
SELECT * FROM users WHERE country = 'JP';

ANALYZE users;
-- 또는 자동
ALTER TABLE users SET (autovacuum_analyze_scale_factor = 0.05);

SELECT * FROM users USE INDEX (idx_email) WHERE email = '...';

CREATE INDEX idx_users_country_age ON users(country, age);

-- ❌ country 빠짐
SELECT * FROM users WHERE age > 25;

-- BIGINT 컬럼에 INT 비교 — 일부 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);

SELECT * FROM events
WHERE metadata @> '{"event_type": "login"}';

CREATE INDEX idx_events_metadata ON events USING gin(metadata);

CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_b ON t(b);
CREATE INDEX idx_c ON t(c);

CREATE INDEX idx_abc ON t(a, b, c);

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;

-- 중복 발견
CREATE INDEX idx_a ON t(a);
CREATE INDEX idx_ab ON t(a, b);  -- idx_a를 포함 (불필요)

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