total_exec_time과 mean_exec_time 중 무엇으로 정렬해야 하나?

서버 전체 부하를 줄이는 게 목적이면 total_exec_time으로 내림차순 정렬한다. 누적 실행 시간이 큰 쿼리가 자원을 가장 많이 쓰기 때문이다. 반면 개별 실행이 무거운 쿼리를 찾으려면 mean_exec_time으로 정렬하되 calls가 너무 적은 항목은 WHERE calls > 50 같은 조건으로 걸러 노이즈를 줄인다.

pg_stat_statements_reset을 실행하면 무엇이 사라지나?

인자 없이 실행하면 pg_stat_statements 뷰에 쌓인 모든 통계(호출 횟수, 실행 시간 합계 등)가 0으로 초기화된다. 인자를 (userid, dbid, queryid)로 주면 해당 쿼리만 리셋한다. PostgreSQL 17 이상에서는 네 번째 인자 minmax_only를 true로 주면 누적값은 유지하고 min/max 통계만 초기화한다.

stats_since 컬럼이 없다고 나온다.

stats_since 컬럼은 PostgreSQL 17에서 추가됐다. 16 이하에서는 존재하지 않아 column does not exist 오류가 난다. 16 이하에서는 pg_stat_statements_info 뷰의 stats_reset 컬럼으로 마지막 전체 리셋 시각을 확인한다.

RDS에서 확장을 만들었는데 뷰에 아무것도 안 쌓인다.

shared_preload_libraries는 정적 파라미터라 파라미터 그룹만 수정하면 pending-reboot 상태로 남는다. 인스턴스를 재부팅해야 적용된다. 재부팅 후 CREATE EXTENSION pg_stat_statements를 다시 확인하고 트래픽이 들어온 뒤 조회한다.

쿼리 원문이 $1 같은 자리표시자로 보인다.

pg_stat_statements는 상수만 다른 쿼리를 하나로 묶기 위해 리터럴을 $1, $2로 정규화해 저장한다. 정상 동작이다. 실행계획을 볼 때는 queryid로 원문 형태를 확인한 뒤, 자리표시자에 실제 값을 넣어 EXPLAIN (ANALYZE, BUFFERS)로 실행한다.

pg_stat_statements로 느린 쿼리 Top N 뽑기

운영 중인 서비스가 느려질 때 어디서부터 봐야 할지 막막한 경우가 많다. 특정 화면 하나가 아니라 서버 전체가 무겁게 느껴지면 슬로우 쿼리 로그만으로는 그림이 안 잡힌다. 나는 이럴 때 개별 쿼리 로그보다 먼저 pg_stat_statements를 열어 서버 전체에서 시간을 가장 많이 잡아먹는 쿼리부터 확인한다.

요약: pg_stat_statements는 서버가 실행한 쿼리를 정규화해 호출 횟수와 실행 시간을 누적해 둔다. total_exec_time으로 내림차순 정렬하면 누적 부하가 큰 쿼리 Top N이 바로 나오고, mean_exec_time과 calls를 같이 읽으면 '한 방이 느린 쿼리'와 '자주 돌아서 합계가 큰 쿼리'를 구분할 수 있다. 튜닝 전후는 pg_stat_statements_reset()으로 카운터를 0으로 돌린 뒤 같은 기간만큼 다시 쌓아 비교한다.

1. pg_stat_statements가 무엇이고 왜 쓰나

pg_stat_statements는 PostgreSQL 기본 배포에 포함된 확장(contrib)이다. 서버가 실행한 SQL을 상수만 다른 형태끼리 하나로 묶어(정규화해서) 호출 횟수, 총 실행 시간, 평균 실행 시간, 반환 행 수 같은 통계를 누적한다. 값을 바꿔 가며 수천 번 도는 WHERE id = $1 같은 쿼리가 한 줄로 합쳐지기 때문에, 개별 로그로는 안 보이던 '누적 부하'가 드러난다.

슬로우 쿼리 로그가 임계값을 넘긴 개별 실행을 기록하는 사후 방식이라면, 이 확장은 서버가 살아 있는 동안 계속 합계를 쌓아 두는 상시 집계다. 그래서 '지금 서버에서 시간을 제일 많이 쓰는 쿼리가 뭐냐'는 질문에 바로 답할 수 있다.

2. 설치와 활성화

이 확장은 공유 메모리에 통계를 올리기 때문에 shared_preload_libraries에 등록해야 한다. 이 파라미터는 서버 기동 시점에만 읽히므로 값을 바꾸면 재시작이 필요하다. 자체 EC2나 온프레미스라면 postgresql.conf를 고친다.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top      # top(기본), all, none
pg_stat_statements.max = 5000       # 추적할 서로 다른 쿼리 수

고친 뒤 서버를 재시작하고, 통계를 볼 데이터베이스에서 확장을 만든다.

