"쿼리튜닝" 태그로 연결된 2개 게시물개의 게시물이 있습니다.

PostgreSQL planner는 테이블의 실제 데이터를 들여다보지 않아요. 100만 건짜리 테이블에 조건을 걸어도, planner는 그 100만 건을 세어 보는 대신 pg_statistic에 저장된 통계 요약만 읽고 "이 조건이면 대략 몇 row가 나오겠다"고 추정해요. 이 추정값이 맞으면 좋은 계획이 나오고, 틀어지면 seq scan을 해야 할 자리에 index scan을 고르거나 nested loop가 터지는 계획이 나와요. 그래서 쿼리가 느릴 때 인덱스보다 먼저 의심해야 할 게 통계예요.

이 글은 그 통계가 어디서 와서 어디에 저장되고 planner가 어떻게 읽는지를 따라갑니다. 시드 출처(richyen.com)와 PostgreSQL 공식 문서를 교차 확인하며 한국어 실무 관점으로 다시 엮었습니다.

왜 통계가 실행계획을 좌우하나​

planner의 일은 같은 쿼리를 실행하는 여러 방법(seq scan vs index scan, nested loop vs hash join, 조인 순서) 중 비용이 가장 싼 것을 고르는 것입니다. 비용을 계산하려면 각 단계에서 몇 row가 흘러갈지를 알아야 합니다. 그런데 쿼리를 실제로 돌려 보고 정할 수는 없으니, 미리 떠둔 통계로 추정합니다.

추정이 틀어지는 전형적인 예는 이렇습니다. 어떤 조건이 실제로는 4000 row를 반환하는데 planner가 8 row로 추정했다면, planner는 "8 row니까 index scan으로 한 건씩 찾아오는 게 싸겠다"고 판단합니다. 막상 실행하면 4000번을 random access로 긁느라 seq scan보다 훨씬 느려집니다. 계획 자체는 통계가 맞다는 가정 아래 합리적이었습니다. 통계가 틀렸습니다.

그래서 row 추정(row estimation)이 쿼리 성능의 출발점입니다. 시드 글의 표현을 빌리면 "planner는 쥐여 준 통계만큼만 똑똑하다".

ANALYZE가 통계를 만드는 과정​

통계를 채우는 명령은 ANALYZE입니다. autovacuum이 백그라운드에서 알아서 돌려 주지만, 대량 적재나 마이그레이션 직후에는 통계가 옛날 값이라 직접 실행해 주는 게 안전합니다.

ANALYZE customers;            -- 테이블 전체 컬럼 통계 갱신
ANALYZE customers (state);    -- 특정 컬럼만

여기서 주목할 점은 ANALYZE가 테이블 전체를 읽지 않는다는 사실입니다. 큰 테이블을 매번 통째로 스캔하면 비용이 감당되지 않으니, 무작위 샘플을 떠서 그걸로 분포를 추정합니다.

샘플 크기는 통계 타깃(statistics target)에 비례합니다. default_statistics_target이 기본값 100일 때 ANALYZE는 약 30,000개의 row를 샘플로 뽑습니다(테이블이 작으면 그보다 적게 뽑습니다). 무작위성을 보장하기 위해 Vitter의 reservoir sampling 알고리즘을 씁니다. 샘플을 다 모으면 컬럼별로 분포를 계산해 pg_statistic에 저장합니다.

이때 테이블 전체 규모를 나타내는 reltuples(행 수)와 relpages(블록 수)는 pg_class에 따로 저장됩니다. 이 값들은 실시간으로 갱신되지 않고 VACUUM, ANALYZE, 일부 DDL 시점에만 갱신되며, 전체를 스캔하지 않은 경우 스캔한 부분으로부터 근사값을 추정해 갱신합니다.

pg_statistic은 슬롯 구조라 사람이 직접 읽기 까다롭습니다. 그래서 PostgreSQL은 같은 내용을 사람이 읽기 좋게 펼친 pg_stats 뷰를 제공하고, 일반 사용자도 자기 권한 안에서 조회할 수 있습니다. 실무에서는 거의 항상 pg_stats를 봅니다.

아래는 ANALYZE가 통계를 만들어 planner에 닿기까지의 흐름입니다.

pg_stats 주요 컬럼 읽는 법​

