상위 비용 SQL을 구간별로 비교해 튜닝하기

느린 쿼리를 잡아 달라는 요청을 받으면 가장 먼저 열어 보는 뷰가 pg_stat_statements다. 슬로우 쿼리 로그는 임계값을 넘긴 개별 실행만 남기지만, 현장에서 서버를 실제로 무겁게 만드는 건 한 번에 느린 쿼리보다 적당히 느린데 수천 번 호출되는 쿼리인 경우가 많다. 그래서 개별 실행이 아니라 같은 모양의 쿼리를 하나로 묶어 총비용으로 줄 세우는 관점이 필요하다. 예전에 Top N만 뽑아 보던 글을 썼는데, 이번에는 정규화 집계와 구간 비교에 초점을 맞춘다.

요약: pg_stat_statements는 상수만 다른 쿼리를 $1, $2로 정규화해 하나로 집계한다. total_exec_time으로 정렬하면 서버 전체 부하를 가장 많이 유발하는 SQL이 위로 올라오고, calls와 mean_exec_time을 같이 보면 그게 느린 쿼리인지 자주 불리는 쿼리인지 구분된다. 튜닝 전에 pg_stat_statements_reset()으로 누적치를 비운 뒤 같은 부하를 다시 돌리면 전후 구간을 깨끗하게 비교할 수 있다.

1. pg_stat_statements가 하는 일

pg_stat_statements는 서버가 실행한 쿼리의 실행 통계를 쿼리 단위로 모아 두는 익스텐션이다. 핵심은 '정규화'다. 상수 값이 다른 쿼리를 같은 구조로 보고 하나의 행으로 합친다. 예를 들어 아래 두 쿼리는 하나의 항목으로 집계된다.

SELECT * FROM posts WHERE author_id = 101;
SELECT * FROM posts WHERE author_id = 102;

-- 뷰에는 이렇게 하나로 표시된다
SELECT * FROM posts WHERE author_id = $1

이 정규화된 쿼리마다 queryid라는 해시 식별자가 붙는다. queryid는 같은 아키텍처의 마이너 버전 사이에서는 안정적이라, 튜닝 전후에 같은 쿼리를 추적하는 기준으로 쓸 수 있다.

자주 보는 지표는 다음과 같다. calls는 호출 횟수, total_exec_time은 누적 실행 시간(밀리초), mean_exec_time은 평균 실행 시간, rows는 반환하거나 변경한 총 행 수, stddev_exec_time은 실행 시간의 표준편차다. 캐시 효율은 shared_blks_hit(버퍼 캐시 적중)와 shared_blks_read(디스크에서 읽은 블록)로 본다.

참고로 컬럼 이름이 total_exec_time, mean_exec_time인 것은 PostgreSQL 13부터다. 그 이전 버전은 total_time, mean_time이었다. RDS에서 구버전을 쓰고 있다면 쿼리에서 컬럼명을 맞춰야 한다.

2. RDS에서 익스텐션 켜기

RDS for PostgreSQL은 버전 10 이상에서 pg_stat_statements가 이미 기본 shared_preload_libraries에 들어 있다. 자체 설치형 PostgreSQL처럼 라이브러리를 추가하고 재시작하는 단계가 대부분 필요 없다. 먼저 로드 여부부터 확인한다.

postgres=> SHOW shared_preload_libraries;
 shared_preload_libraries
--------------------------
 pg_stat_statements
(1 row)

목록에 보이면 데이터베이스에서 익스텐션만 만들면 된다. RDS는 superuser 대신 rds_superuser 권한을 가진 postgres 계정으로 작업한다.

postgres=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION

postgres=> SELECT count(*) FROM pg_stat_statements;
 count
-------
   142
(1 row)

여기서 한 번 걸리는 지점이 있다. SHOW shared_preload_libraries에 라이브러리가 없는 커스텀 파라미터 그룹을 쓰는 경우다. 이때는 DB 파라미터 그룹의 shared_preload_libraries에 pg_stat_statements를 넣고 인스턴스를 재부팅해야 적용된다. 이 파라미터는 정적 파라미터라 재부팅 없이는 반영되지 않는다.

집계 범위를 넓히고 싶으면 파라미터 그룹에서 pg_stat_statements.track을 all로 바꾼다. 기본값 top은 최상위 문장만 집계하고, all은 함수나 프로시저 안에서 호출되는 중첩 문장까지 집계한다. 쿼리 텍스트가 길어 뒤가 잘린다면 track_activity_query_size를 1024에서 2048 이상으로 올린다. pg_stat_statements.max는 추적할 정규화 쿼리 수의 상한으로 기본값은 5000이다.

3. 상위 비용 SQL 줄 세우기

가장 먼저 보는 것은 누적 실행 시간이다. total_exec_time으로 내림차순 정렬하면 서버 CPU 시간을 가장 많이 쓴 쿼리가 위로 온다.

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 1)   AS total_ms,
  round(mean_exec_time::numeric, 2)     AS mean_ms,
  rows,
  left(query, 50)                       AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

