여섯 번째 실행부터 느려지는 쿼리 — PostgreSQL generic plan 문제

배포 직후에는 빠른데 시간이 지나면 특정 쿼리가 느려져요. 서버를 재시작하면 다시 빨라지고, 또 한참 뒤에 느려져요.

로그를 자세히 보니 규칙이 보여요. 같은 커넥션에서 그 쿼리를 다섯 번까지는 빠르게 돌다가, 여섯 번째부터 느려져요. 데이터가 바뀐 것도 아니고 통계가 낡은 것도 아닌데요.

숫자가 정확히 여섯이라는 게 이상하잖아요. 이건 우연이 아니라 옵티마이저가 정해 둔 규칙이에요.

그리고 이 규칙을 모르면 "가끔 느려진다"로 몇 달을 보내게 돼요. 재현이 안 되니 조사도 못 하고, 재시작하면 낫는다는 이유로 우선순위가 계속 밀립니다.

왜 그런가

프리페어드 스테이트먼트에는 두 종류의 계획이 있어요.

하나는 맞춤 계획이에요. 실제 파라미터 값을 보고 매번 새로 세워요. 값의 분포를 알고 세우니 정확하지만, 실행할 때마다 계획 수립 비용이 들어요.

다른 하나는 일반 계획이에요. 파라미터 값을 모른다고 가정하고 한 번만 세워서 계속 재사용해요. 수립 비용이 한 번뿐이라 싸지만, 특정 값에 최적화되지 않아요.

PostgreSQL 은 이 둘을 자동으로 고르는데, 그 방식이 이래요. 처음 다섯 번은 맞춤 계획으로 실행하면서 비용을 기록해요. 여섯 번째에 일반 계획을 세워 보고, 그 비용이 지금까지의 맞춤 계획 평균보다 나쁘지 않으면 일반 계획으로 전환해요. 그 뒤로는 계속 일반 계획을 씁니다.

여기서 문제가 생겨요. 데이터가 치우쳐 있으면 일반 계획이 특정 값에서 크게 나빠져요.

예를 들어 상태 컬럼에서 완료가 99%, 대기가 0.1% 라고 해 볼게요. 대기를 조회할 때는 인덱스가 아주 유리하지만, 일반 계획은 평균적인 선택도를 가정하니 순차 스캔을 고를 수 있어요. 그러면 드문 값을 조회하는 요청이 여섯 번째부터 느려집니다.

코드로 보면

여섯 번째의 정체:

PREPARE q(text) AS
  SELECT * FROM orders WHERE status = $1;

EXECUTE q('pending');   -- 1회: 맞춤 계획, 인덱스 스캔
EXECUTE q('pending');   -- 2~5회: 계속 맞춤 계획
✕ EXECUTE q('pending'); -- 6회: 일반 계획으로 전환 → 순차 스캔

· 계획이 어떻게 잡혔는지 본다
✓ EXPLAIN (ANALYZE) EXECUTE q('pending');
✓ Seq Scan ... Filter: (status = $1)
✓ $1 이 보이면 일반 계획이다
✓ Index Scan ... (status = 'pending')
✓ 값이 박혀 있으면 맞춤 계획이다

· 치우친 데이터라면 맞춤 계획을 강제한다
✓ SET plan_cache_mode = 'force_custom_plan';

✕ 문제 지점 · ✓ 해법 · 그대로 돌려 보기 전에 자기 환경에 맞게 고쳐 주세요.

실전에서는

이 동작은 대부분의 경우에 합리적이에요. 계획 수립도 비용이고, 단순한 쿼리를 초당 수천 번 돌리면 그 비용이 무시할 수 없거든요. 일반 계획은 그걸 아끼는 장치예요.

문제가 되는 건 데이터가 치우쳤을 때예요. 값마다 최적 계획이 다른데 하나로 고정해 버리니까요.