pg_stats 한 행은 한 컬럼의 통계 요약입니다. 컬럼이 여럿이지만 실무에서 손이 가는 건 다섯 개 정도입니다.

조회는 이렇게 합니다.

SELECT attname, n_distinct, null_frac,
       most_common_vals, most_common_freqs,
       correlation
FROM pg_stats
WHERE tablename = 'customers' AND attname = 'state';

n_distinct​

n_distinct에서 눈여겨볼 것은 음수의 의미입니다 — 서로 다른 값의 추정 개수를 나타내지만 별도의 부호 규칙이 있습니다. 공식 문서 정의는 이렇습니다.

0보다 크면 컬럼의 distinct 값 추정 개수. 0보다 작으면 distinct 값 개수를 행 수로 나눈 값의 음수. (음수 형태는 테이블이 커질수록 distinct 값도 늘어날 것으로 ANALYZE가 판단할 때 쓰이고, 양수 형태는 가능한 값의 수가 고정돼 보일 때 쓰인다.) 예를 들어 -1은 distinct 값 수가 행 수와 같은 unique 컬럼을 뜻한다.

즉 state 컬럼이 50으로 나오면 "값이 50종이고 테이블이 커져도 50종일 것"이라는 뜻이고, 기본키처럼 -1이면 "모든 값이 다 다르다"는 뜻입니다. -0.5라면 "행 두 개당 distinct 값 하나꼴"입니다.

이 값이 실제와 어긋나면 추정이 통째로 흔들립니다. 실제로는 값이 만 종인데 통계가 100종으로 잡혀 있으면 planner는 100분의 1만 걸러질 조건을 만 분의 1로 착각합니다.

most_common_vals와 most_common_freqs​

여기서는 편향된 값을 읽습니다 — 분포가 한쪽으로 쏠린 컬럼에서는 흔한 값 몇 개가 통계를 지배합니다. most_common_vals(MCV)는 그 흔한 값들의 목록이고, most_common_freqs는 각 값이 차지하는 비율입니다. 두 배열은 같은 순서로 짝을 이룹니다.

예를 들어 state 컬럼에서 CA가 0.174, TX가 0.116으로 나온다면, planner는 WHERE state = 'CA'에 대해 "전체의 17.4%"라고 정확히 추정할 수 있습니다. 흔한 값은 빈도를 직접 들고 있으니 추측할 필요가 없습니다.

histogram_bounds​

histogram_bounds는 나머지 값의 분포를 담습니다 — MCV에 들지 못한 값들이 대상입니다. 공식 문서 정의는 "컬럼 값들을 거의 같은 개수의 그룹으로 나누는 경계값 목록"입니다. 즉 equi-depth histogram이라, 각 구간(bucket)이 데이터의 거의 같은 비율을 담습니다. 기본 타깃 100이면 경계가 약 101개 잡혀 구간마다 전체의 약 1%를 덮습니다.

중요한 성질이 두 가지 있습니다. 첫째, MCV에 들어간 값은 histogram 계산에서 빠집니다. 흔한 값은 MCV가 맡고 나머지는 histogram이 맡습니다. 둘째, 컬럼 타입에 < 연산자가 없거나 MCV가 전체를 다 덮으면 이 값은 NULL입니다.

WHERE signup_date < '2026-03-01' 같은 범위 조건의 추정이 여기서 나옵니다. 경계가 촘촘할수록 범위 추정이 정확해집니다.

correlation​

correlation으로는 index scan이 쌀지 가늠합니다 — 물리적 행 순서와 논리적 값 순서가 얼마나 일치하는지를 -1에서 +1로 나타냅니다. 공식 문서는 "값이 -1이나 +1에 가까우면 그 컬럼의 index scan이 0에 가까울 때보다 싸게 추정됩니다. random access가 줄어들기 때문"이라고 설명합니다.

값이 디스크에 정렬된 순서로 쌓여 있으면(예: 시간순 append) correlation이 1에 가깝고, index로 범위를 긁어도 디스크를 거의 순차로 읽습니다. 값이 무작위로 흩어져 있으면 0에 가깝고, index scan은 매번 다른 블록으로 점프해야 해서 비싸집니다. 같은 인덱스라도 이 값에 따라 planner의 선택이 갈립니다.

planner가 통계로 row를 추정하는 예​

추정의 뼈대 공식은 하나입니다.

추정 row = reltuples × selectivity