$ psql -d sarc
psql (17.4)
Type "help" for help.

sarc=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION
sarc=# \dx pg_stat_statements
                                     List of installed extensions
        Name        | Version | Schema |                        Description
--------------------+---------+--------+------------------------------------------------------------
 pg_stat_statements | 1.11    | public | track planning and execution statistics of all SQL ...

버전 번호는 서버 상태에 따라 다를 수 있다. 이 글의 예시는 PostgreSQL 17(확장 1.11)을 기준으로 한다. 뒤에서 다루는 stats_since 컬럼과 pg_stat_statements_reset의 minmax_only 인자는 PostgreSQL 17에서 추가됐으므로, 16 이하에서는 동작하지 않는다. 16 이하 사용자는 2절의 3-인자 리셋과 pg_stat_statements_info.stats_reset을 대신 쓴다(6절 참고).

RDS에서 켜기

Amazon RDS와 Aurora PostgreSQL은 postgresql.conf를 직접 못 고치므로 파라미터 그룹으로 설정한다. 기본 파라미터 그룹은 이미 shared_preload_libraries에 pg_stat_statements가 들어 있는 경우가 많다. 커스텀 파라미터 그룹을 쓴다면 이 값을 확인하고, 없으면 추가한 뒤 인스턴스를 재부팅한다.

여기서 한 가지 짚어 둘 함정이 있다. shared_preload_libraries는 정적(static) 파라미터라 파라미터 그룹만 바꾸면 상태가 pending-reboot로 남는다. 재부팅 전까지는 값이 적용되지 않아 확장을 만들어도 뷰에 통계가 안 쌓인다. 파라미터를 바꿨으면 반드시 재부팅 후에 CREATE EXTENSION과 조회를 진행한다.

# 파라미터 적용 상태 확인
$ aws rds describe-db-parameters \
    --db-parameter-group-name sarc-pg17 \
    --query "Parameters[?ParameterName=='shared_preload_libraries']"

3. 세 컬럼 읽는 법: total_exec_time, mean_exec_time, calls

튜닝 대상을 고를 때 세 값을 같이 본다. 각각 의미가 다르고, 하나만 보면 엉뚱한 쿼리를 잡는다.

total_exec_time - 그 쿼리가 지금까지 실행에 쓴 시간의 합계(밀리초). 서버 전체 부하 관점에서 우선순위를 정하는 핵심 지표다.
mean_exec_time - 한 번 실행에 걸린 평균 시간(밀리초). total_exec_time / calls에 해당한다. 개별 실행이 얼마나 무거운지 본다.
calls - 호출 횟수. 평균은 짧아도 호출이 많으면 합계가 커진다.

구분하면 이렇다. mean_exec_time은 작은데 total_exec_time이 큰 쿼리는 '자주 도는 쿼리'다. 애플리케이션 캐시나 호출 횟수 자체를 줄이는 게 답일 수 있다. 반대로 calls는 적은데 mean_exec_time이 큰 쿼리는 '한 방이 무거운 쿼리'다. 인덱스나 실행계획을 손볼 대상이다.

4. 느린 쿼리 Top N 뽑기

먼저 누적 부하 기준으로 total_exec_time 내림차순 Top 10을 뽑는다. 밀리초 값은 소수 자리가 길어서 round로 정리했다.

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

      queryid       | calls | total_ms | mean_ms |  rows  |                    query
--------------------+-------+----------+---------+--------+---------------------------------------------
 -643...c8         | 48213 | 512930.4 |   10.64 | 482130 | SELECT * FROM articles WHERE author_id = $1
  219...af         |   372 | 288114.9 |  774.50 | 371000 | SELECT count(*) FROM board_posts WHERE ...
  ...

여기서 첫 줄은 평균 10ms대인데 4만 번 넘게 불려 합계가 가장 크다. 애플리케이션이 목록을 그릴 때마다 작성자별 기사를 다시 조회하는 패턴, 즉 N+1로 볼 만한 모양이다. 둘째 줄은 호출은 적지만 한 번에 770ms대라 실행계획을 봐야 하는 대상이다.

'한 방이 무거운' 쿼리만 따로 보려면 정렬 기준을 mean_exec_time으로 바꾸고, 노이즈를 줄이려 최소 호출 횟수로 거른다.

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

캐시 적중 비율을 같이 보고 싶으면 shared_blks_hit와 shared_blks_read를 더한다. 디스크에서 많이 읽는 쿼리는 인덱스 후보다.

