EXPLAIN ANALYZE 와 pg_stat_statements 비교 분석

이쯤에서 이런 생각이 들 수도 있어. "왜 분석 도구가 두 개나 필요하지?" 실제로 EXPLAIN ANALYZE랑 pg_stat_statements 중에 뭘 더 많이 쓸까? 이 두 가지 접근법의 장단점, 그리고 각각 언제 어디서 쓰는지 같이 파헤쳐보자.

각 도구가 해결하는 문제

EXPLAIN ANALYZE: 특정 쿼리 하나를 깊게 파고드는 분석 도구야. PostgreSQL이 쿼리를 어떻게 실행하는지, 어떤 노드를 쓰는지, 몇 줄을 처리하는지, 각 연산에 시간이 얼마나 걸리는지 궁금하다면 이걸 써. "왜 내 쿼리 하나가 이렇게 느리지?"라는 질문에 답해주는 도구지.

pg_stat_statements: 좀 더 전체적인 관점에서 데이터베이스에 실행된 모든 쿼리의 성능 정보를 보여주는 모니터링 도구야. "내 DB에서 제일 느린 쿼리는 뭐지?" 혹은 "서버에 제일 부담 주는 쿼리는 뭐지?" 이런 전체적인 그림을 보고 싶을 때 이걸 써.

EXPLAIN ANALYZE를 언제 써야 할까?

EXPLAIN ANALYZE는 디버깅 도구로, PostgreSQL이 특정 쿼리를 어떻게 실행하는지 파악할 때 써. 이런 상황에서 유용해:

쿼리 하나만 딱 최적화하고 싶을 때 앱 페이지가 너무 느리게 뜬다는 불만이 들어오면, 제일 먼저 문제의 쿼리를 찾아서 EXPLAIN ANALYZE를 돌려봐. 그러면 쿼리 실행 계획과 실제 실행 시간, 처리된 줄 수 같은 메트릭을 바로 볼 수 있어.

인덱스가 잘 적용되는지 확인할 때 새 인덱스를 만들거나 기존 인덱스를 바꿨다면, EXPLAIN ANALYZE로 PostgreSQL이 그 인덱스를 실제로 쓰는지 확인해봐. 만약 안 쓴다면, 인덱스가 쿼리 최적화에 도움이 안 되는 걸 수도 있어.

복잡한 쿼리 디버깅할 때 JOIN이나 WHERE가 많은 복잡한 쿼리를 짤 때, EXPLAIN ANALYZE로 실제 실행 계획을 보면, 예를 들어 불필요한 전체 테이블 스캔(안녕, Seq Scan!) 같은 병목을 찾을 수 있어.

예시: EXPLAIN ANALYZE로 쿼리 최적화하기

-- 느리게 동작하는 쿼리
SELECT *
FROM students
WHERE name = 'Alice';

-- 실행 계획 분석
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Seq Scan이 뜬다면, 인덱스를 안 만든 걸 수도 있어:

-- name 컬럼에 인덱스 만들기
CREATE INDEX idx_students_name ON students(name);

-- 다시 확인
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

pg_stat_statements를 언제 써야 할까?

이 도구는 시스템 전체 성능을 분석할 때 필수야. 이런 상황에서 써봐:

운영 환경 모니터링 pg_stat_statements는 일정 기간 동안 쿼리 실행 통계를 보여줘. total_time 컬럼 덕분에 어떤 쿼리가 제일 오래 걸렸는지 쉽게 찾을 수 있어.

"무거운" 쿼리 찾기 DB에 부담을 주는 쿼리가 뭔지 알고 싶으면, 메모리에서 읽은 횟수(shared_blks_hit)나 처리된 줄 수(rows)로 정렬해봐.

실행 빈도가 높은 쿼리 찾기 꼭 오래 걸리는 쿼리만 문제는 아니야. 자주 실행되는 쿼리도 문제를 일으킬 수 있어. 예를 들어, 어떤 쿼리가 1분에 100번 실행된다면, 조금만 최적화해도 서버 부담이 확 줄어들 수 있지.

예시: pg_stat_statements로 느린 쿼리 찾기

-- 쿼리 통계 보기
SELECT query,
       calls,
       total_time,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

이 쿼리는 시간 제일 많이 잡아먹는 상위 5개 쿼리를 보여줘.

비교: 뭐가 다를까?

두 도구를 같이 쓰자

프로그래밍에서 만능 버튼은 없어. 제일 좋은 방법은 두 도구를 같이 쓰는 거야. 예를 들어:

1. pg_stat_statements로 시스템에서 제일 느리거나 자주 실행되는 쿼리를 찾는다.

2. 그 다음 EXPLAIN ANALYZE로 해당 쿼리를 분석해서 왜 느린지 원인을 파악한다.

실전 예시: 종합적 접근

-- 1단계: 제일 느린 쿼리 찾기
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

-- 2단계: 해당 쿼리 분석
EXPLAIN ANALYZE
<이전 단계에서 복사한 쿼리>;

자주 하는 실수

EXPLAIN ANALYZE랑 pg_stat_statements 쓸 때 초보자들이 자주 하는 실수 몇 가지가 있어:

1. 데이터 최신성 무시하기. 빈 테이블에서 쿼리 분석하면 EXPLAIN ANALYZE 결과가 헷갈릴 수 있어. 테스트 DB가 실제 데이터 양을 반영하는지 꼭 확인하자.

2. 모니터링 리소스 무시하기. 운영 서버에서 pg_stat_statements 확장 켜뒀다면, 설정이 최적화되어 있는지, 과도한 부하를 주지 않는지 체크하자.

3. 이론적 실행 계획만 보기. EXPLAIN만 쓰면 이론상 실행 계획만 보여줘. 실제 데이터를 보려면 EXPLAIN ANALYZE를 꼭 써야 해.

이제 느린 쿼리랑 싸우는 것뿐만 아니라, 아예 그런 쿼리가 생기지 않게 미리 막을 수 있는 모든 지식을 갖췄어. PostgreSQL은 강력한 도구를 제공하고, 이걸 잘 조합하면 부하가 큰 시스템에서도 최적의 성능을 낼 수 있어.

2

과제

SQL SELF, 레벨 42, 레슨 4

잠금

`pg_stat_statements`를 이용한 가장 "무거운" 쿼리 찾기

`pg_stat_statements`를 이용한 가장 "무거운" 쿼리 찾기

열림

1

설문조사/퀴즈

쿼리 최적화, 레벨 42, 레슨 4

사용 불가능

쿼리 최적화

쿼리 최적화

열림