reltuples는 테이블 전체 행 수, selectivity는 조건이 걸러 내는 비율(0~1)입니다. selectivity를 어떻게 구하느냐가 통계가 쓰이는 지점입니다. 공식 문서의 Row Estimation Examples에 나온 tenk1(1만 행) 예시로 봅니다.

MCV에 있는 등치 조건​

조건 값이 most_common_vals에 있으면 그 빈도를 그대로 selectivity로 씁니다.

SELECT * FROM tenk1 WHERE stringu1 = 'CRAAAA';

'CRAAAA'의 most_common_freqs가 0.003이면 selectivity는 0.003, 추정 row는 10000 × 0.003 = 30입니다.

MCV에 없는 등치 조건​

조건 값이 MCV 목록에 없으면, MCV가 차지하지 않은 나머지를 남은 distinct 값들이 고르게 나눠 가진다고 가정합니다.

selectivity = (1 - sum(mcv_freqs)) / (n_distinct - num_mcv)

SELECT * FROM tenk1 WHERE stringu1 = 'xxx';

MCV 빈도 합이 0.03333, n_distinct가 676, MCV 개수가 10이면,

selectivity = (1 - 0.03333) / (676 - 10) = 0.0014559
추정 row = 10000 × 0.0014559 ≈ 15

n_distinct가 추정에 직접 들어가는 게 여기서 보입니다. 이 값이 틀리면 비-MCV 등치 조건이 통째로 빗나갑니다.

histogram을 사용하는 범위 조건​

<, > 같은 범위 조건은 histogram_bounds로 값이 어느 구간에 떨어지는지를 보고 비율을 보간합니다.

SELECT * FROM tenk1 WHERE unique1 < 1000;

histogram 경계가 {0, 993, 1997, 3050, ...}(10구간)이고 1000이 993~1997 구간 안에 있다면,

selectivity = (1 + (1000 - 993) / (1997 - 993)) / 10 = 0.100697
추정 row = 10000 × 0.100697 ≈ 1007

독립을 가정하는 AND 결합​

여러 조건이 AND로 묶이면 planner는 기본적으로 각 조건이 서로 독립이라 보고 selectivity를 곱합니다.

SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx';
selectivity = 0.100697 × 0.0014559 = 0.0001466
추정 row = 10000 × 0.0001466 ≈ 1

이 독립 가정이 다음 절의 함정으로 이어집니다. 추정값과 실제값은 EXPLAIN으로 바로 대볼 수 있습니다.

EXPLAIN ANALYZE
SELECT * FROM customers WHERE state = 'CA';
-- Seq Scan ... (rows=1740 ...) (actual ... rows=1736 ...)
--                    ↑ 추정              ↑ 실제

추정(rows=)과 실제(actual ... rows=)가 크게 벌어지는 노드가 통계 문제의 출발점입니다.

통계가 틀어질 때의 운영 점검​

default_statistics_target​

pg_statistic에 담기는 MCV/histogram 배열의 최대 길이는 컬럼별 ALTER TABLE ... SET STATISTICS로, 또는 전역 default_statistics_target으로 정합니다. 공식 문서 기준 기본 한도는 100입니다. 값을 키우면 샘플이 늘고 배열이 길어져 분포가 불규칙한 컬럼에서 추정이 정밀해지지만, pg_statistic 공간과 ANALYZE 시간을 더 씁니다.

-- 특정 컬럼만 정밀하게
ALTER TABLE customers ALTER COLUMN signup_date SET STATISTICS 1000;
ANALYZE customers;

특정 컬럼의 단일 추정이 계속 빗나가면, 전역값을 올리기 전에 그 컬럼만 타깃을 올리는 게 비용 대비 효과가 좋습니다.

다중 컬럼 상관과 extended statistics​

AND 결합의 독립 가정은 컬럼들이 실제로 상관돼 있으면 깨집니다. 시드 글의 예가 명확합니다. WHERE city = 'Cheyenne' AND state = 'WY'는 도시가 정해지면 주는 사실상 결정되는데, planner는 둘을 독립으로 보고 곱해 8 row로 추정합니다. 실제는 4012 row로, 약 500배 차이가 납니다.

PostgreSQL 10부터 도입된 extended statistics가 이 문제를 풉니다. CREATE STATISTICS로 관심 컬럼 조합을 등록하면 ANALYZE가 그 조합의 통계를 함께 모읍니다.

