sql-optimization-patterns

SQL Optimization Patterns

Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query execution plan analysis.

When to Apply

• Debugging slow-running queries
• Designing high-performance database schemas
• Optimizing application response times
• Reducing database load and costs
• Improving scalability as data grows
• Analyzing EXPLAIN query execution plans
• Implementing efficient indexes
• Resolving N+1 query problems

Core Concepts

1. Query Execution Plans (EXPLAIN)

Understanding EXPLAIN output is the foundation of optimization.

PostgreSQL EXPLAIN:

-- 기본 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 실제 실행 통계 포함
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';

-- 더 많은 세부 정보를 포함한 상세 출력
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

Key metrics to watch carefully:

• Seq Scan: Full table scan (usually slow on large tables)
• Index Scan: Index usage (good)
• Index Only Scan: Index-only usage without table access (best)
• Nested Loop: Join method (fine for small datasets)
• Hash Join: Join method (good for large datasets)
• Merge Join: Join method (good for sorted data)
• Cost: Estimated query cost (lower is better)
• Rows: Estimated number of returned rows
• Actual Time: Actual execution time

2. Index Strategies

Indexes are the most powerful optimization tool.

Index types:

• B-Tree: Default, good for equality (=) and range queries
• Hash: Used only for equality (=) comparisons
• GIN: Full-text search, array queries, JSONB
• GiST: Geometric data, full-text search
• BRIN: Block range index for very large tables with correlation between data

-- 표준 B-Tree 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스 (순서가 중요합니다!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 부분 인덱스 (행의 일부만 인덱싱)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- 표현식 인덱스 (함수 기반 인덱스)
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- 커버링 인덱스 (추가 컬럼 포함)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- 전체 텍스트 검색 인덱스
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB 인덱스
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Query Optimization Patterns

Avoid SELECT *:

-- 나쁨: 불필요한 모든 컬럼을 가져옴
SELECT * FROM users WHERE id = 123;

-- 좋음: 필요한 컬럼만 명시
SELECT id, email, name FROM users WHERE id = 123;

Efficient Use of WHERE Clauses:

-- 나쁨: 함수 사용으로 인덱스 활용 불가
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 좋음: 함수 기반 인덱스(functional index) 생성 또는 정확한 일치 사용
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- 그 다음:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 또는 데이터를 정규화하여 저장
SELECT * FROM users WHERE email = 'user@example.com';

JOIN Optimization:

-- 나쁨: 카테시안 곱 생성 후 필터링
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- 좋음: 조인 전 필터링
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- 더 좋음: 두 테이블 모두 사전 필터링
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

Optimization Patterns

Pattern 1: Eliminating N+1 Queries

Problem: N+1 query anti-pattern

# 나쁨: N+1개의 쿼리를 실행함
users = db.query("SELECT * FROM users LIMIT 10")
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
    # orders 처리

Solution: Use JOIN or Batch Loading

-- 해결책 1: 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);

-- 해결책 2: 배치 쿼리
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5);

# 좋음: JOIN 또는 배치 로드를 통한 단일 쿼리 실행
# JOIN 사용 시
results = db.query("""
    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)
""")

# 또는 배치 로드(Batch load)
users = db.query("SELECT * FROM users LIMIT 10")
user_ids = [u.id for u in users]
orders = db.query(
    "SELECT * FROM orders WHERE user_id IN (?)",
    user_ids
)
# user_id별로 orders 그룹화
orders_by_user = {}
for order in orders:
    orders_by_user.setdefault(order.user_id, []).append(order)

Pattern 2: Pagination Optimization

Bad: Using OFFSET on large tables

-- 큰 offset 값에서 속도 저하 발생
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;  -- 매우 느림!

Good: Cursor-Based Pagination

-- 훨씬 빠름: 커서(마지막 확인된 ID) 사용
SELECT * FROM users
WHERE created_at < '2024-01-15 10:30:00'  -- 마지막 커서
ORDER BY created_at DESC
LIMIT 20;

-- 복합 정렬 시
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 인덱스 필요
CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);

Pattern 3: Aggregate Efficiently

COUNT query optimization:

-- 나쁨: 모든 행을 카운트함
SELECT COUNT(*) FROM orders;  -- 큰 테이블에서 느림

-- 좋음: 근사치를 위한 추정치(estimates) 사용
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';

-- 좋음: 카운트 전 필터링 적용
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

-- 더 좋음: 인덱스 전용 스캔(index-only scan) 활용
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

GROUP BY optimization:

-- 나쁨: 그룹화 후 필터링
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10;

-- 좋음: 가능한 경우 먼저 필터링 후 그룹화
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) > 10;

-- 가장 좋음: 커버링 인덱스 활용
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Pattern 4: Subquery Optimization

