Index Design

인덱스 설계: Menu와 Review

이 문서에서 다루는 것

BookingSlot·Reservation과 같은 측정 → 발견 → 결정 흐름입니다. 두 도메인 모두 단순히 "인덱스를 추가했다"는 결론이 아니라, Review에서는 잘못 설계된 인덱스가 Planner를 혼란시켜 성능을 오히려 악화시키는 상황을 실제 EXPLAIN으로 확인하고 제거하기까지의 과정을 다룹니다.

---

1. Menu 인덱스

초안 EXPLAIN에서 문제 발견 (200건)

-- WHERE business_id = ? AND is_active = true ORDER BY created_at DESC

Sort  (cost=4.41..4.42 rows=1 width=381) (actual time=0.147..0.154 rows=180 loops=1)
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 50kB
  ->  Seq Scan on menu  (cost=0.00..4.40 rows=1 width=381) (actual time=0.008..0.050 rows=180 loops=1)
        Filter: (is_active AND (business_id = '...'))
        Rows Removed by Filter: 20
Planning Time: 0.082 ms
Execution Time: 0.171 ms

Seq Scan + Filter: 200건 전체를 읽고 20건을 버립니다. rows=1 예상 vs 180 실제 — 통계 오류 17,900%입니다. 별도 Sort 노드에서 50kB quicksort가 발생합니다.

데이터가 200건이라 0.171ms로 빠르지만, 구조적 문제를 확인했습니다. Seq Scan은 데이터 증가에 비례해 선형으로 느려집니다.

인덱스 설계

@Table(name = "menu", indexes = {
    @Index(name = "idx_menu_business_active_created",
           columnList = "business_id, is_active, created_at DESC")
})

is_active를 인덱스에 포함한 이유: 업체의 비활성 메뉴는 고객에게 노출되지 않습니다. 조회의 90% 이상이 is_active = true 조건을 포함합니다. 인덱스에 포함하면 Filter가 Index Cond로 전환되어 비활성 레코드를 스캔 전에 제외할 수 있습니다. is_active는 Boolean이라 선택도는 낮지만, business_id로 먼저 좁힌 뒤 두 번째 컬럼으로 필터링하는 패턴에서 의미가 있습니다.

소량 검증 (200건)

-- 인덱스 추가 후

Index Scan using idx_menu_business_active_created on menu
  (cost=0.14..8.16 rows=1 width=381) (actual time=0.009..0.049 rows=180 loops=1)
  Index Cond: ((business_id = '...') AND (is_active = true))
  Buffers: shared hit=6
Planning Time: 0.181 ms
Execution Time: 0.068 ms

Seq Scan + Sort → Index Scan. Sort 노드가 사라졌습니다. created_at DESC가 인덱스 컬럼 순서와 일치해 별도 정렬이 필요 없습니다. Execution Time 0.171ms → 0.068ms (60% 개선).

빅스케일 검증 (100만건 중 2,250건)

-- WHERE business_id = ? AND is_active = true ORDER BY created_at DESC

Index Scan using idx_menu_business_active_created on menu
  (cost=0.42..3810.25 rows=2238 width=122) (actual time=0.015..0.878 rows=2250 loops=1)
  Index Cond: ((business_id = '...') AND (is_active = true))
  Buffers: shared hit=2257
Planning Time: 0.367 ms
Execution Time: 0.958 ms

100만건에서 Index Scan이 유지됩니다. 2,250건을 0.958ms에 처리합니다. Sort 노드가 없습니다.

카테고리별 조회는 별도 인덱스를 추가하지 않았습니다. WHERE business_category_id = ? AND is_active = true 패턴을 테스트하면 100만건에서 Parallel Seq Scan 70ms가 나옵니다. 그러나 이 조회는 카테고리 목록 화면에서 한 번 호출되고, 메뉴 수정 등 관리 화면에서만 사용됩니다. 호출 빈도가 낮고 business_category_id 단독 인덱스는 다른 쿼리에서 거의 사용되지 않아 추가하지 않았습니다. 필요 시 (business_id, business_category_id, is_active) 인덱스를 고려할 수 있습니다.

---

2. Review 인덱스: 잘못된 인덱스 제거

초기 인덱스 구성과 문제

Review 엔티티는 초기에 세 개의 인덱스를 가지고 있었습니다.