CREATE STATISTICS customers_city_state (dependencies, ndistinct)
  ON city, state FROM customers;
ANALYZE customers;

이후 같은 쿼리의 추정은 4087 row로, 실제(4012)에 거의 붙습니다. 단, dependencies(함수 종속성)는 컬럼을 상수와 비교하는 단순 등치 조건과 상수 IN 절에만 적용되고, 두 컬럼끼리 비교하거나 범위/LIKE 조건에는 쓰이지 않는다는 한계가 있습니다. 한 가지 더, extended statistics도 일반 단일 컬럼 통계와 같은 샘플로 계산되므로, 통계 타깃을 올리면 extended statistics도 함께 정밀해집니다.

통계가 의심될 때의 점검 순서​

추정과 실제가 벌어지는 노드를 찾았다면 순서대로 봅니다.

1. EXPLAIN ANALYZE로 추정 row와 실제 row의 차이를 짚습니다.
2. 문제 컬럼의 pg_stats에서 n_distinct와 MCV를 확인합니다.
3. 통계가 낡았으면 ANALYZE를 먼저 실행합니다.
4. 단일 컬럼 추정이 계속 빗나가면 그 컬럼의 SET STATISTICS를 올립니다.
5. 다중 컬럼 상관이 원인이면 extended statistics를 만듭니다.
6. 그래도 안 되면 그때 쿼리 재작성을 검토합니다.

대량 적재나 마이그레이션 직후, 파티션 추가 직후처럼 데이터가 급변한 시점에는 autovacuum의 ANALYZE를 기다리지 말고 직접 실행해 두는 습관이 추정 사고를 가장 많이 막아 줍니다.

정리​

planner는 데이터를 보지 않고 통계를 봐요. ANALYZE가 무작위 샘플로 분포를 떠서 pg_statistic에 채우고, pg_stats가 그걸 읽기 좋게 펼쳐요. 등치 조건은 MCV의 빈도나 n_distinct로, 범위 조건은 histogram_bounds로, index scan 여부는 correlation으로 추정해요. 추정이 빗나가면 인덱스를 의심하기 전에 pg_stats를 먼저 열어 보고, 단일 컬럼이면 통계 타깃을, 상관된 컬럼이면 extended statistics를 손봐요. 이 흐름만 손에 익으면 "왜 이 계획이 나왔지"의 절반은 통계 한 군데에서 답이 나와요.

참고한 출처:

• How Postgres Internal Stats Work (pg_stats): richyen.com
• PostgreSQL 공식 문서: 14.2. Statistics Used by the Planner
• PostgreSQL 공식 문서: 76.1. Row Estimation Examples
• PostgreSQL 공식 문서: pg_stats 뷰
• PostgreSQL 공식 문서: ANALYZE
• PostgreSQL 공식 문서: pg_statistic 카탈로그

pg_stat_statements는 컬럼이 스무 개가 넘지만, DBA가 매일 보는 건 사실 대여섯 개뿐이에요. 나머지는 그 대여섯 개를 해석할 때 보조로 쓰이며, 이 글에서는 핵심 컬럼을 읽는 법과 이 확장의 내부 동작 때문에 가끔 데이터가 비어 보이는 이유를 운영 관점에서 정리해요.

같은 주에 boringsql와 pganalyze가 이 주제를 각각 깊게 다뤘습니다. 두 글을 PostgreSQL 공식 문서로 교차 확인하면서 한국어 실무 가이드로 다시 엮었습니다.

pg_stat_statements가 수집하는 것​

pg_stat_statements는 공유 메모리에 있는 고정 크기 해시 테이블입니다. 개별 실행을 하나하나 기록하는 게 아니라, 같은 모양의 쿼리를 하나의 항목으로 묶어 누적 카운터를 갱신합니다.

여기서 "같은 모양"의 기준은 정규화(normalization)입니다. 상수를 떼어내고 $1, $2 같은 자리표시자로 바꾼 뒤, 파싱된 쿼리 트리의 해시값을 queryid로 삼습니다. 그래서 아래 두 쿼리는 하나의 항목으로 집계됩니다.

SELECT * FROM users WHERE id = 42;
SELECT * FROM users WHERE id = 99;

