이주호 · 개발 블로그

5. 느린 쿼리의 진짜 범인은 인덱스 — EXPLAIN이 가르고, 자동 패치는 멈춘다

11 min read

핵심 한 줄

GET /owners?lastName=Davis가 100k 행짜리 owners 테이블에서 QueryTimeoutException으로 터진다. 지연 메트릭은 “느리다”만 말하지만, EXPLAIN은 한 줄로 가른다 — Seq Scan on owners. 쿼리 수는 1인데(N+1과 정반대) 그 한 줄이 풀스캔으로 느리다. LIKE '%Davis%'의 앞 와일드카드가 인덱스를 죽인 것. 그런데 고치는 건 추론이 아니라 설계 판단이다 — 인덱스 추가냐 쿼리 재작성이냐, 둘 다 트레이드오프다. 그래서 자동 진단은 원인만 명명하고 PR을 열지 않는다(Class C): 고치는 방향은 사람 몫이다.

시리즈 — 장애를 '구별 가능'하게 만든 카오스 벤치

0.서문 · 1.스택트레이스가 곧 원인인 유일한 순간 · 2.p99가 튀는데 아무도 안 던졌다 · 3.200 OK인데 답이 틀렸다 · 4.top frame은 표면이지 원인이 아니다 · 5.느린 쿼리의 진짜 범인은 인덱스 · 6.커넥션을 안 돌려줬다 · 7.CPU가 남는데 스로틀당했다 · 8.컨슈머가 못 따라잡았다 · 9.멈춘 건 GC였다 · 10.다 멈췄는데 데드락은 아니다 · 11.요청 하나에 쿼리 1+N · 12.캐시 만료에 떼로 몰렸다

1. 배경 — 같은 타임아웃, 여러 원인

운영에서 p99 latency↑ 알림이 뜨면, 원인 후보가 십수 가지다. 락 경합·GC 폭주·N+1·커넥션풀 고갈·CPU 스로틀… 전부 같은 증상으로 도착한다. 이 시리즈의 목표는 각 원인을 다른 원인은 못 내는 단 하나의 신호로 가르는 것이다(0.서문).

W5의 주인공은 인덱스 부재(missing index) 다. 쿼리가 느릴 때 첫 직관은 흔히 “애플리케이션 로직이 비효율적이다”로 향한다. 코드를 뒤지고, 루프를 줄이고, 캐싱을 검토한다. 하지만 코드는 멀쩡하고, 문제는 DB 실행 계획이 처음부터 잘못된 경로로 흘렀던 것이다.

2. 무엇을 망가뜨렸나 — 앞 와일드카드가 인덱스를 걸어잠근다

chaos 프로파일에서 queryPlanRegression을 arm하면, owner 검색이 평소의 findByLastNameStartingWith 대신 findByLastNameContaining 을 통과한다.

// OwnerController.java (chaos seam — queryPlanRegression armed)
private Page<Owner> findPaginatedForOwnersLastName(int page, String lastname) {
    Pageable pageable = PageRequest.of(page - 1, 5);
    if (this.chaosFaults.useRegressedOwnerQuery()) {
        // 앞 와일드카드 LIKE '%name%' → last_name 인덱스 무력화 → Seq Scan
        return this.owners.findByLastNameContaining(lastname, pageable);
    }
    return this.owners.findByLastNameStartingWith(lastname, pageable);
}

seam은 ChaosFaults.useRegressedOwnerQuery()로 주입되어 chaos 프로파일에서만 살아난다(NoOpChaosFaults에서는 항상 false). 평소 빌드에는 이 분기가 실행되지 않는다.

차이는 한 글자다 — LIKE 'Davis%'(접두 검색)냐 LIKE '%Davis%'(앞 와일드카드)냐. B-tree 인덱스는 시작점을 알아야 range scan을 한다. 앞이 %면 시작점이 없으므로 인덱스를 아예 쓸 수 없다. 결과적으로 Postgres는 LargeOwnerSeeder가 generate_series로 씨드한 100k 행짜리 owners 테이블을 처음부터 끝까지 읽는다.

3. 증상 — QueryTimeoutException

부하를 주면 GET /owners?lastName=Davis가 per-statement 타임아웃을 넘기고 QueryTimeoutException을 던진다. 스택트레이스에는 JPA 레이어만 드러날 뿐, 어떤 쿼리가 왜 느린지는 나오지 않는다.

지연 메트릭(p99↑)도 마찬가지다. “느리다”는 사실만 안다 — 왜 느린지, 코드 문제인지 DB 구조 문제인지는 말해주지 않는다. 이게 이 원인 클래스의 핵심 함정이다: 지연 신호만으로는 코드 비효율과 인덱스 부재를 구별할 수 없다.

4. 판별 신호 — EXPLAIN이 한 줄로 가른다