SELECT round(100.0 * shared_blks_hit
         / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

5. Top N을 인덱스와 쿼리로 튜닝하기

대상을 골랐으면 queryid로 원문 전체를 꺼내 EXPLAIN (ANALYZE, BUFFERS)로 실행계획을 확인한다. pg_stat_statements는 어디가 느린지 짚어 주지 않는다. 무엇을 먼저 볼지 순위만 정해 줄 뿐이다.

-- 원문 전체 보기
SELECT query FROM pg_stat_statements WHERE queryid = -643...c8;

-- 실행계획 확인 (정규화된 $1 자리에 실제 값을 넣어 실행)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM articles WHERE author_id = 42;

계획에 Seq Scan on articles가 뜨고 필터로 대부분 행이 버려지면 인덱스가 없거나 안 타는 상태다. 앞서 뽑은 첫 줄 쿼리는 author_id 조건이 반복되니 여기에 인덱스를 만든다.

CREATE INDEX CONCURRENTLY idx_articles_author_id
    ON articles (author_id);

CONCURRENTLY는 테이블을 잠그지 않고 인덱스를 만들어 운영 중 쓰기를 막지 않는다. 대신 트랜잭션 블록 안에서는 못 쓰고 시간이 더 걸린다. 자주 쓰는 조건 조합이면 정렬 컬럼까지 포함한 복합 인덱스나, 특정 상태만 자주 조회하면 부분 인덱스(WHERE status = 'published')가 더 좁고 효율적이다.

쿼리 쪽에서 풀 문제도 있다. 목록을 그리며 행마다 작성자 쿼리를 따로 던지는 N+1이면 인덱스로 개별 실행은 빨라져도 호출 횟수 자체가 남는다. 이럴 때는 애플리케이션에서 IN (...) 한 번이나 조인으로 묶어 calls를 줄이는 편이 근본 해결이다.

6. pg_stat_statements_reset으로 기간별 비교하기

확장은 서버 기동 이후(또는 마지막 리셋 이후)의 누적값을 보여준다. 그래서 '방금 만든 인덱스가 효과가 있나'를 보려면 카운터를 0으로 돌리고 같은 조건으로 다시 쌓아 비교해야 한다. 이 역할을 하는 함수가 pg_stat_statements_reset()이다.

-- 전체 통계 초기화
SELECT pg_stat_statements_reset();

-- 특정 쿼리 하나만 초기화 (userid, dbid, queryid)
SELECT pg_stat_statements_reset(0, 0, -643...c8);

인자를 0으로 두면 '전체'를 뜻한다. PostgreSQL 17부터는 네 번째 인자 minmax_only가 추가돼, true로 주면 누적값은 그대로 두고 min_exec_time과 max_exec_time 같은 최소/최대 통계만 리셋한다. 16 이하에는 이 인자가 없어 호출하면 함수 없음 오류가 난다.

-- PostgreSQL 17 이상: min/max 값만 리셋
SELECT pg_stat_statements_reset(0, 0, 0, true);

튜닝 전후 비교는 이 순서로 한다.

튜닝 전, 대표적인 부하 시간대에 pg_stat_statements_reset() 실행
일정 시간(예: 1시간) 트래픽을 받게 두고 Top N 저장
인덱스 생성 또는 쿼리 수정 배포
다시 reset() 후 같은 길이만큼 수집하고 Top N 비교

수집 기간이 언제 시작됐는지는 PostgreSQL 17의 stats_since 컬럼으로 확인한다. 이 값이 있어야 두 스냅샷이 같은 길이인지 판단할 수 있다.

-- PostgreSQL 17 이상
SELECT max(stats_since) AS newest_entry FROM pg_stat_statements;

16 이하에는 stats_since 컬럼이 없다. 대신 마지막 전체 리셋 시각을 pg_stat_statements_info에서 읽는다.

-- 버전 공통 (마지막 전체 리셋 시각)
SELECT stats_reset FROM pg_stat_statements_info;

튜닝 후 같은 쿼리의 total_ms와 mean_ms가 눈에 띄게 줄고 실행계획이 Index Scan으로 바뀌었다면 효과를 본 것이다. 개선폭은 데이터 양과 트래픽에 따라 다르지만, 순차 스캔이 인덱스 스캔으로 바뀌는 경우 평균 실행 시간이 한 자릿수 ms대로 떨어지는 일이 많다.

7. 정리

서버가 전반적으로 느릴 때는 개별 슬로우 로그보다 pg_stat_statements로 Top N을 먼저 뽑는 편이 빠르다. total_exec_time으로 우선순위를 정하고, mean_exec_time과 calls로 '무거운 한 방'과 '자주 도는 쿼리'를 갈라 대응한다. 실제 개선은 EXPLAIN으로 계획을 확인해 인덱스나 쿼리를 고치는 단계에서 일어나고, 효과 검증은 pg_stat_statements_reset()으로 기간을 맞춰 전후를 비교하면 된다. stats_since 컬럼과 minmax_only 인자는 PostgreSQL 17부터라는 점만 버전에 맞춰 챙기면 된다.