[DB] EXPLAIN (ANALYZE, BUFFERS)로 보는 쿼리 튜닝

실행 계획을 어떻게 읽고 고칠까?

들어가며

운영에서 느린 쿼리를 마주치면 대부분 이렇게 시작합니다.

“EXPLAIN 한 번 돌려보셨나요?”

그런데 막상 EXPLAIN 결과를 열어 보면, Nested Loop, GroupAggregate, Sort, Buffers: shared hit=... 같은 말들만 가득해서 처음엔 막막합니다.

이번 글에서는 제가 실제로 테스트했던 아래 쿼리를 예제로 삼아서:

EXPLAIN (ANALYZE, BUFFERS)
SELECT dvc_id, AVG(data_val)
FROM h_raw
         JOIN t_dvc_priority_manage USING (dvc_id)
WHERE data_dt >= NOW() - INTERVAL '10 minutes'
GROUP BY dvc_id
HAVING AVG(data_val) > 50;

• EXPLAIN 결과에서 어떤 걸 먼저 봐야 하는지
• 어떤 노드가 병목이 되는지 어떻게 판단하는지
• 인덱스나 쿼리를 어떻게 손보면 좋아지는지

를 하나씩 뜯어보겠습니다.

---

EXPLAIN (ANALYZE, BUFFERS) 기본만 이해하자

EXPLAIN은 “이 쿼리를 이렇게 실행할 생각이다”라는 실행 계획을 보여줍니다.
여기에 옵션을 더 붙이면 더 많은 정보를 볼 수 있습니다.

• ANALYZE: 실제로 쿼리를 실행해서 예상이 아니라 실측 시간/row 수를 보여줌.
• BUFFERS: 이 쿼리가 읽은 페이지(블록)가 메모리(hit) 기준인지, 디스크(read)인지 알려줌.

실무 튜닝할 땐 거의 항상:

EXPLAIN (ANALYZE, BUFFERS)
...

이 조합으로 보는 게 정석입니다.

---

실행 계획에서 눈여겨볼 키 포인트

실제 결과는 환경마다 다르겠지만, 이번 쿼리에서 눈에 띄는 노드들을 정리하면 대략 이런 느낌이었을 겁니다:

• ChunkAppend (TimescaleDB 하이퍼테이블 청크 선택)
• Index Scan 또는 Index Only Scan on t_dvc_priority_manage
• GroupAggregate
• 그 위에 붙은 Sort
• 전체 하단의 Planning Time, Execution Time, Buffers: shared hit=...

이 각각이 뭘 의미하는지, 그리고 튜닝 포인트가 어디인지가 핵심입니다.

---

1. 인덱스 스캔 상태부터 체크하기

Index Scan / Index Only Scan

t_dvc_priority_manage 쪽에서 Index Only Scan이 나왔다면 아주 좋은 상태입니다.

• Index Scan: 인덱스로 위치를 찾은 뒤 테이블 페이지까지 가서 실제 값을 읽음
• Index Only Scan: 필요한 값을 인덱스에 이미 다 가지고 있어서 테이블을 안 타도 됨.

이 쿼리에서 JOIN이 USING (dvc_id) 기준으로 걸려 있으니,
t_dvc_priority_manage에 PRIMARY KEY(dvc_id) 혹은 UNIQUE INDEX(dvc_id)가 잘 잡혀 있고,
통계/visibility map 상태가 좋다면 Index Only Scan까지 떠주는 그림이 나옵니다.

이 단계에서 확인할 수 있는 것:

• JOIN 키에 적절한 인덱스가 있는지
• Filter 조건(WHERE/HAVING)이 인덱스를 잘 타고 있는지

이 쿼리에서는 dvc_id 기준 JOIN, data_dt 기준 시간 필터가 핵심이기 때문에
h_raw(data_dt, dvc_id) or h_raw(dvc_id, data_dt) 같은 인덱스를 어떻게 줄지 고민할 포인트가 됩니다.

---

2. JOIN 방식: Nested Loop이 나쁜 건 아니다

실행 계획에 Nested Loop가 보이면 겁부터 나는 경우가 많은데, 항상 나쁜 건 아닙니다.

• 이 쿼리는 h_raw에서 시간 조건으로 걸러진 결과에 대해
• 각 dvc_id마다 t_dvc_priority_manage를 인덱스로 찔러보는 패턴이라
• 소량 데이터 기준에선 Nested Loop + Index Scan 조합이 가장 빠를 수 있습니다.

다만 데이터가 커졌을 때를 대비해서 봐야 할 지표는:

• Nested Loop 아래쪽(outer)에서 나오는 rows 숫자
• 그 아래쪽에서 쓰이는 인덱스/Seq Scan의 rows / loops / buffers 값들

만약 장기적으로 h_raw에서 시간 조건으로 걸러지는 데이터가 매우 많아진다면,

• Hash Join으로 바뀌도록 유도하거나
• h_raw의 시간 조건 필터링을 더 강하게 만들거나
• hypertable chunk 크기/retention 정책도 함께 고려해볼 수 있습니다.

---

3. GROUP BY + HAVING에서 병목이 생길 수 있는 부분

이 쿼리의 핵심은:

GROUP BY dvc_id
HAVING AVG(data_val) > 50;

실행 계획에서는 보통 이런 구조로 나타납니다:

• GroupAggregate 혹은 HashAggregate
• 그 아래에 Sort (GroupAggregate가 필요로 하는 정렬)

Sort가 붙어 있으면?

GroupAggregate 위에 Sort 노드가 있다면, DB가 이렇게 하고 있다는 뜻입니다:

1. 먼저 입력 데이터를 dvc_id 기준으로 정렬
2. 그 다음 정렬된 상태에서 그룹별로 AVG() 계산