Transforming Correlated Subqueries:

-- 나쁨: 상관 서브쿼리 (각 행마다 실행됨)
SELECT u.name, u.email,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

-- 좋음: 집계가 포함된 JOIN
SELECT u.name, u.email, 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, u.email;

-- 더 좋음: 윈도우 함수 사용
SELECT DISTINCT ON (u.id)
    u.name, u.email,
    COUNT(o.id) OVER (PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Using CTEs for readability:

-- 공통 테이블 식별자(CTE) 활용
WITH recent_users AS (
    SELECT id, name, email
    FROM users
    WHERE created_at > NOW() - INTERVAL '30 days'
),
user_order_counts AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
FROM recent_users ru
LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;

Pattern 5: Batch Operations

Batch INSERT:

-- 나쁨: 다수의 개별 insert 수행
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Carol', 'carol@example.com');

-- 좋음: 배치 insert
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Carol', 'carol@example.com');

-- 더 좋음: 대량 insert 시 COPY 활용 (PostgreSQL)
COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;

Batch UPDATE:

-- 나쁨: 반복문 내 업데이트
UPDATE users SET status = 'active' WHERE id = 1;
UPDATE users SET status = 'active' WHERE id = 2;
-- ... 많은 ID를 반복

-- 좋음: IN 절을 활용한 단일 UPDATE
UPDATE users
SET status = 'active'
WHERE id IN (1, 2, 3, 4, 5, ...);

-- 더 좋음: 대량 배치 시 임시 테이블 활용
CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;

UPDATE users u
SET status = t.new_status
FROM temp_user_updates t
WHERE u.id = t.id;

Advanced Techniques

Materialized Views

Precompute expensive queries.

-- 구체화된 뷰 생성
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
    u.id,
    u.name,
    COUNT(o.id) as total_orders,
    SUM(o.total) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 구체화된 뷰에 인덱스 추가
CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);

-- 구체화된 뷰 갱신
REFRESH MATERIALIZED VIEW user_order_summary;

-- 동시 갱신 (PostgreSQL, 락 최소화)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

-- 구체화된 뷰 쿼리 (매우 빠름)
SELECT * FROM user_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC;

Partitioning

Split large tables for performance.

-- 날짜별 범위 파티셔닝 (PostgreSQL)
CREATE TABLE orders (
    id SERIAL,
    user_id INT,
    total DECIMAL,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- 파티션 생성
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- 쿼리는 자동으로 적절한 파티션을 사용함
SELECT * FROM orders
WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
-- orders_2024_q1 파티션만 스캔함

Query Hints and Optimization

-- 인덱스 사용 강제 (MySQL)
SELECT * FROM users
USE INDEX (idx_users_email)
WHERE email = 'user@example.com';

-- 병렬 쿼리 (PostgreSQL)
SET max_parallel_workers_per_gather = 4;
SELECT * FROM large_table WHERE condition;

-- 조인 힌트 (PostgreSQL)
SET enable_nestloop = OFF;  -- hash join 또는 merge join 강제

Best Practices

1. Selective indexing: Too many indexes slow down writes.
2. Monitor query performance: Use slow query logs.
3. Keep statistics updated: Run ANALYZE regularly.
4. Use appropriate data types: Smaller types perform better.
5. Thoughtful normalization: Balance normalization and performance.
6. Cache frequently accessed data: Use application-level caching.
7. Connection Pooling: Reuse database connections.
8. Regular maintenance: Perform VACUUM, ANALYZE, index rebuilds, etc.

-- 통계 업데이트
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- 공간 회수 (테이블 락 발생)

-- 인덱스 재구성
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Common Pitfalls

• Over-indexing: Each index slows down INSERT/UPDATE/DELETE.
• Unused indexes: Waste space and degrade write performance.
• Missing indexes: Slow queries, causing full table scans.
• Implicit type conversion: Prevents index usage.
• OR conditions: Can make it difficult to use indexes efficiently.
• LIKE with a leading wildcard: LIKE '%abc'cannot use an index.
• Functions in the WHERE clause: Prevents index usage unless there is a function-based index.

Query Monitoring

-- 느린 쿼리 찾기 (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 누락된 인덱스 찾기 (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- 사용되지 않는 인덱스 찾기 (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Resources

• references/postgres-optimization-guide.md: PostgreSQL-specific optimization
• references/mysql-optimization-guide.md: MySQL/MariaDB optimization
• references/query-plan-analysis.md: In-depth analysis of EXPLAIN execution plans
• assets/index-strategy-checklist.md: When and how to create indexes
• assets/query-optimization-checklist.md: Step-by-step optimization guide
• scripts/analyze-slow-queries.sql: Identify slow queries in the database
• scripts/index-recommendations.sql: Generate index recommendations