인덱스가 선택되지 않는 순간 - 컬럼에 캐스팅 하나가 3,000배, 그리고 추정이 31.7배 틀려도 계획이 안 바뀌는 경우

실행 계획을 읽는 법에서 “옵티마이저가 안 쓰는 것인가, 못 쓰는 것인가를 먼저 구분해야 한다”고 썼다. 그 글은 문서를 근거로 썼고, 마지막에 세 가지를 재 보자고 적어 뒀다. 이 글이 그 측정이다.

저장소는 data-ops-lab이고 재실행은 한 줄이다.

./.venv/bin/python experiments/index/run.py

조건

orders 200만 행, 인덱스 여섯 개. city가 country를 결정하도록 만들어 옵티마이저의 독립 가정이 최대로 틀리게 했다. 각 항목 5회, 중앙값.

병렬 질의를 껐다(max_parallel_workers_per_gather = 0). 병렬 스캔에서 EXPLAIN의 Actual Rows는 워커당 평균이라 추정과 직접 비교할 수 없기 때문이다. 이 실험이 재는 것은 추정의 정확도이지 병렬 실행이 아니다.

결과

조건	계획	추정	실제	실제/추정	중앙값
선택도 0.9%	Index Only Scan	17,000	17,873	1.1	28.5 ms
선택도 2.1%	Index Only Scan	40,333	42,277	1.0	41.4 ms
선택도 97%	Seq Scan	1,942,667	1,939,850	1.0	357.2 ms
created_at >= ? AND < ?	Bitmap Heap Scan	5,551	5,401	1.0	9.6 ms
date(created_at) = ?	Seq Scan	10,000	5,401	0.5	292.1 ms
amount_text = '5000'	Bitmap Heap Scan	21	24	1.1	0.07 ms
amount_text::bigint = 5000	Seq Scan	10,016	24	0.0	208.7 ms
city+country, 확장 통계 없음	Bitmap Heap Scan	62,756	250,000	4.0	271.7 ms
city+country, 확장 통계 있음	Bitmap Heap Scan	249,506	250,000	1.0	157.9 ms
낡은 통계	Index Only Scan	21,390	678,645	31.7	124.3 ms
ANALYZE 후	Index Only Scan	692,090	678,645	1.0	122.8 ms
user_id = ? ORDER BY created_at	Index Scan	11	10	0.9	0.11 ms
user_id < ? ORDER BY created_at	Bitmap Heap Scan + Sort	1,011	1,015	1.0	4.08 ms

못 쓰는 것: 컬럼을 건드리면 인덱스가 사라진다

두 항목이 같은 원인이다. 조건절에서 컬럼에 무언가를 씌우면 그 컬럼의 인덱스와 통계를 둘 다 못 쓴다.

date(created_at) = '2026-06-01'은 순차 스캔 292.1ms, 같은 의미의 범위 조건은 9.6ms. 30배다. 추정도 망가진다. 옵티마이저는 표현식의 선택도를 모르므로 고정된 추측값 10,000을 썼고 실제는 5,401이었다.

암묵적 형변환은 더 나쁘다. amount_text::bigint = 5000은 208.7ms, 같은 값을 문자열로 비교하면 0.07ms. 약 3,000배이고 추정은 실제의 417배(10,016 대 24)였다.

이 둘이 “인덱스가 있는데 안 탄다”의 대부분이고, 처방은 인덱스 추가가 아니다. 조건을 sargable하게 바꾸거나 표현식 인덱스를 만드는 것이다.

안 쓰는 것: 대개 옵티마이저가 옳다

선택도 97%에서 순차 스캔을 골랐고 추정은 1.0배로 정확했다. 인덱스로 200만 행 중 194만 행을 찾아 힙으로 가는 것보다 전부 읽는 것이 빠르다. 여기서 인덱스를 강제하면 느려진다.

추정이 정확한 상태에서의 순차 스캔은 고칠 것이 없다. 고칠 것은 추정이 틀린 순차 스캔이다.

상관관계: 독립 가정만큼 정확히 틀린다

city = 'seoul' AND country = 'kr'의 추정이 62,756, 실제가 250,000이었다. 4.0배 과소추정이고, 이 값은 우연이 아니다.

P(city='seoul') = 1/8
P(country='kr') = 2/8      (kr에 도시가 둘)
곱하면 1/32 × 2,000,000 = 62,500   ← 추정 62,756
실제는 1/8 × 2,000,000  = 250,000

옵티마이저는 두 컬럼이 독립이라고 가정하고 선택도를 곱한다. city가 country를 결정하는 관계는 그 가정을 정면으로 어긴다.

CREATE STATISTICS ... (dependencies, ndistinct)로 확장 통계를 만들자 추정이 249,506(1.0배)이 됐고, 실행 시간도 271.7ms에서 157.9ms로 줄었다. 추정이 맞으니 더 나은 계획이 선택된 것이다.

추정이 31.7배 틀려도 계획이 안 바뀌는 경우

전체의 3분의 1을 FAILED로 바꾸고 ANALYZE를 돌리지 않았다. 추정 21,390, 실제 678,645. 31.7배 과소추정이다.

그런데 계획이 바뀌지 않았다. 낡은 통계와 갱신 후가 같은 인덱스 스캔이고 시간도 124.3ms 대 122.8ms로 거의 같다.

이것이 이 실험에서 가장 조심스럽게 읽어야 할 줄이다. 추정 오차가 크다고 항상 계획이 나빠지는 것은 아니다. 낡은 통계는 “지금 느리다”가 아니라 “언제든 계획이 뒤집힐 수 있다”는 상태다. 위 상관관계 항목이 뒤집힌 쪽의 예이고(271.7 → 157.9ms), 이 항목은 아직 안 뒤집힌 쪽의 예다.