대표적인 결과는 아래 형태다. 숫자는 워크로드에 따라 전혀 다르게 나오니 모양만 참고한다.

    queryid     | calls  | total_ms  | mean_ms | rows  |              query
----------------+--------+-----------+---------+-------+--------------------------------
 -2847...109821 | 48213  | 912004.3  |   18.92 | 48213 | SELECT * FROM comments WHERE a
  6391...448210 |   312  | 410221.8  | 1315.45 | 61200 | SELECT a.*, u.name FROM article
  1120...772044 | 95120  |  88210.1  |    0.93 | 95120 | SELECT 1 FROM sessions WHERE to

여기서 해석이 갈린다. 첫 행은 평균 19ms로 느리지 않지만 4만 번 넘게 불려 총비용 1위가 됐다. 이런 쿼리는 쿼리 자체보다 호출 빈도, 즉 애플리케이션 쪽 N+1 호출이나 캐시 부재를 의심한다. 두 번째 행은 평균 1초가 넘는 진짜 느린 쿼리라 인덱스나 조인 계획을 손봐야 한다.

목적에 따라 정렬 기준을 바꾼다. 한 방에 느린 쿼리를 찾으려면 mean_exec_time으로 정렬하되, 통계가 신뢰할 만하도록 calls가 일정 횟수 이상인 것만 본다.

SELECT queryid, calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 60) AS query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 10;

전체 부하에서 차지하는 비중이 궁금하면 윈도 함수로 비율을 같이 뽑는다. 상위 몇 개 쿼리가 전체의 몇 퍼센트를 먹는지 보면 튜닝 우선순위가 분명해진다.

SELECT queryid,
       calls,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
       left(query, 50) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

디스크를 많이 때리는 쿼리를 찾을 때는 shared_blks_read로 정렬한다. 캐시 적중률이 낮아 디스크 읽기가 많은 쿼리는 인덱스 설계나 작업 집합 크기를 다시 봐야 한다.

4. reset으로 구간 비교하기

pg_stat_statements의 수치는 마지막 리셋 이후 계속 쌓인 누적치다. 그래서 인스턴스가 오래 떠 있으면 과거 부하와 현재 부하가 섞여 지금 무엇이 문제인지 흐릿해진다. 특정 구간만 깔끔하게 보려면 누적치를 비우고 시작한다.

SELECT pg_stat_statements_reset();

이 함수는 pg_stat_statements_reset(userid, dbid, queryid, minmax_only) 형태이고, 인자를 다 생략하면 모든 통계를 비운다. 리셋 시각은 pg_stat_statements_info 뷰의 stats_reset으로 확인한다.

postgres=> SELECT stats_reset FROM pg_stat_statements_info;
          stats_reset
-------------------------------
 2026-10-22 04:11:52.013+00
(1 row)

튜닝 전후 비교는 이렇게 한다. 먼저 리셋하고 일정 시간 또는 정해진 부하 테스트를 한 구간 돌린 뒤 상위 쿼리를 기록한다. 그다음 인덱스를 추가하거나 쿼리를 고치고, 다시 리셋한 뒤 같은 부하를 돌려 같은 기준으로 상위 쿼리를 뽑는다. 두 결과에서 같은 queryid의 mean_exec_time과 total_exec_time이 얼마나 줄었는지 비교하면 효과가 숫자로 드러난다.

전체를 비우기 부담스러우면 문제 쿼리 하나만 리셋할 수도 있다. queryid를 지정하면 해당 쿼리의 누적치만 0부터 다시 쌓인다.

-- 특정 쿼리만 리셋
SELECT pg_stat_statements_reset(0, 0, 6391448210);

운영 환경에서 전체 리셋을 할 때는 한 가지 주의한다. Performance Insights나 Database Insights, 그 밖의 모니터링 도구가 같은 뷰를 주기적으로 읽어 추세를 그린다면, 리셋이 그 그래프를 끊어 놓을 수 있다. 전체 리셋 대신 queryid 단위 리셋을 쓰거나, 리셋 시점을 모니터링 담당과 맞추는 편이 안전하다.

5. 튜닝 후 확인 요령

상위 쿼리를 하나 골랐으면 EXPLAIN (ANALYZE, BUFFERS)로 실제 계획과 버퍼 사용을 확인한다. pg_stat_statements는 어떤 쿼리가 비싼지 가리키고, EXPLAIN은 왜 비싼지 보여 준다. 두 도구는 역할이 다르니 같이 쓴다.

인덱스를 추가한다면 운영 테이블에서는 CREATE INDEX CONCURRENTLY로 테이블 락을 피한다. 인덱스를 만든 뒤 해당 쿼리의 queryid만 리셋하고 부하를 다시 받아 mean_exec_time과 shared_blks_read가 내려갔는지 확인하면, 한 쿼리 단위로 튜닝 사이클을 짧게 돌릴 수 있다.