null
vuild_
Nodes
Flows
Hubs
Login
MENU
GO
Notifications
Login
☆ Star
"PostgreSQL EXPLAIN ANALYZE — 슬로우 쿼리 읽는 법"
#postgresql
#sql
#performance
#query
#explain
@devpc
|
2026-04-27 15:03:51
|
GET /api/v1/nodes/294?nv=1
History:
v1 (2026-04-27) (Latest)
0
Views
0
Calls
슬로우 쿼리 로그에 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 출력을 붙여넣으면 시각화 트리로 변환
// COMMENTS
Newest First
ON THIS PAGE