데이터가 적을 때는 이 Sort 비용이 미미하지만,
수백만 건 단위로 가면 Sort가 메모리를 많이 쓰거나 디스크까지 쓰는 병목이 됩니다.

실행 계획에서 체크할 부분:

• Sort Method: quicksort Memory: xxxkB
  → 여기 메모리가 커지거나 disk가 보이면 위험 신호입니다.
• Buffers에서 특정 Sort/Group 노드에 read가 많이 붙는지 확인

---

4. GROUP BY를 더 빠르게 만드는 인덱스 전략

GROUP BY가 느릴 때 쓸 수 있는 대표적인 전략은:

GROUP BY 컬럼 순서에 맞는 인덱스를 만들어 정렬 비용을 줄인다.

예를 들어 이 쿼리는 dvc_id 기준으로 그룹핑을 합니다.

• 만약 h_raw에 (dvc_id, data_dt) 순서의 인덱스가 있다면,
• 시간 조건으로 범위를 줄이면서, 같은 dvc_id끼리 어느 정도 묶인 상태로 읽을 수 있습니다.
• 그러면 Sort가 아예 없어지거나, 더 저렴한 Incremental Sort / 정렬 최소화가 일어날 수 있습니다.

실제 전략 예시:

-- 시간 + dvc_id 기준으로 자주 조회한다면
CREATE INDEX idx_h_raw_dvcdt ON h_raw (dvc_id, data_dt);

-- 혹은 시간 범위가 항상 강하게 걸린다면
CREATE INDEX idx_h_raw_dtdvc ON h_raw (data_dt, dvc_id);

어느 쪽이 더 유리한지는 실제 쿼리 패턴과 카디널리티에 따라 달라집니다.
WHERE data_dt ... GROUP BY dvc_id 패턴이 많으면 (data_dt, dvc_id)도 좋은 선택입니다.

---

5. BUFFERS로 I/O 병목 찾는 법

BUFFERS 옵션은 “이 쿼리가 어디서 얼마나 IO를 쓰고 있는지”를 보여줍니다.
예를 들어 이런 식으로 나올 수 있습니다:

Buffers: shared hit=29
...
Buffers: shared hit=20 read=5

• shared hit: 이미 메모리에 있던 페이지를 읽은 경우 → 상대적으로 가벼움.
• read: 디스크에서 읽어 온 경우 → IO 비용이 더 큼.

튜닝할 때는:

• 어느 노드에서 read가 많이 뜨는지
• 특정 Sort/Hash 노드에서 Buffers 사용량이 비정상적으로 크지 않은지

를 중심으로 보면, “이 쿼리가 어느 단계에서 디스크를 많이 긁고 있는지” 감이 잡힙니다.

---

6. Planning Time vs Execution Time

실행 계획 맨 아래를 보면 보통 이런 정보가 딸려옵니다.

Planning Time: 1.332 ms
Execution Time: 0.273 ms

• Planning Time: 옵티마이저가 계획 세우는 데 걸린 시간.
• Execution Time: 실제 쿼리를 실행한 시간.

보통은 Execution이 압도적으로 중요하지만,
하이퍼테이블 청크가 아주 많거나, 복잡한 JOIN/서브쿼리가 많으면 Planning Time이 수백 ms~수 초까지 튈 수 있습니다.

TimescaleDB 환경에서:

• 청크 개수가 너무 많고 필터링 조건(특히 시간 조건)이 약하면
• Planner가 “어떤 청크를 볼지” 판단하는 데 시간이 많이 들 수 있습니다.

---

이 쿼리로부터 뽑아낼 수 있는 튜닝 아이디어들

1. 시간 조건 인덱스 최적화

   • WHERE data_dt >= NOW() - INTERVAL '10 minutes'
   • → h_raw(data_dt) 또는 (data_dt, dvc_id) 인덱스로 시간 범위 스캔을 싸게 만들기.

2. GROUP BY dvc_id 최적화

   • Sort + GroupAggregate 조합이 보인다면
   • → (dvc_id, data_dt) 인덱스로 정렬 비용을 줄이거나 Incremental Sort 유도.

3. JOIN 키 인덱스 확인

   • JOIN t_dvc_priority_manage USING (dvc_id)
   • → t_dvc_priority_manage 쪽의 PRIMARY KEY (dvc_id) 또는 적절한 인덱스 유무 확인.

4. 데이터가 쌓였을 때를 위한 체크 포인트

   • Execution Time이 100ms → 500ms → 1s 넘어갈 때마다
   • 다시 EXPLAIN (ANALYZE, BUFFERS)를 찍어서:
     • Sort / Hash의 메모리/디스크 상태
     • Buffers read 폭증 구간
     • Nested Loop 아래 outer rows 증가 여부
       를 반드시 한 번씩 체크.

---

마무리

EXPLAIN은 처음 보면 복잡하지만, 패턴을 몇 개만 잡고 보면 훨씬 쉬워집니다.
이번 글에서 다룬 쿼리 기준으로 요약하면:

• 인덱스 스캔이 잘 되는지 → Scan 노드와 rows 확인
• JOIN이 언제부터 느려질지 → Nested Loop/Hash Join 아래 rows·loops·buffers 확인
• GROUP BY 병목인지 → Sort + GroupAggregate / HashAggregate를 주시
• IO 병목인지 → BUFFERS의 read 폭을 보고 의심 구간 찾기

같은 테이블에 대해서 다양한 튜닝 쿼리들의 실행계획을 통해 EXPLAIN 결과가 어떻게 달라지는지도 비교해보면 더 재미있게 파볼 수 있습니다.