@Index(name = "idx_review_deleted",
       columnList = "deleted_at")                        // Soft Delete 필터용

@Index(name = "idx_review_user_created",
       columnList = "user_id")                           // created_at 없음!

@Index(name = "idx_review_business_rating",
       columnList = "business_id, rating")               // 평점 필터용

idx_review_user_created에 created_at이 빠져 있습니다. ORDER BY created_at DESC 처리를 위해 Sort가 필요합니다.

idx_review_business_rating은 평점별 조회를 위해 추가되었지만, 실제 쿼리 패턴을 보면 평점 필터(WHERE rating >= 4)보다 전체 리뷰 목록(ORDER BY created_at DESC)이 압도적으로 많습니다.

소량 EXPLAIN (1,000건)

-- WHERE user_id = ? AND deleted_at IS NULL ORDER BY created_at DESC

Sort  (cost=12.93..12.94 rows=1 width=334) (actual time=0.671..0.702 rows=900 loops=1)
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 158kB
  ->  Bitmap Heap Scan on review
        Recheck Cond: ((deleted_at IS NULL) AND (user_id = '...'))
        ->  BitmapAnd
              ->  Bitmap Index Scan on idx_review_deleted
                    Index Cond: (deleted_at IS NULL)
                    Buffers: shared hit=4    -- 2,700건
              ->  Bitmap Index Scan on idx_review_user_created
                    Index Cond: (user_id = '...')
                    Buffers: shared hit=21   -- 3,000건
Planning Time: 0.096 ms
Execution Time: 0.754 ms

Planner가 단일 인덱스를 사용하지 않고 idx_review_deleted와 idx_review_user_created 두 개를 동시에 스캔한 뒤 BitmapAnd로 병합합니다. deleted_at IS NULL이 90%를 커버하는 저선택도 조건임에도 별도 인덱스로 참여합니다. Sort는 여전히 발생합니다.

빅스케일에서 Planner 잘못 선택 (10,000건)

-- WHERE business_id = ? AND deleted_at IS NULL ORDER BY created_at DESC (10,000건)

Sort  (cost=...) (actual time=... rows=9000 loops=1)
  Sort Key: created_at DESC
  Sort Method: quicksort  Memory: 1861kB   -- 1.8MB
  ->  Bitmap Heap Scan on review
        ->  Bitmap Index Scan on idx_review_business_rating   -- 잘못된 인덱스 선택!
              Index Cond: (business_id = '...')
              Buffers: shared hit=225
Execution Time: 7.5~8 ms

business_id, created_at DESC 인덱스가 있음에도 Planner가 idx_review_business_rating (business_id, rating)을 선택합니다. 이유는 두 가지입니다. 첫째, 통계 오류로 rows=1을 예상해 "데이터가 적다"고 판단했습니다. 둘째, rating 인덱스가 created_at 없이 단순해 보여 Sort 비용을 과소평가했습니다. 결과적으로 Sort 노드에서 1.8MB quicksort가 발생하고, 이것이 전체 실행 시간의 80%를 차지합니다.

50,000건에서 디스크 spill

-- 동일 쿼리, 50,000건

Sort Method: external merge  Disk: 6792kB   -- 디스크 사용!
Buffers: shared hit=1155
Execution Time: 43.313 ms

45,000건 정렬이 work_mem을 초과해 디스크로 spill됩니다. 디스크 I/O가 개입되면서 43ms가 됩니다. 전체 시간의 70%가 Sort 때문입니다.

근본 원인 분석

Planner 판단 과정:

business_created 인덱스 (business_id, created_at DESC):
  → business_id: Index Cond ✅
  → created_at: 이미 정렬됨, Sort 불필요 ✅
  → deleted_at: Filter (10% 오버헤드)
  → Cost 예상: 낮음

business_rating 인덱스 (business_id, rating):
  → business_id: Index Cond ✅
  → rating: 이 쿼리에서 사용 안 함 ❌
  → created_at: 없음, Sort 필요 ❌
  → Cost 예상: "통계 오류로" 낮게 계산

→ 결과: Planner가 business_rating을 선택 (오답)

