[PostgreSQL] pg_stat_statements — 어떤 쿼리가 DB를 잡아먹는지 총시간 순으로 찾는다

설치·접속: PostgreSQL 설치와 접속

부제: DB가 느린데 범인 쿼리를 모를 때 — 개별 슬로우로그 말고 "누적 총 소요시간"으로 원흉을 지목한다

-- 총 실행시간이 큰 순으로 상위 5개 (진짜 병목은 여기서 나온다)
SELECT queryid,
       calls,
       round(total_exec_time)              AS total_ms,
       round(mean_exec_time, 2)            AS mean_ms,
       round(100*total_exec_time/sum(total_exec_time) OVER (), 1) AS pct,
       left(query, 60)                     AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- ★ 먼저 넘는 관문: 미리 로드 안 하면 조회 자체가 막힌다
shop=# SELECT count(*) FROM pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries
-- → postgresql.conf 에 등록 + 재시작이 필수(아래 설정 참고)

느린 DB를 잡을 때 흔한 함정은 "제일 느린 쿼리 한 방"만 찾는 것이다. 1초짜리 쿼리가 하루 10번이면 10초지만, 5ms짜리가 하루 100만 번이면 5000초 — DB를 실제로 태우는 건 후자다. pg_stat_statements는 실행된 쿼리를 상수만 뺀 형태로 정규화해 누적 집계한다(값이 다른 id=1, id=2를 같은 id=$1로 묶음). 그래서 total_exec_time(호출당 시간 × 호출 수) 순으로 정렬하면 위처럼 "전체의 몇 %를 이 쿼리가 먹는지"가 바로 드러난다 — 튜닝은 여기 상위 몇 개만 손대면 된다. 단, 이 확장은 공유 메모리에 후크를 심어야 해서 shared_preload_libraries에 등록하고 서버를 재시작해야 한다. 이 준비 없이 조회하면 위 출력처럼 must be loaded via shared_preload_libraries 에러가 난다 — 가장 많이 걸려 넘어지는 지점이다.

이렇게도 쓴다

준비: 설정에 등록하고 재시작한 뒤 확장을 만든다. (전제: 1회 설정)

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';  -- 그리고 DB 재시작
CREATE EXTENSION pg_stat_statements;                               -- 재시작 후 실행

 

디스크로 새는(temp) 쿼리를 따로 뽑아 work_mem 튜닝 대상을 찾는다. (조합: temp)

SELECT left(query,50), temp_blks_written FROM pg_stat_statements
WHERE temp_blks_written > 0 ORDER BY temp_blks_written DESC LIMIT 5;

 

캐시 밖(디스크 읽기)이 많은 쿼리를 찾아 인덱스 후보를 고른다. (조합: shared_blks_read)

SELECT left(query,50), shared_blks_read FROM pg_stat_statements
ORDER BY shared_blks_read DESC LIMIT 5;

 

호출 수가 폭발적인 쿼리를 찾아 N+1·캐시 누락을 잡는다. (조합: calls)

SELECT left(query,50), calls, mean_exec_time FROM pg_stat_statements
ORDER BY calls DESC LIMIT 5;

 

튜닝 전후를 비교하려면 통계를 리셋하고 다시 관찰한다. (조합: reset)

SELECT pg_stat_statements_reset();

 

실행시간 편차가 큰(불안정한) 쿼리를 표준편차로 찾는다. (조합: stddev)

SELECT left(query,50), mean_exec_time, stddev_exec_time FROM pg_stat_statements
ORDER BY stddev_exec_time DESC LIMIT 5;