QueryPlanProbe가 5초마다(chaos.queryplan.interval-ms=5000) EXPLAIN SELECT id FROM owners WHERE last_name LIKE ?를 실행하고, 결과를 파싱해 petclinic.query.seqscan 게이지를 발행한다(@Profile("chaos & postgres")). Seq Scan on owners가 등장하면 1, 아니면 0이다.

두 실행 계획을 나란히 놓으면 원인이 보인다.

항목정상 (LIKE ‘Davis%‘)회귀 (LIKE ‘%Davis%‘)
실행 계획Index Scan using owners_last_nameSeq Scan on owners
추정 비용0.28..8.290.00..1834.00
행 추정11
petclinic_query_seqscan01

petclinic_query_seqscan > 0이 되는 순간 QueryPlanRegression 알림이 발화한다. 지연 메트릭이 “느리다”고만 말할 때, EXPLAIN은 “Seq Scan on owners — 인덱스를 타지 못하고 있다” 고 말한다. 원인 축을 가르는 신호다.

여기에 두 번째 축이 같은 지연 패밀리 안의 사촌인 N+1과 갈라준다 — 요청당 쿼리 수다. 인덱스 부재는 쿼리 1개가 풀스캔으로 느리다(queries/request == 1). N+1은 정반대로 쿼리가 1+N개라서 느리다. 같은 p99↑라도 “쿼리 수=1인데 그 하나가 느림”과 “쿼리 수=1+N”은 정면으로 갈린다(정본 differential-table의 판별 기준).

EXPLAIN 전문은 warn 로그로 Loki까지 흘러간다. 스모크 스크립트(query-plan-regression-to-slack.sh)는 {service="petclinic"} |= "Seq Scan on owners" 쿼리로 Loki에서 이 로그를 확인하며 보강 신호가 전달됐음을 검증한다.

5. 자동 패치가 없는 이유 — 고치는 건 추론이 아니라 설계 판단

원인은 EXPLAIN이 명확히 짚어줬다. 그런데 고치는 방법은 하나가 아니다.

선택지 1: 인덱스를 추가한다. 앞 와일드카드 검색(LIKE '%term%') 자체를 지원하려면 pg_trgm 확장의 GIN 인덱스가 필요하다. B-tree 인덱스(owners_last_name)는 접두 검색에만 효과적이다. GIN 인덱스는 쓰기 부하와 스토리지 비용을 수반한다.

선택지 2: 쿼리를 재작성한다. LIKE '%term%'을 LIKE 'term%'(접두 검색)으로 바꾸면 기존 B-tree 인덱스를 탄다. 단, 검색 의미가 달라진다 — “Davis”를 찾을 때 “ADavis” 같은 성은 못 잡는다. 제품 요구사항에 따라 수용 가능할 수도, 불가능할 수도 있다.

어느 쪽이 맞는지는 EXPLAIN이 말해줄 수 없다 — 기능 요구사항, 인덱스 유지 비용, 쓰기 부하를 종합해 사람이 판단해야 한다. 원인은 명명됐지만(named), 고치는 방향은 설계 판단이다.

그리고 바로 이 지점이 자동 진단이 멈추는 경계다. 자동 진단(oncall)의 정본 규칙은 분명하다 — 카오스 벤치의 augmentation fault는, 고치는 게 설계 트레이드오프라도 Class C로 둔다. PR을 열지 않는다. 원인을 풍부하게 진단해 사람에게 넘길 뿐, 인덱스를 대신 추가하지 않는다. (일반적인 인덱스 부재라면 2~3개 선택지를 담은 draft PR = Class B일 수 있다. 하지만 이 differential 안에서 augmentation fault는 C를 유지한다 — 기계가 설계 결정을 가로채지 않게 하는 의도적 선이다.)

설령 인덱스를 추가하기로 사람이 정했더라도, 그건 마이그레이션 파일(src/main/resources/db/migration/)로 제안할 일이지 라이브 DDL이 아니다. ALTER TABLE owners ...를 운영 중에 바로 실행하는 것은 금지 — 장시간 테이블 락·복제 지연·롤백 불가 리스크를 감수할지조차 사람의 결정이다(머지는 사람만).

6. 교훈

'느리다'와 '인덱스 없다'는 다른 신호다

지연 메트릭은 십수 원인의 공통 증상이다. 인덱스 부재의 판별 신호는 EXPLAIN의 Seq Scan on owners — 이 한 줄이 “코드 비효율인가, DB 구조 문제인가”를 가른다. 그리고 고치는 방향(인덱스 추가 vs 쿼리 재작성)은 EXPLAIN이 말해줄 수 없다. 원인은 기계가 명명하고, 선택은 사람이 한다.

관련 글

그래프 뷰

백링크