Sql Optimization Patterns

CRITICAL Rules

1. ALWAYS EXPLAIN ANALYZE before optimizing -- 추측 금지, 실측 기반
2. NEVER SELECT * in production -- 필요한 컬럼만 명시
3. ALWAYS index columns in WHERE, JOIN, ORDER BY -- 풀 테이블 스캔 방지
4. NEVER function on indexed column in WHERE -- WHERE LOWER(email) = x 는 인덱스 무시 (functional index 필요)
5. ALWAYS ANALYZE after bulk data changes -- 통계 갱신으로 옵티마이저 판단 정상화
6. NEVER OFFSET for deep pagination -- 10만 행 이후 극심한 성능 저하
7. PREFER batch operations -- 루프 내 개별 INSERT/UPDATE 금지
8. ALWAYS covering index for hot queries -- Index Only Scan 유도
9. NEVER over-index -- 인덱스마다 INSERT/UPDATE/DELETE 비용 증가
10. ALWAYS monitor slow query log -- 문제는 코드가 아닌 운영에서 발견됨

Optimization Workflow

Slow query reported
+-- 1. EXPLAIN ANALYZE 실행
|    +-- Seq Scan on large table? --> 인덱스 필요 (Index Decision Tree)
|    +-- Nested Loop on large sets? --> JOIN 전략 변경 또는 인덱스 추가
|    +-- High cost but low rows? --> 통계 오래됨, ANALYZE 실행
|    +-- Sort with high cost? --> ORDER BY 컬럼에 인덱스 추가
+-- 2. 쿼리 자체 최적화
|    +-- SELECT * ? --> 필요한 컬럼만
|    +-- Correlated subquery? --> JOIN으로 변환
|    +-- N+1 pattern? --> JOIN 또는 IN clause batch
+-- 3. 인덱스 추가/조정
+-- 4. EXPLAIN ANALYZE 재실행으로 개선 확인

Index Type Decision

What query pattern?
+-- Equality (=) + Range (<, >, BETWEEN)
|    --> B-Tree (default, most common)
+-- Equality only, no range
|    --> Hash (PostgreSQL, slightly faster for =)
+-- Full-text search (LIKE '%word%')
|    --> GIN + to_tsvector
+-- JSONB containment (@>, ?)
|    --> GIN
+-- Geometric/spatial data
|    --> GiST
+-- Very large table, data correlates with physical order
|    --> BRIN (10-100x smaller than B-Tree)

Composite Index Rules

Index column order matters!
+-- WHERE a = ? AND b = ?      --> (a, b) or (b, a) - both work
+-- WHERE a = ? AND b > ?      --> (a, b) - equality first, range last
+-- WHERE a = ? ORDER BY b     --> (a, b) - covers both filter and sort
+-- WHERE a = ? AND b = ? AND c > ?  --> (a, b, c) - equalities first

Leftmost prefix rule: INDEX(a, b, c) 는 (a), (a, b), (a, b, c) 쿼리에 사용 가능. (b, c) 단독은 불가.

Index Selection Checklist

Pagination Decision

Need pagination?
+-- Small dataset (<10K rows)?
|    --> Offset-based (simple, "jump to page N" 가능)
+-- Large dataset, infinite scroll?
|    --> Cursor-based (consistent performance)
+-- Search results with page numbers?
|    --> Offset + total count cache
+-- Real-time feed?
|    --> Cursor (keyset) only

Cursor-Based Pattern

-- First page
SELECT id, name, created_at FROM users
ORDER BY created_at DESC, id DESC
LIMIT 21;  -- +1 for has_next detection

-- Next page (after last item)
SELECT id, name, created_at FROM users
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 21;

-- Required index
CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);

Core Optimization Patterns

N+1 Query

-- BAD: N+1 (loop executes N queries)
-- app code: for user in users: query("SELECT * FROM orders WHERE user_id = ?", user.id)

-- GOOD: JOIN
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

-- GOOD: Batch IN clause
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);

Correlated Subquery

-- BAD: Executes subquery per row
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id)
FROM users u;

-- GOOD: JOIN + GROUP BY
SELECT u.name, COUNT(o.id) as order_count
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Aggregate Optimization

-- BAD: COUNT(*) on entire large table
SELECT COUNT(*) FROM orders;

-- GOOD: Approximate count (PostgreSQL)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

-- GOOD: Filtered count with index
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Batch Operations

-- BAD: Individual inserts in loop
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- GOOD: Batch insert
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Carol');

-- BEST: COPY for bulk (PostgreSQL)
COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;

Anti-Patterns

Troubleshooting

EXPLAIN Key Metrics

Maintenance Commands

-- Update statistics (lightweight)
ANALYZE users;

-- Vacuum + analyze (reclaim dead rows)
VACUUM ANALYZE users;

-- Full vacuum (locks table, reclaims disk space)
VACUUM FULL users;  -- use during maintenance window only

-- Find unused indexes (PostgreSQL)
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

-- Find slow queries (PostgreSQL)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;

Cross-References

References

• PostgreSQL EXPLAIN docs -- Official guide
• Use The Index, Luke -- SQL indexing bible
• pganalyze -- PostgreSQL performance monitoring
• Markus Winand: SQL Performance Explained
• MySQL Query Optimization