여기서 DBA가 미리 알아둬야 할 정규화의 성질이 몇 가지 있습니다.

• queryid는 메이저 버전 사이에서 안정적이지 않습니다. 같은 SQL이라도 메이저 업그레이드 후, 혹은 다른 CPU 아키텍처에서 다른 값으로 해시될 수 있습니다. 여러 서버의 통계를 묶을 때 queryid를 영구 조인 키로 쓰면 안 됩니다.
• OID 기반이라 이름 기반이 아닙니다. 테이블을 drop하고 다시 만들면 새 OID가 붙고, 그 결과 새 queryid가 생깁니다. 이전 통계는 고아로 남습니다.
• 구조에 민감합니다. 테이블 alias가 다르거나, 컬럼 목록이 바뀌거나, LIMIT이 붙거나, 조건 순서가 달라지면 논리적으로 같은 쿼리가 여러 항목으로 쪼개집니다. ORM이 이런 변형을 대량으로 쏟아내면 항목이 수백 개로 흩어지기도 합니다.

또 하나, 중요한 동작상의 제약이 있습니다. pg_stat_statements는 ExecutorEnd 단계에서 통계를 기록합니다. 즉 정상적으로 끝난 실행만 집계됩니다. 타임아웃으로 끊기거나 중간에 abort된 쿼리는 흔적을 남기지 않습니다. 장애 시점에 문제를 일으킨 쿼리가 정작 이 뷰에 안 보이는 경우가 여기서 나옵니다.

DBA가 봐야 할 핵심 컬럼​

전체 컬럼은 공식 문서에 다 있습니다. 여기서는 운영 중에 실제로 손이 가는 것만 추립니다.

느린 쿼리를 찾을 때 mean과 total 구분하기​

가장 먼저 정해야 할 건 정렬 기준입니다. mean_exec_time과 total_exec_time은 전혀 다른 질문에 답합니다.

• total_exec_time이 큰 쿼리는 서버 전체 부하에 가장 많이 기여하는 쿼리입니다. 한 번은 1ms로 빨라도 하루에 수천만 번 실행되면 누적 시간이 1위가 됩니다. 서버가 바쁜 원인을 찾을 때는 이 값을 봅니다.
• mean_exec_time이 큰 쿼리는 한 번 실행이 비싼 쿼리입니다. 빈도는 낮아도 실행할 때마다 사용자를 기다리게 만듭니다. 특정 화면이 느리다는 제보를 추적할 때 유용합니다.

-- 누적 부하 상위 10개
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

calls를 항상 함께 봐야 둘을 헷갈리지 않습니다. total_exec_time이 높은데 calls도 높으면 "자주 불려서 누적된" 것이고, calls가 낮은데 total이 높으면 "한 번이 무거운" 것입니다.

평균에서 사라지는 분포​

pg_stat_statements는 평균(mean)과 표준편차(stddev)만 유지하고, 개별 실행 시간은 즉시 버립니다. 그래서 분포의 모양을 알 수 없습니다.

예를 들어 99%는 1ms에 끝나지만 1%는 2초가 걸리는 쿼리가 있다면, 평균은 약 21ms 근처로 찍힙니다. 이 21ms는 어느 실행도 대표하지 못하는 숫자입니다. 정작 문제가 되는 2초짜리 1%는 나머지 99%에 희석돼 평균 뒤로 묻힙니다. 게다가 pg_stat_statements는 p99(실행의 99%가 그 안에 들어오는 시간, 상위 1%를 잘라낸 경계) 같은 percentile 값을 주지 않으니, 평균만 봐서는 이런 느린 실행을 따로 짚을 길이 없습니다. stddev_exec_time이 평균에 비해 유난히 크거나 max_exec_time이 평균과 크게 벌어져 있다면, 그 쿼리는 분포가 넓다는 신호이니 별도로 들여다볼 가치가 있습니다.

I/O 컬럼의 hit와 read 읽기​

shared_blks_hit과 shared_blks_read는 짝으로 읽습니다.

• shared_blks_hit은 buffer cache에서 바로 찾은 블록 수입니다. 빠릅니다.
• shared_blks_read는 buffer에 없어서 디스크(또는 OS page cache)에서 읽어 온 블록 수입니다. 느립니다.