여기서 헷갈리는 지점이 있어요. 개발자가 명시적으로 PREPARE 를 쓴 적이 없어도 이 일이 일어나요. 대부분의 드라이버가 자동으로 서버 사이드 프리페어를 쓰거든요. 그래서 "우리는 프리페어드 스테이트먼트 안 쓰는데요"라고 하면서 이 증상을 겪어요.

재시작하면 빨라지는 이유도 여기 있어요. 계획 캐시는 커넥션에 붙어 있어요. 커넥션이 새로 맺어지면 카운트가 0 부터 다시 시작하니 다섯 번은 다시 빨라져요. 커넥션 풀이 커넥션을 오래 유지하면 이 상태가 며칠씩 갑니다.

앞서 본 통계 문제와도 이어져요. ANALYZE 를 돌려 통계를 갱신해도 이미 캐시된 계획은 안 바뀔 수 있어요. 계획을 다시 세우게 하려면 프리페어를 다시 하거나 커넥션을 새로 맺어야 해요.

pgbouncer 를 transaction 모드로 쓰는 환경에서는 또 다르게 나타나요. 매 트랜잭션마다 다른 백엔드에 앉으니 계획 캐시가 잘 안 쌓여요. 이 문제가 덜 생기는 대신 계획 수립 비용을 매번 내는 셈이에요.

MySQL 은 구조가 달라요. 예전 버전에는 쿼리 캐시가 있었지만 지금은 없고, 실행 계획을 재사용하는 방식도 PostgreSQL 과 다릅니다.

처방

1. 증상이 "여섯 번째부터" 또는 "한참 뒤부터" 느려지는 형태면 이걸 먼저 의심하세요. 아주 특징적인 패턴이라 알면 바로 짚어요.
2. 계획 모드를 확인하고, 치우친 데이터에 대한 쿼리라면 맞춤 계획을 강제하세요. 세션 단위로 걸 수 있어서 문제가 되는 경로에만 적용할 수 있어요.
3. 반대로 단순하고 자주 도는 쿼리는 일반 계획이 유리해요. 전역으로 맞춤 계획을 강제하면 계획 수립 비용이 전체적으로 올라갑니다.
4. 치우친 컬럼의 통계 표본을 늘리세요. 자주 나오는 값 목록이 충분히 크면 일반 계획의 추정도 나아져요.
5. 애플리케이션에서 서버 사이드 프리페어를 쓰는지 확인하세요. 드라이버 기본값이 대개 켜져 있어요.
6. 값에 따라 성능이 갈리는 쿼리는 아예 분리하는 것도 방법이에요. 드문 값 전용 경로를 따로 만들면 계획이 섞이지 않아요.
7. 같은 쿼리의 실행 시간 분포를 보세요. 평균은 그대로인데 최댓값이 튀는 형태면 이 문제일 가능성이 큽니다.

지금 확인해 보세요

일반 계획인지 맞춤 계획인지:

· 계획에 $1 이 보이면 일반 계획이다
PREPARE q(text) AS SELECT * FROM orders WHERE status=$1;
EXPLAIN (ANALYZE) EXECUTE q('pending');   -- 여섯 번 반복
✕ Filter: (status = $1)        ← 일반 계획
✓ Index Cond: (status = 'pending')  ← 맞춤 계획

· 현재 모드
SHOW plan_cache_mode;

· 값에 따라 선택도가 얼마나 다른가
SELECT status, count(*),
       round(100.0*count(*)/sum(count(*)) over (),2) AS pct
  FROM orders GROUP BY status ORDER BY 2;
✕ 0.1% 와 99% 가 같이 있으면 이 문제가 나기 좋은 조건

· 같은 쿼리의 시간 분포 — 평균은 멀쩡한데 최댓값이 튄다
SELECT calls, mean_exec_time, max_exec_time, left(query,50)
  FROM pg_stat_statements ORDER BY max_exec_time DESC LIMIT 10;

핵심 한 줄여섯 번째부터 느려진다면 데이터가 아니라 계획이 바뀐 것이다.