그래서 진단할 때 추정 오차를 보는 이유는 지금의 느림을 설명하기 위해서만이 아니다. 다음 배포나 다음 데이터 증가에서 무엇이 뒤집힐지를 미리 보는 것이다.

정렬: 선두 컬럼이 범위면 정렬이 돌아온다

복합 인덱스 (user_id, created_at)에서

• user_id = 42 ORDER BY created_at DESC → Index Scan, Sort 노드 없음, 0.11ms
• user_id < 100 ORDER BY created_at DESC → Bitmap Heap Scan + Sort, 4.08ms

선두 컬럼이 등치면 그 안의 created_at이 이미 정렬돼 있어 인덱스 순서를 그대로 쓴다. 범위면 여러 user_id의 created_at이 섞이므로 전체 순서가 보장되지 않아 정렬이 필요하다. B+Tree 인덱스의 내부에서 구조로 설명한 것이 실행 계획의 Sort 노드로 나타난다.

실무로 옮기면

1. EXPLAIN이 아니라 EXPLAIN ANALYZE를 본다. 추정만으로는 무엇이 틀렸는지 알 수 없다.
2. 추정과 실제의 비율이 가장 큰 노드를 먼저 찾는다. 그 아래 계획은 전부 틀린 전제 위에 있다.
3. 순차 스캔을 보면 추정부터 확인한다. 추정이 정확하면 대개 옳은 선택이다.
4. 조건절에서 컬럼을 건드리고 있는지 본다. 함수, 형변환, 연산이 붙어 있으면 인덱스는 존재해도 없는 것과 같다.
5. AND로 묶인 컬럼들이 상관있는지 본다. 있으면 확장 통계를 검토한다.
6. 추정이 크게 틀린데 지금은 빠른 곳을 기록해 둔다. 뒤집힐 후보다.

키 생성 병목 시리즈와 대량 배치에서 다룬 것은 쓰기 경로였다. 읽기 경로에서도 순서는 같다. 추측으로 인덱스를 추가하기 전에 계획을 읽고, 계획을 믿기 전에 추정과 실제를 비교한다. ParityPay 2편에서 잠금 보유 17ms를 DB 실행 2.31ms와 대기 14.7ms로 나눠 본 뒤에야 고칠 곳이 보인 것과 같은 절차다.

측정에서 틀렸던 것 둘

시드가 상관관계를 안 만들었다. 처음에는 CROSS JOIN LATERAL (SELECT ... ORDER BY random() LIMIT 1)로 도시를 뽑았는데, 이 서브쿼리가 바깥 행을 참조하지 않아 PostgreSQL이 한 번만 평가했다. 200만 행 전부가 같은 도시가 됐고, 상관관계 항목의 실제 행 수가 0으로 나왔다. 배열 인덱싱으로 바꿔 행마다 달라지게 고쳤다.

병렬 스캔이 추정과 실제를 비교 불가능하게 만들었다. 낡은 통계 항목에서 실제 행 수가 678,560과 226,187로 달라 보였는데, 후자는 워커 3개로 나눈 값이었다(226,187 × 3 = 678,561). EXPLAIN의 Actual Rows는 루프당 평균이다. 추정 정확도를 재는 실험이므로 병렬을 끄는 쪽을 택했고, 그 사실을 표 위에 적었다.

둘 다 “그럴듯한 숫자가 나왔지만 재고 있던 것이 아니었던” 경우다. T1의 배리어 문제와 같은 종류이고, 결과가 이론과 맞아 보일수록 도구를 먼저 의심해야 한다는 쪽에 가깝다.

한계

• 단일 테이블, 조인 없음. 추정 오차가 복리로 커지는 곳은 조인 순서와 조인 방식 선택인데, 여기서는 재지 않았다.
• 첫 실행 후 전부 메모리에 있다. shared_buffers 512MB에 테이블이 그보다 작아 웜 상태다. 콜드 캐시라면 모든 수치가 커진다.
• 항목당 5회 중앙값이다. 이 규모에서 안정적이지만 분포를 말하지는 않는다.
• PostgreSQL만 쟀다. MySQL의 옵티마이저와 통계는 다르게 동작한다.
• 낡은 통계가 계획을 뒤집는 사례를 만들지 못했다. 뒤집히는 조건을 일부러 구성하면 더 강한 증명이 되는데, 이 실험에는 없다.

정리

• 컬럼에 함수나 형변환이 붙으면 인덱스와 통계를 둘 다 못 쓴다. 각각 30배, 3,000배였다.
• 추정이 정확한 상태의 순차 스캔은 대개 옳다. 고칠 것은 추정이 틀린 순차 스캔이다.
• 상관있는 컬럼을 AND로 묶으면 독립 가정만큼 정확히 틀린다. 확장 통계가 그것을 고치고 시간도 줄였다.
• 추정이 31.7배 틀려도 계획이 안 바뀔 수 있다. 낡은 통계는 지금의 느림이 아니라 뒤집힐 가능성이다.
• 복합 인덱스의 선두 컬럼이 범위이면 뒤 컬럼의 정렬 이점이 사라지고 Sort 노드가 돌아온다.
• 결과가 이론과 맞아 보일수록 측정 도구를 먼저 의심한다. 시드와 병렬 설정이 둘 다 그럴듯한 오답을 만들었다.

참고

• data-ops-lab — 원본은 reports/data/t2-index.json, 표는 reports/01-experiment-report.md
• 실행 계획을 읽는 법 — 이 실험의 개념 짝
• B+Tree 인덱스의 내부
• PostgreSQL: Extended Statistics