shared_blks_read가 크다는 건 그 쿼리가 실제 I/O를 많이 일으킨다는 뜻입니다. 작업 집합이 buffer pool보다 크거나, 인덱스 없이 큰 테이블을 스캔하고 있을 가능성이 있습니다. 두 값을 합친 대비 적중률을 보면 캐시 효율을 가늠할 수 있습니다.

읽기/쓰기에 걸린 실제 시간까지 보려면 shared_blk_read_time, shared_blk_write_time 컬럼을 봅니다. 단, 이 값은 track_io_timing이 켜져 있어야 채워지고, 꺼져 있으면 전부 0입니다.

temp_blks_read, temp_blks_written은 임시 파일 I/O입니다. 정렬이나 해시 조인이 work_mem을 넘겨서 디스크로 흘러넘쳤다는 신호이므로, 이 값이 큰 쿼리는 work_mem 조정이나 쿼리 재작성 후보입니다.

쓰기 부하를 보여주는 WAL 컬럼​

wal_bytes, wal_records, wal_fpi는 그 쿼리가 만든 WAL 양을 보여줍니다. 쓰기가 많은 쿼리, 즉 replication 지연이나 디스크 쓰기 부하의 원인을 찾을 때 봅니다. wal_fpi(full page image)가 유난히 크면 checkpoint 직후의 첫 쓰기에서 페이지 전체가 WAL에 기록되는 패턴일 수 있습니다.

버전에 따른 total_time 분리​

PostgreSQL 13에서 타이밍 컬럼 이름이 바뀌었습니다. 12 이하에서는 total_time, mean_time 한 묶음이었지만, 13부터 계획(planning)과 실행(execution)이 분리되어 total_plan_time/total_exec_time, mean_plan_time/mean_exec_time으로 나뉘었습니다. 12 이하를 대상으로 한 옛 쿼리는 13 이상에서 컬럼명을 바꿔줘야 동작합니다.

단, 계획 시간 컬럼(total_plan_time 등)은 pg_stat_statements.track_planning이 켜져 있어야 채워집니다. 이 설정의 기본값은 off입니다. 계획 시간이 전부 0으로 보인다면 이 설정부터 확인합니다.

PostgreSQL 17부터는 stats_since(항목 통계 수집이 시작된 시각)와 minmax_stats_since(min/max 통계 수집 시작 시각) 컬럼이 추가됐습니다. 어떤 구간을 측정할 때 그 사이에 eviction이나 리셋이 끼었는지 판단하는 데 씁니다.

해시 테이블이 가득 찰 때의 eviction과 dealloc​

pg_stat_statements가 추적하는 서로 다른 쿼리 수에는 상한이 있습니다. pg_stat_statements.max이고 기본값은 5000입니다. 이 값은 서버 시작 시에만 설정할 수 있습니다.

서로 다른 쿼리 수가 이 상한을 넘으면 eviction(축출)이 일어납니다. 공식 문서는 "가장 적게 실행된(least-executed) 항목 정보를 버려서 새 항목 자리를 만든다"고 설명합니다. 내부적으로는 각 항목에 겉으로 드러나지 않는 usage 카운터가 있고, deallocation 시점에 모든 usage 값을 일정 비율 줄인 뒤 정렬해서 하위 일부를 버립니다. 이 정렬은 항목 수에 비례하는 작업이며, exclusive lock을 잡은 채로 진행됩니다.

그래서 pg_stat_statements.max를 무작정 키우는 건 공짜가 아닙니다. max가 클수록 deallocation 시점의 정렬 비용이 커지고, 그동안 lock 경합도 길어집니다.

축출이 일어났는지는 별도 뷰인 pg_stat_statements_info로 확인합니다. 이 뷰는 단 한 행이고 컬럼 두 개를 가집니다.

SELECT dealloc, stats_reset FROM pg_stat_statements_info;

dealloc이 계속 올라간다면, 테이블이 쉼 없이 가득 차고 비워지는 중입니다. 이 상태에서는 자주 실행되지 않는 쿼리의 통계가 수시로 사라지므로 데이터를 신뢰하기 어렵습니다. ORM이 구조가 조금씩 다른 쿼리를 대량으로 흘려보내 해시 테이블을 가득 채우는 경우가 대표적입니다. 이때 실행 빈도가 낮은 항목이 먼저 축출되면서, 나중에 그 쿼리를 되짚어볼 단서 자체가 남지 않습니다.