통계가 부정확한 상태에서 여러 인덱스가 경쟁하면 Planner가 잘못된 선택을 할 수 있습니다. idx_review_business_rating은 평점 필터가 필요한 경우(전체 API 호출의 5% 미만)를 위해 추가되었지만, 주요 쿼리 패턴에서는 Planner를 오히려 혼란시켰습니다.

잘못된 인덱스 < 인덱스 없음. 잘못 설계된 인덱스는 단지 비효율에 그치지 않고, Planner가 더 나은 인덱스 대신 이것을 선택하게 유도합니다.

해결: 인덱스 재설계

@Table(name = "review", indexes = {
    // 고객별 리뷰 목록: created_at 추가, deleted_at 단독 인덱스 제거
    @Index(name = "idx_review_user_created",
           columnList = "user_id, created_at DESC"),

    // 업체별 리뷰 목록
    @Index(name = "idx_review_business_created",
           columnList = "business_id, created_at DESC")

    // ❌ idx_review_deleted 제거 — deleted_at IS NULL은 Filter로 수용
    // ❌ idx_review_business_rating 제거 — Planner 혼란 원인, 평점 필터 빈도 낮음
})

deleted_at을 인덱스에서 제거한 이유: deleted_at IS NULL은 전체 레코드의 90%가 해당하는 저선택도 조건입니다. 인덱스에 추가해도 탐색 범위를 의미있게 좁히지 못합니다. (user_id, created_at DESC) 인덱스로 먼저 특정 사용자 레코드를 찾은 뒤 10%를 Filter로 제거하는 오버헤드가 더 경제적입니다. 여기에 BitmapAnd 병합 비용도 사라집니다.

개선 결과

-- After: 50,000건

Index Scan using idx_review_business_created on review
  Index Cond: (business_id = '...')
  Filter: (deleted_at IS NULL)
  Rows Removed by Filter: 5000   -- 10%
  Buffers: shared hit=10088
  -- Sort 노드 없음!
Execution Time: 16.068 ms

Sort가 완전히 제거되었습니다. 디스크 spill도 없습니다.

	Before	After	개선
10,000건	7.5~8 ms (Sort 6ms)	2.846 ms	62%
50,000건	43.313 ms (Disk 6.7MB)	16.068 ms	62%

10,000건에서 Buffers가 Before(668) → After(364)로 45% 감소합니다. 50,000건은 After에서 Buffers가 증가합니다(1,155 → 10,088). Index Scan이 인덱스 순서대로 Random I/O를 하기 때문입니다. 그럼에도 Sort 제거 효과가 Random I/O 증가를 상회해 전체 성능은 62% 개선됩니다.

업체별 리뷰 조회(100만건 중 2,250건)도 확인했습니다.

-- WHERE business_id = ? AND deleted_at IS NULL ORDER BY created_at DESC LIMIT 20

Limit  (cost=0.42..84.49 rows=20 width=51) (actual time=1.697..1.698 rows=0 loops=1)
  ->  Index Scan using idx_review_business_created on review
        Index Cond: (business_id = '...')
        Filter: (deleted_at IS NULL)
        Rows Removed by Filter: 2500
        Buffers: shared hit=2505
Execution Time: 1.710 ms

---

3. 컬럼 순서 결정에서 공통으로 확인한 것

Menu와 Review를 거치며 확인된 원칙이 있습니다.

등호(=) 조건 컬럼을 인덱스 앞에 배치합니다. Menu의 (business_id, is_active, created_at DESC)에서 business_id와 is_active는 모두 등호 조건입니다. 이 둘이 앞에서 탐색 범위를 좁힌 뒤 created_at DESC로 정렬 순서를 제공합니다.

인덱스 순서와 ORDER BY 방향이 일치하면 Sort 노드가 사라집니다. Menu에서는 created_at DESC, Review에서는 created_at DESC — 인덱스에 명시한 방향과 쿼리의 ORDER BY가 같아 모두 Sort 없이 처리됩니다.

쓰지 않는 인덱스는 삭제가 답입니다. idx_review_business_rating은 평점 필터를 위해 만들었지만, 주요 쿼리 패턴과 맞지 않아 오히려 Planner를 잘못된 경로로 유도했습니다. INSERT·UPDATE 시 불필요한 인덱스 갱신 비용도 발생합니다. "혹시 필요할 수 있다"는 이유로 인덱스를 유지하는 것은 옳지 않습니다.