인덱스를 만들었는데 플래너가 안 쓴다 — EXISTS 가 selectivity 추정을 무너뜨릴 때

목록 API 가 첫 조회에서만 10초씩 걸렸다. 두 번째부터는 빨랐다. 쿼리는 흔한 형태다.

SELECT id FROM attachments
 WHERE room_id = $1
   AND <컬럼 필터 몇 개>
   AND EXISTS (SELECT 1 FROM posts p WHERE p.id = attachments.post_id)
 ORDER BY created_at DESC, id DESC
 LIMIT 50;

진단까지는 쉬웠다. (room_id, created_at DESC, id DESC) 복합 인덱스가 없어서 정렬을 못 준다. 큰 방은 후보가 80만 건인데, 50건 보여주려고 80만 건을 전부 읽어 정렬하고 있었다. LIMIT 이 일을 전혀 줄여주지 못하는 전형적인 top-N sort 다.

인덱스를 만들면 끝날 일처럼 보였다.

인덱스를 만들었는데 그대로 느렸다

인덱스를 만들고 다시 쟀다. 거의 그대로였다.

플랜을 보니 인덱스를 쓰긴 썼다. 다만 Index Scan 이 아니라 Bitmap Index Scan 이었고, 그 위에 Sort 가 그대로 얹혀 있었다.

Limit
  -> Sort  (Sort Key: created_at DESC, id DESC)     ← 그대로 남아 있다
       -> ... 
            -> Bitmap Index Scan using idx_new      ← 정렬이 아니라 필터로만 쓰임

정렬을 제공하라고 만든 인덱스가 필터 제공자로만 쓰였다. B-tree 인덱스는 정렬 순서대로 걸으면서 LIMIT 만큼만 읽고 멈출 수 있는데, bitmap 으로 바뀌는 순간 그 순서가 사라진다. 조기 종료는 여전히 일어나지 않았다.

추정 행 수가 무너지는 지점

EXPLAIN ANALYZE 로 노드별 추정 행 수와 실제 행 수를 따라가 봤다.

열 통계는 멀쩡했다. 첫 줄이 1.04x 다. 흔히 의심하는 n_distinct 왜곡 같은 게 아니었다.

무너진 곳은 EXISTS 의 semi join selectivity 추정이었다. 9,283 건이 나올 자리에 33 을 예측했다.

그리고 이게 왜 인덱스를 죽이는지가 핵심이다.

추정 결과가 33행이면 LIMIT 50 보다 작다. 플래너 입장에서는 "어차피 전부 반환될 것이고 50건에 도달하지도 못한다 → 정렬을 피해서 얻을 이득이 없다"가 된다. 이 인덱스의 유일한 가치인 조기 종료가 비용 모델에서 통째로 사라진다. 그러니 Sort 를 고른 것은 플래너 입장에서는 합리적인 선택이었다. 입력이 틀렸을 뿐이다.

ANALYZE 로는 고쳐지지 않는다

추정이 틀렸다고 하면 보통 통계부터 손본다. 하지만 이 경우엔 셋 다 듣지 않는다.

• ANALYZE — 열 통계는 이미 정확했다. 다시 수집해도 33 은 그대로다.
• ALTER TABLE ... ALTER COLUMN ... SET STATISTICS — 히스토그램 해상도를 올리는 것이고, 문제는 히스토그램이 아니다.
• CREATE STATISTICS (extended statistics) — 같은 테이블의 열 조합 상관관계를 다룬다. 서브쿼리 semi join 추정에는 개입하지 않는다.

셋 다 열/열-조합 통계를 다루는 도구인데, 무너진 곳은 조인 단계의 추정 로직이다. 도구의 사정거리 밖이다.

확인 삼아 정렬 경로를 강제로 막아봤다.

SET enable_sort = off;

같은 쿼리, 같은 인덱스인데 buffers 가 40,711 → 411, 실행 시간이 130ms → 0.65ms 로 떨어졌다. 인덱스의 잠재력은 충분한데 플래너가 꺼내 쓰지 않는 상태라는 게 확정됐다.

