PostgreSQL 성능 튜닝: EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 10;


-- 출력
HashAggregate  (cost=15234.50..15234.60 rows=10 width=72)
              (actual time=234.567..234.580 rows=10 loops=1)
  ->  Hash Right Join  (cost=2456.78..14567.12 rows=8901 width=64)
                       (actual time=12.345..201.234 rows=8765 loops=1)
        Hash Cond: (p.author_id = u.id)
        ->  Seq Scan on posts p  (cost=0.00..1234.56 rows=43210)
                                 (actual time=0.012..50.123 rows=43210)
                                 ⚠️ Seq Scan = 풀 테이블 스캔
        ->  Hash  (cost=2123.45..2123.45 rows=2345)
            ->  Index Scan using idx_users_created_at on users u
                ✅ 인덱스 사용
Planning Time: 0.234 ms
Execution Time: 234.789 ms

| 지표 | 의미 | 좋음 |
|---|---|---|
| Seq Scan | 풀 테이블 스캔 | 작은 테이블만 |
| Index Scan | 인덱스 사용 | 일반적 ✅ |
| Index Only Scan | 인덱스만으로 답 | 최고 ✅✅ |
| Bitmap Heap Scan | OR 조건 결합 | 보통 |
| Nested Loop | 작은 테이블 join | 보통 |
| Hash Join | 큰 테이블 join | 좋음 |
| Merge Join | 정렬된 테이블 | 좋음 |


actual time vs cost:
- cost = planner 추정 (의미 없음)
- actual time = 실제 시간 (집중)


rows 차이:
- cost rows ≠ actual rows = 통계 오래됨 → ANALYZE 필요

-- 느림 (300ms)
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';


-- 인덱스 생성
CREATE INDEX idx_orders_user_status ON orders(user_id, status);


-- 빠름 (10ms)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';
-- → Index Scan using idx_orders_user_status

ANALYZE orders;  -- 통계 갱신

-- 99%가 status='completed'면, 나머지 1%만 인덱싱
CREATE INDEX idx_orders_pending
  ON orders(user_id)
  WHERE status = 'pending';

-- 인덱스 크기 ↓ + 쿼리 속도 ↑

-- ❌ N+1 (애플리케이션 레벨)
for user in users:
    posts = db.query("SELECT * FROM posts WHERE author_id = ?", user.id)

-- ✅ JOIN 한 번
SELECT u.*, p.*
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.id IN (...);


-- ❌ SELECT *
SELECT * FROM products WHERE id = 1;

-- ✅ 필요한 컬럼만
SELECT id, name, price FROM products WHERE id = 1;


-- ❌ OFFSET 큰 값 (느림)
SELECT * FROM posts ORDER BY id OFFSET 10000 LIMIT 20;

-- ✅ Cursor (id 기반)
SELECT * FROM posts WHERE id > 10000 ORDER BY id LIMIT 20;

-- CTE — 가독성 좋음
WITH active_users AS (
  SELECT * FROM users WHERE last_seen_at > NOW() - INTERVAL '7 days'
)
SELECT au.name, COUNT(p.id)
FROM active_users au
LEFT JOIN posts p ON p.author_id = au.id
GROUP BY au.id, au.name;

-- PostgreSQL 12+ CTE는 자동 inline (성능 동일)
-- 그 이전 버전은 MATERIALIZED 명시 필요

-- pg_stat_statements 익스텐션
CREATE EXTENSION pg_stat_statements;

-- TOP 10 느린 쿼리
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;


-- postgresql.conf
log_min_duration_statement = 1000  -- 1초+ 쿼리 로깅