PostgreSQL EXPLAIN ANALYZE — 슬로우 쿼리 읽는 법

슬로우 쿼리 로그에 2초짜리 쿼리가 잡혔다. `EXPLAIN ANALYZE`를 돌렸는데 숫자 더미가 화면을 가득 채운다. 이 출력을 읽는 법을 모르면 인덱스를 걸어도 왜 빨라졌는지 모른 채 넘어간다. 쿼리 플랜을 제대로 읽는 것은 PostgreSQL 성능 튜닝의 시작점이다.

## 1. EXPLAIN vs EXPLAIN ANALYZE

```sql
-- 계획만 보기 (실제 실행 X)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- 실제 실행 + 실측 데이터 포함
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- 버퍼 캐시 정보까지
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;
```

`EXPLAIN`만 쓰면 플래너의 **예상값(estimate)**만 나온다. `ANALYZE`를 붙여야 실제로 쿼리가 실행되고 **실측값(actual)**이 함께 출력된다. 프로덕션에서 `EXPLAIN ANALYZE`는 실제로 DML을 수행하므로 `BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;` 패턴을 쓰는 것이 안전하다.

## 2. 출력 구조 읽기

```
Seq Scan on orders  (cost=0.00..4280.00 rows=1000 width=64)
                     (actual time=0.034..42.1 rows=987 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 98013
Planning Time: 0.4 ms
Execution Time: 42.5 ms
```

각 필드의 의미:

| 필드 | 설명 |
|------|------|
| `cost=시작..종료` | 플래너 예상 비용 (임의 단위, 첫 행 반환~전체 반환) |
| `rows=N` | 플래너 예상 행 수 |
| `width=N` | 예상 평균 행 크기(bytes) |
| `actual time=시작..종료` | 실측 시간(ms) |
| `actual rows=N` | 실제 반환 행 수 |
| `loops=N` | 해당 노드 반복 횟수 (중첩 루프에서 중요) |

**예상 rows vs 실제 rows의 乖離**가 클수록 통계(statistics)가 오래됐거나 선택도 추정이 잘못된 것. `ANALYZE` 명령으로 통계를 갱신하거나 `default_statistics_target`을 높여야 한다.

## 3. 스캔 방식 비교

### Seq Scan (순차 스캔)
테이블 전체를 처음부터 끝까지 읽는다. 조건에 맞는 행이 전체의 10~20% 이상이거나, 테이블이 작아서 인덱스 오버헤드가 더 클 때 플래너가 선택한다.

```sql
Seq Scan on products  (cost=0.00..1200.00 rows=50000 width=48)
```

### Index Scan
인덱스 B-트리를 탐색해 행 위치(TID)를 찾고 힙 페이지를 랜덤 접근한다. 선택도가 낮을 때(행이 적을 때) 유리.

```sql
Index Scan using idx_orders_user_id on orders
  (cost=0.43..8.45 rows=1 width=64)
  Index Cond: (user_id = 42)
```

### Bitmap Index Scan + Bitmap Heap Scan
인덱스에서 TID 비트맵을 먼저 만들고, 힙 페이지를 정렬된 순서로 읽는다. 중간 선택도(수백~수천 행)에서 랜덤 I/O를 줄여준다.

```sql
Bitmap Heap Scan on orders
  (cost=12.3..156.7 rows=40 width=64)
  Recheck Cond: (status = 'pending')
  ->  Bitmap Index Scan on idx_orders_status
        (cost=0.00..12.2 rows=40 width=0)
        Index Cond: (status = 'pending')
```

## 4. 실제 슬로우 쿼리 분석 예시

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.*, u.email
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '7 days'
  AND o.status = 'pending';
```

출력:
```
Hash Join  (cost=540..12800 rows=1200 width=128)
           (actual time=8.2..245.0 rows=1187 loops=1)
  Hash Cond: (o.user_id = u.user_id)
  Buffers: shared hit=4200 read=1800
  ->  Seq Scan on orders o
        (cost=0..9800 rows=1200 width=80)
        (actual time=0.1..230.0 rows=1187 loops=1)
        Filter: (status = 'pending' AND created_at > ...)
        Rows Removed by Filter: 98813
        Buffers: shared hit=2100 read=1800
  ->  Hash  (cost=320..320 rows=17600 width=48)
        (actual time=7.8..7.8 rows=17600 loops=1)
        ->  Seq Scan on users u  ...
```

**분석**:
- `orders` 테이블에서 Seq Scan으로 10만 건을 읽어 1,187건만 통과 → `(created_at, status)` 복합 인덱스 필요
- `Buffers: shared read=1800` → 캐시 미스, 디스크 I/O 발생
- `Hash Join`은 적절함, users 테이블 크기 대비 합리적

**개선 후**:
```sql
CREATE INDEX idx_orders_created_status ON orders(created_at, status)
WHERE status = 'pending';  -- partial index로 크기 절약
```

## 5. 핵심 지표 체크리스트

| 확인 항목 | 문제 신호 | 해결 방향 |
|-----------|-----------|-----------|
| 예상 rows vs 실제 rows | 10배 이상 차이 | `ANALYZE`, 통계 목표값 상향 |
| `Seq Scan` on 대용량 테이블 | rows removed >> rows returned | 인덱스 추가 |
| `loops` 횟수가 큰 중첩 루프 | Nested Loop + 내부 Seq Scan | 조인 컬럼 인덱스 |
| `Buffers: read` 높음 | 캐시 미스 | `shared_buffers` 증가, 쿼리 최적화 |
| `actual time` >> `cost` | 플래너 오추정 | 통계 갱신, 힌트(pg_hint_plan) |

> 💡 **핵심**: `EXPLAIN ANALYZE`를 볼 때 가장 먼저 **총 Execution Time**과 **각 노드의 actual rows vs estimated rows**를 비교하라. 이 두 가지만 제대로 읽어도 슬로우 쿼리의 원인 80%는 잡힌다.

## 6. 유용한 도구

- **`pgBadger`**: 슬로우 쿼리 로그 시각화
- **`auto_explain`**: 설정한 threshold 이상 쿼리를 자동으로 EXPLAIN ANALYZE 출력
- **`explain.dalibo.com`**: EXPLAIN 출력을 붙여넣으면 시각화 트리로 변환

PostgreSQL EXPLAIN ANALYZE — 슬로우 쿼리 읽는 법

// COMMENTS

ON THIS PAGE