물론 enable_sort = off 를 프로덕션에 둘 수는 없다. 그 세션의 모든 쿼리에 영향을 주고, 정말로 정렬이 필요한 쿼리를 망가뜨린다. 진단 도구지 해결책이 아니다.

해결: 정렬과 LIMIT 을 물리적으로 격리한다

플래너를 설득하는 대신, 추정이 무너질 여지 자체를 없애기로 했다. PostgreSQL 에서 WITH ... AS MATERIALIZED 는 최적화 배리어다.

WITH cand AS MATERIALIZED (        -- MATERIALIZED 없으면 PG12+ 가 inline 해서 원위치
  SELECT id, post_id, created_at
    FROM attachments
   WHERE room_id = $1
     AND <단일 테이블 컬럼 필터만>   -- 조인, EXISTS 는 절대 넣지 않는다
   ORDER BY created_at DESC, id DESC
   LIMIT $N                        -- over-fetch: 최종 limit x k
)
SELECT c.id
  FROM cand c
 WHERE EXISTS (SELECT 1 FROM posts p WHERE p.id = c.post_id)
   AND <나머지 조건들>
 ORDER BY c.created_at DESC, c.id DESC
 LIMIT 50;

CTE 안쪽에는 단일 테이블 술어만 남긴다. 조인도 EXISTS 도 없으니 추정이 무너질 여지가 없고, 플래너는 자연스럽게 인덱스 순서를 타고 N 건에서 멈춘다. 힌트도, 세션 파라미터 조작도 필요 없다.

결과는 40,711 buffers / 130ms → 1,674 buffers / 3.0ms 였다.

이 방법의 대가

공짜는 아니다. 세 가지를 지켜야 한다.

MATERIALIZED 키워드는 생략할 수 없다. PostgreSQL 12 부터 CTE 는 기본적으로 inline 된다. 키워드를 빼면 옵티마이저가 CTE 를 바깥 쿼리에 합쳐버리고, 정확히 원래 플랜으로 되돌아간다. 이 한 단어가 전부다.

over-fetch 는 정확성 문제를 만든다. CTE 가 상위 N 건만 자르므로, 바깥 조건에서 탈락한 만큼 최종 결과가 LIMIT 에 미달할 수 있다. 그대로 두면 "데이터가 분명히 있는데 목록에 안 나오는" 버그가 된다. 결과가 부족하고 CTE 가 N 건을 꽉 채웠다면(= 더 있을 가능성이 있다면) N 을 늘려 재시도하는 루프를 두거나, keyset 페이지네이션으로 전환해야 한다. k 는 실제 데이터의 조건 통과율을 재서 정한다.

조건을 CTE 안으로 옮기고 싶은 유혹을 참아야 한다. 하나라도 조인이나 EXISTS 를 안으로 넣으면 추정 붕괴가 배리어 안까지 따라 들어온다. 배리어의 의미가 사라진다.

정리

느린 쿼리에 인덱스를 처방할 때는 두 가지를 따로 확인해야 한다.

1. 이 인덱스가 있으면 빨라지는가
2. 플래너가 이 인덱스를 고를 것인가

둘은 다른 질문이다. 보통은 1번만 확인하고 넘어가는데, 2번에서 조용히 실패하면 "인덱스를 만들었는데 왜 그대로지" 상태에 갇힌다.

실용적인 확인 순서는 이렇다.

1. 인덱스를 만든 뒤 EXPLAIN 에서 Index Scan 인지 Bitmap Index Scan 인지 본다. Sort 노드가 남아 있으면 정렬 제공에 실패한 것이다.
2. SET enable_sort = off 로 강제해 이득의 상한을 잰다. 여기서 크게 빨라지면 인덱스는 옳고 선택의 문제다.
3. 추정 행 수와 실제 행 수를 노드별로 훑어 어디서 무너지는지 특정한다. 열 통계가 정확한데 조인 단계에서 무너진다면 ANALYZE 는 답이 아니다.

3번까지 갔다면, 통계를 더 손보는 대신 쿼리 구조로 문제를 격리하는 쪽이 빠르다.

참고

• PostgreSQL - WITH Queries (CTE Materialization)
• PostgreSQL - How the Planner Uses Statistics
• PostgreSQL - CREATE STATISTICS
• PostgreSQL - Planner Method Configuration