대응은 두 갈래입니다. max를 적당히 늘리거나(정렬 비용 증가를 감수하고), 애초에 쿼리 변형 수를 줄이는 것입니다. 예컨대 길이가 제각각인 IN 목록은 PostgreSQL 18 이전까지 길이마다 별도 항목을 만들었는데, = ANY($1) 배열 바인딩으로 바꾸면 버전과 무관하게 하나의 항목으로 묶입니다.

아래 도식은 새 쿼리가 들어왔을 때 항목이 잡히고 축출되기까지의 흐름입니다.

운영 관점의 리셋 전략과 함정​

단조 증가하는 누적 통계​

뷰의 카운터는 마지막 리셋 이후로 계속 쌓입니다. 그래서 "지금 어떤 쿼리가 부하를 일으키는가"를 보려면 두 시점의 스냅샷을 떠서 빼는 수밖에 없습니다. 한 번 조회한 절댓값만으로는 한 달 전 배치 작업과 방금 들어온 트래픽이 뒤섞여 보입니다.

모니터링 도구들이 일정 간격으로 뷰를 떠다가 차분을 계산하는 이유가 이것입니다. 직접 차분을 계산할 때는 그 사이에 eviction이나 리셋이 끼면 음수가 나올 수 있으니, stats_since(17 이상)나 pg_stat_statements_info의 dealloc/stats_reset을 함께 확인해야 합니다.

리셋 함수​

pg_stat_statements_reset()으로 통계를 비웁니다. 인자 없이 호출하면 전체를 리셋하고, 특정 대상만 비울 수도 있습니다.

-- 전체 리셋
SELECT pg_stat_statements_reset();

-- 특정 user / db / queryid만 리셋 (0은 "전체" 의미)
SELECT pg_stat_statements_reset(0, 0, :queryid);

minmax_only 인자를 true로 주면 min/max_exec_time, min/max_plan_time만 비웁니다. 평균과 누적은 살려두고 최소/최대만 다시 측정하고 싶을 때 씁니다. 기본값은 superuser만 실행할 수 있고, 필요하면 GRANT로 권한을 넘깁니다.

리셋 전략에는 정답이 없습니다. 흔한 선택은 두 가지입니다.

• 리셋하지 않고 누적 + 차분. 모니터링 도구에 맡기는 방식. 장기 추세를 잃지 않습니다.
• 정기 리셋(예: 배포/점검 전후). 특정 구간만 깨끗하게 보고 싶을 때. 단, 리셋 순간 이전 데이터는 사라지니 추세 분석은 포기합니다.

자주 밟는 함정 정리​

• 타임아웃/abort된 쿼리는 집계되지 않습니다. 장애 원인 쿼리가 뷰에 없을 수 있습니다.
• 평균만으로 판단하면 드물게 튀는 느린 실행을 놓칩니다. max_exec_time/stddev로 분포를 함께 의심합니다.
• dealloc이 오르는 상태에서는 통계가 불완전합니다. 먼저 그것부터 해결합니다.
• 계획 시간(*_plan_time)이 0이면 track_planning이 꺼진 것입니다.
• I/O 시간(shared_blk_read_time 등)이 0이면 track_io_timing이 꺼진 것입니다.
• queryid는 메이저 버전/아키텍처 간에 달라질 수 있어 영구 키로 부적합합니다.

정리​

pg_stat_statements는 컬럼이 많아 보여도 DBA의 시선은 결국 몇 갈래로 좁혀져요. 부하의 원인은 total_exec_time, 느린 한 방은 mean_exec_time으로 찾고 둘 다 calls로 맥락을 잡으며, I/O가 의심되면 shared_blks_read와 temp 블록을, 쓰기가 의심되면 wal_bytes를 봐요. 데이터가 비어 보이거나 음수가 나오면 pg_stat_statements_info의 dealloc과 stats_reset을 먼저 확인하면 되고, 이 정도만 손에 익히면 대부분의 쿼리 튜닝은 이 뷰 하나에서 출발할 수 있어요.

참고한 출처:

• pg_stat_statements: the good, the bad and the ugly — boringsql
• Postgres in Production: pg_stat_statements Deep Dive (Part 4) — pganalyze
• PostgreSQL 공식 문서 — F.32. pg_stat_statements
• Queries for pg_stat_statements — pgMustard