결론부터 말씀드리면, 어느 쿼리가 느린지 모를 때는 감으로 찾지 말고 pg_stat_statements로 범위를 좁힌 다음 EXPLAIN으로 원인을 확인하는 순서를 지키는 것이 가장 빠릅니다. 로그를 뒤지거나 “이 쿼리가 느릴 것 같다”는 추측으로 튜닝을 시작하면 시간만 쓰고 원인은 못 찾는 경우가 많습니다. 이 글은 PostgreSQL 13 이상 환경을 기준으로, 통계 확장을 켜는 설정부터 EXPLAIN 출력에서 실제로 봐야 할 줄까지 순서대로 정리합니다.
어느 쿼리가 느린지 모를 때 가장 먼저 확인할 것
가장 먼저 확인할 것은 “내가 느리다고 느끼는 쿼리”와 “실제로 DB를 가장 많이 잡아먹는 쿼리”가 다를 수 있다는 점입니다. 사람이 체감하는 느림은 네트워크 지연, 애플리케이션 로직, 커넥션 풀 대기 시간이 섞여 있어서 정확하지 않습니다.
이걸 구분하려면 DB 엔진 쪽에서 실제로 실행 시간을 집계한 통계가 필요합니다. PostgreSQL에는 이 역할을 하는 확장이 기본 번들에 포함되어 있는데, 바로 pg_stat_statements입니다. 이 확장은 실행된 모든 쿼리를 정규화해서 누적 실행 시간, 호출 횟수, 평균 실행 시간을 테이블 형태로 보여줍니다.
pg_stat_statements로 느린 쿼리 순위 뽑기
설정은 postgresql.conf를 수정하고 재시작해야 적용됩니다. shared_preload_libraries에 추가하지 않으면 CREATE EXTENSION만으로는 통계가 쌓이지 않으니 이 순서를 지켜야 합니다.
-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
pg_stat_statements.max = 5000
-- 재시작 후 DB 안에서
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
재시작까지 끝났다면, 아래 쿼리로 누적 실행 시간이 가장 긴 쿼리부터 뽑아볼 수 있습니다. PostgreSQL 13부터 컬럼명이 total_time에서 total_exec_time으로 바뀌었기 때문에, 버전이 12 이하라면 컬럼명을 total_time으로 바꿔야 합니다.
SELECT
query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 1) AS mean_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
여기서 total_exec_time(누적 시간)과 mean_exec_time(평균 시간)을 같이 봐야 범위를 제대로 좁힐 수 있습니다. 누적 시간이 높아도 호출 횟수가 수백만 번이면 쿼리 한 번은 가볍지만 자주 불려서 전체 부담이 큰 경우고, mean_exec_time이 높으면 호출 한 번이 그 자체로 무거운 경우입니다. 둘 중 어느 쪽이냐에 따라 튜닝 방향이 완전히 달라집니다.
EXPLAIN (ANALYZE, BUFFERS) 출력, 어디부터 읽어야 할까요?
후보 쿼리를 추렸다면 그 쿼리를 EXPLAIN으로 돌려서 PostgreSQL이 세운 실행 계획을 확인합니다. ANALYZE 옵션을 넣으면 실제로 쿼리를 실행하면서 각 단계가 걸린 시간을 보여주고, BUFFERS 옵션을 넣으면 디스크/캐시 I/O 횟수까지 같이 나옵니다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 1024
AND status = 'paid';
출력 예시는 대략 이런 모양입니다 (실제 수치는 데이터량에 따라 다릅니다).
Seq Scan on orders (cost=0.00..18562.00 rows=12 width=96)
(actual time=0.021..142.887 rows=9 loops=1)
Filter: ((customer_id = 1024) AND (status = 'paid'::text))
Rows Removed by Filter: 499991
Buffers: shared hit=1021 read=7540
Planning Time: 0.112 ms
Execution Time: 143.021 ms
읽는 순서는 보통 아래쪽에서 위쪽입니다. Execution Time으로 전체 소요 시간을 확인하고, cost 추정치와 actual time의 차이를 비교해서 플래너가 예측을 얼마나 틀렸는지 봅니다. 이 전체 그림이 바로 PostgreSQL이 세운 실행 계획이고, query plan이라는 개념 자체는 대부분의 관계형 DB에 공통으로 존재합니다.
Seq Scan과 Rows Removed by Filter가 말해주는 것
위 예시에서 눈여겨볼 두 줄은 Seq Scan과 Rows Removed by Filter입니다. Seq Scan은 인덱스를 쓰지 않고 테이블 전체를 순차 스캔했다는 뜻이고, Rows Removed by Filter는 499,991행을 읽은 뒤에야 조건에 맞는 9행을 걸러냈다는 뜻입니다.
| 표시 | 의미 | 확인할 것 |
|---|---|---|
| Seq Scan | 인덱스 미사용, 전체 테이블 읽음 | customer_id, status에 인덱스가 있는지 |
| Index Scan | 인덱스로 바로 찾아감 | cost와 actual time 격차가 작은지 |
| Rows Removed by Filter | 조건에 안 맞아 버려진 행 수 | 이 값이 크면 인덱스 조건 재검토 |
| Buffers read | 디스크에서 직접 읽은 블록 수 | 값이 크면 캐시에 못 들어간 상태 |
이 네 가지만 짚어도 “어디가 느린지”는 거의 확인이 됩니다. 위 사례라면 (customer_id, status) 복합 인덱스를 하나 추가하는 것으로 Seq Scan을 Index Scan으로 바꿀 수 있는 전형적인 케이스입니다.
이 방법이 안 통하는 경우와 주의할 점
이 순서가 항상 통하는 것은 아닙니다. 몇 가지 조건에서는 다르게 접근해야 합니다.
- pg_stat_statements는 서버 재시작이나 pg_stat_statements_reset() 호출 시 통계가 초기화되어서, 간헐적으로 튀는 쿼리는 짧은 관찰 구간에는 안 잡힐 수 있습니다.
- 파라미터 값이 다른 쿼리는 정규화돼서 하나의 행으로 합쳐지기 때문에, 특정 파라미터 값에서만 느린 경우는 EXPLAIN에 그 값을 직접 넣어서 따로 재현해봐야 합니다.
- EXPLAIN ANALYZE는 쿼리를 실제로 실행합니다. INSERT/UPDATE/DELETE가 포함된 쿼리를 운영 DB에서 그대로 돌리면 데이터가 바뀌므로, 트랜잭션으로 감싸고 ROLLBACK하거나 복제 환경에서 테스트해야 합니다.
- auto_explain 확장을 같이 쓰면 느린 쿼리의 실행 계획을 로그에 자동으로 남길 수 있지만, 이건 이 글의 범위를 넘어서는 별도 설정입니다.
pg_stat_statements를 켰는데 통계가 안 쌓이는 이유
가장 흔한 원인은 shared_preload_libraries에 추가한 뒤 재시작을 안 한 경우입니다. CREATE EXTENSION은 확장을 데이터베이스에 등록만 할 뿐, 실제 통계 수집 기능은 서버 시작 시점에 로드되는 공유 라이브러리가 담당하기 때문에 reload로는 적용되지 않고 반드시 재시작이 필요합니다.
그다음으로 많은 원인은 track 설정이 none으로 되어 있거나, pg_stat_statements.max를 너무 작게 잡아서 오래된 쿼리 통계가 밀려나는 경우입니다. 설정값은 postgresql.org에서 관리하는 pg_stat_statements 문서에 옵션별 기본값과 설명이 정리되어 있으니, 버전별로 다른 기본값은 거기서 다시 확인하는 것이 가장 정확합니다.
정리하면, 어느 쿼리가 느린지 모르는 상태에서는 pg_stat_statements로 후보를 3~5개로 좁히고, 그 쿼리만 EXPLAIN (ANALYZE, BUFFERS)로 확인하는 두 단계면 충분합니다. 지금 바로 할 수 있는 다음 단계는 postgresql.conf에 shared_preload_libraries를 추가하고 재시작한 뒤, total_exec_time 기준 상위 5개 쿼리부터 EXPLAIN을 돌려보는 것입니다.
