DB Optimization

DB 성능 최적화

이 문서에서 다루는 것

현재 문서의 의도는 "어떻게 최적화했는가"의 결론이 아니라 "무엇을 발견하고, 왜 그 결정을 내렸는가"의 과정입니다. 실제 EXPLAIN ANALYZE 결과와 트레이드오프 판단 근거를 그대로 남깁니다.

---

1. 측정 도구 구성

세 도구를 목적에 따라 구분했습니다.

WAS 콘솔 로그 (spring.jpa.show-sql=true): Spring Boot 실행 중 터미널에 출력되는 SQL 로그입니다. N+1처럼 예상보다 많은 쿼리가 반복 실행되는 것을 처음 발견할 때 사용했습니다. 별도 설정 없이 즉시 확인할 수 있는 가장 빠른 수단입니다.

EXPLAIN ANALYZE: "쿼리가 DB 내부에서 어떻게 실행되는가"를 보여줍니다. Seq Scan(Full Scan)인지 Index Scan인지, Sort 노드가 발생하는지, 어디서 비용이 집중되는지 확인합니다. BEGIN ~ ROLLBACK 안에서 실행해 실제 데이터 변경 없이 측정합니다.

P6Spy: SQL 실행 이력을 파일로 기록합니다. Grafana + Prometheus와 연계해 시간 흐름에 따른 쿼리 패턴을 리포트 형태로 남깁니다. 개발 단계의 임시 콘솔 로그와 달리 운영 환경 모니터링 용도입니다.

---

2. N+1 문제: Reservation 목록 조회

발견 (WAS 콘솔 로그)

예약 목록 API를 호출하면 단건 조회 예약 수만큼 추가 SELECT가 터미널에 출력됐습니다. Reservation 10건을 조회하면 Business 10번, Menu 10번, BusinessCategory 10번, 총 30번의 추가 쿼리가 연쇄 발생했습니다.

// Before — 기본 findAll 이후 연관 엔티티 접근마다 쿼리 발생
Page<Reservation> reservations = reservationRepository.findAll(pageable);  // 쿼리 1번

for (Reservation r : reservations) {
    r.getBusiness().getBusinessName();   // Business 쿼리 N번 (LAZY 로딩)
    r.getMenu().getServiceName();        // Menu 쿼리 N번
    r.getMenu().getBusinessCategory();   // BusinessCategory 쿼리 N번
}
// 예약 1,000건 조회 → 1 + 1,000 + 1,000 + 1,000 = 3,001번
// 예약 10건 단순 케이스도 → 1 + 10 + 10 + 10 = 31번

JPA의 기본 연관관계 전략은 FetchType.LAZY입니다. Reservation 엔티티를 로드해도 Business, Menu는 프록시 상태로 대기하다가 실제 필드에 접근하는 순간 각자 SELECT를 실행합니다.

해결: QueryDSL fetchJoin으로 단일 쿼리

// After — 연관 엔티티를 JOIN으로 한 번에 로드
List<Reservation> reservations = queryFactory
    .selectFrom(reservation)
    .join(reservation.business, business).fetchJoin()        // Business 함께 로드
    .join(reservation.menu, menu).fetchJoin()                // Menu 함께 로드
    .join(menu.businessCategory, businessCategory).fetchJoin() // BusinessCategory 함께 로드
    .where(builder)
    .offset(pageable.getOffset())
    .limit(pageable.getPageSize())
    .orderBy(getOrderSpecifiers(pageable.getSort()))
    .fetch();
// 예약 N건 조회 → 쿼리 1번 (JOIN으로 모두 포함)

fetchJoin()은 일반 JOIN과 다릅니다. 일반 JOIN은 조건 필터 용도로만 쓰고 연관 엔티티를 영속성 컨텍스트에 로드하지 않습니다. fetchJoin()은 JOIN 결과를 연관 엔티티까지 함께 영속성 컨텍스트에 올리므로, 이후 r.getBusiness() 접근 시 추가 쿼리가 발생하지 않습니다.

	Before	After
쿼리 횟수 (예약 1,000건)	1 + 3,000 = 3,001번	1번
쿼리 횟수 (예약 10건)	31번	1번

고객용 findMyReservationsWithFilters, 업체용 findBusinessReservationsWithFilters 두 쿼리 메서드 모두 동일한 패턴으로 적용했습니다. 페이징 COUNT 쿼리는 연관 엔티티 로드가 필요 없으므로 selectFrom(reservation).where(builder).fetchOne()으로 분리해 불필요한 JOIN을 하지 않습니다.

---

3. N+1 문제: Menu 삭제 시 BookingSlot 처리

발견 (WAS 콘솔 로그)

Menu 삭제 API를 호출했을 때 터미널에 SELECT 쿼리가 루프로 반복 출력되었습니다. 각 슬롯마다 "예약이 있는지" 확인하는 쿼리가 슬롯 수만큼 실행되고 있었습니다.

// Before — 루프 안에서 쿼리 반복
List<BookingSlot> slots = bookingSlotRepository.findByMenuId(menuId);  // 쿼리 1번

for (BookingSlot slot : slots) {
    boolean hasReservation = reservationRepository.existsByBookingSlot(slot); // 슬롯마다 쿼리
    if (!hasReservation) {
        bookingSlotRepository.delete(slot);  // 슬롯마다 DELETE
    }
}
// 슬롯 100개 → 쿼리 101번, 약 2초

해결: 3번 고정 쿼리

// After — 집합 연산으로 재설계
@Transactional(propagation = Propagation.MANDATORY)
public int deleteSlotsForMenu(UUID businessId, UUID menuId) {

    // 쿼리 1: 슬롯 ID 목록 전체 조회
    List<BookingSlot> allSlots = bookingSlotRepository
            .findByBusinessIdAndMenuId(businessId, menuId);
    if (allSlots.isEmpty()) return 0;

    // 쿼리 2: 예약이 있는 슬롯 ID를 IN 절로 한 번에 조회
    Set<UUID> occupiedSlotIds = new HashSet<>(
            reservationRepository.findSlotIdsWithActiveReservations(
                allSlots.stream().map(BookingSlot::getId).toList()
            )
    );

    // 메모리 필터링 (DB I/O 없음)
    List<BookingSlot> deletableSlots = allSlots.stream()
            .filter(slot -> !occupiedSlotIds.contains(slot.getId()))
            .toList();

    // 쿼리 3: 삭제 가능한 슬롯 일괄 삭제
    bookingSlotRepository.deleteAll(deletableSlots);
    return deletableSlots.size();
}

	Before	After
쿼리 횟수	슬롯 100개 → 101번	3번 (고정)
실행 시간	약 2초	50ms 이하

설계 원칙: Reservation은 예약 시점의 스냅샷이므로 영구 보존합니다. 예약 레코드가 하나라도 있는 슬롯은 삭제하지 않으며, FK 제약조건이 DB 레벨에서도 이를 보장합니다.

---

4. BookingSlot 인덱스 설계

3-1. 초안 EXPLAIN에서 문제 발견

소량 데이터(300건)로 먼저 EXPLAIN을 실행했습니다.

-- 날짜별 슬롯 조회: WHERE business_id = ? AND slot_date = ? AND is_available = true
-- 인덱스 없음, 데이터 300건

Sort  (cost=24.41..24.41 rows=1 width=85) (actual time=0.085..0.087 rows=10 loops=1)
  Sort Key: start_time
  Sort Method: quicksort  Memory: 26kB
  ->  Seq Scan on booking_slot  (cost=0.00..24.40 rows=1 width=85) (actual time=0.027..0.076 rows=10 loops=1)
        Filter: (is_available AND (business_id = '...') AND (slot_date = ...))
        Rows Removed by Filter: 290
Planning Time: 0.091 ms
Execution Time: 0.103 ms

세 가지 문제를 확인했습니다.

Seq Scan + Filter: 300건 전체를 읽은 후에 조건을 검사합니다. 10건이 필요한데 290건을 읽고 버렸습니다(낭비율 97%). 데이터가 늘어날수록 이 낭비가 선형으로 증가합니다.

Sort 노드 발생: ORDER BY start_time을 처리하기 위해 quicksort 26KB를 별도로 사용합니다. 인덱스 컬럼 순서와 정렬 순서가 일치하면 이 노드 자체가 사라집니다.

rows=1 예상 vs 10 실제: Planner 통계가 오래되어 예측이 크게 빗나갔습니다. ANALYZE booking_slot 실행으로 통계를 갱신했습니다.

---

3-2. 인덱스 추가 후 소량 검증

-- 인덱스 추가 후, 동일 쿼리 (300건)

Index Scan using idx_booking_slot_business_date_time on booking_slot
  (cost=0.28..8.30 rows=1 width=85) (actual time=0.015..0.022 rows=10 loops=1)
  Index Cond: ((business_id = '...') AND (slot_date = ...))
  Filter: is_available
  Buffers: shared hit=6
Planning Time: 0.349 ms
Execution Time: 0.098 ms

Filter가 Index Cond로 전환되었고 Sort 노드가 사라졌습니다. is_available만 Filter로 남아 있는데, 이는 인덱스에 포함하지 않기로 결정했습니다. 이유: 대부분의 슬롯이 is_available = true 상태여서 선택도가 낮고, Filter로 처리해도 오버헤드가 미미합니다. 추가 시 INSERT마다 인덱스 갱신 비용이 늘어나는 것이 더 불리합니다.

---

3-3. 메뉴별 조회에서 두 번째 문제 발견

기간별 조회가 Index Scan으로 잘 동작하는 것을 확인한 뒤, 메뉴별 조회를 실행했습니다.

-- 메뉴별 슬롯 조회: WHERE menu_id = ? AND slot_date >= ? AND is_available = true
-- 인덱스: (business_id, slot_date, start_time), 데이터 300건

Sort  (cost=20.35..20.36 rows=1 width=85) (actual time=0.276..0.288 rows=300 loops=1)
  Sort Key: slot_date, start_time
  Sort Method: quicksort  Memory: 60kB
  ->  Seq Scan on booking_slot
        Filter: (is_available AND (menu_id = '...') AND (slot_date >= CURRENT_DATE))
Execution Time: 0.320 ms

다시 Seq Scan + Sort입니다. 원인은 B-tree 인덱스의 Leading 컬럼 규칙입니다. (business_id, slot_date, start_time) 인덱스는 business_id 조건 없이는 사용이 불가합니다. 메뉴별 조회는 menu_id가 필터 기준이므로 기존 인덱스가 작동하지 않습니다.

인덱스를 두 개로 분리하기로 결정했습니다.

@Table(name = "booking_slot", indexes = {
    // 패턴 1: 업체 기반 조회 (달력 화면)
    // WHERE business_id = ? AND slot_date = ? or BETWEEN
    // ORDER BY slot_date, start_time
    @Index(name = "idx_booking_slot_business_date_time",
           columnList = "business_id, slot_date, start_time"),

    // 패턴 2: 메뉴 기반 조회 (예약 가능 시간 조회)
    // WHERE menu_id = ? AND slot_date >= ?
    // ORDER BY slot_date, start_time
    @Index(name = "idx_booking_slot_menu_date_time",
           columnList = "menu_id, slot_date, start_time")
})

단일 인덱스로 통합이 불가한 이유: (business_id, menu_id, slot_date, start_time) 같은 구성은 business_id 없는 쿼리에서 여전히 사용 불가입니다. B-tree 인덱스는 Leading 컬럼부터 순서대로 매칭해야 합니다. 두 쿼리 패턴의 첫 번째 조건이 다르므로 분리가 맞습니다.

트레이드오프: INSERT 시 인덱스 2개를 갱신해야 하므로 쓰기 비용이 약 10~20% 증가합니다. BookingSlot은 슬롯 생성 시 일괄 INSERT하고 이후 주로 읽기만 발생하는 패턴이므로, 읽기 성능 개선이 이 비용을 충분히 정당화합니다.

인덱스 추가 후 소량 검증 (300건)

-- idx_booking_slot_menu_date_time 추가 후

Index Scan using idx_booking_slot_menu_date_time on booking_slot
  (cost=0.28..8.30 rows=1 width=85) (actual time=0.026..0.307 rows=300 loops=1)
  Index Cond: ((menu_id = '...') AND (slot_date >= CURRENT_DATE))
  Filter: is_available
  Buffers: shared hit=609
Execution Time: 0.373 ms

Seq Scan이 Index Scan으로 전환되었습니다. 그런데 같은 쿼리를 여러 번 실행하자 실행 계획이 일정하지 않은 현상이 발견되었습니다.

플랜 불안정성 관찰 (동일 쿼리 5회 실행)

실행	플랜	Buffers	Execution Time
1	Index Scan	609	0.278 ms
2	Bitmap + Sort	26	0.842 ms
3	Bitmap + Sort	24	0.244 ms
4	Bitmap + Sort	28	0.557 ms
5	Bitmap + Sort	32	0.473 ms

같은 인덱스를 사용하지만 Planner가 Index Scan과 Bitmap Index Scan을 번갈아 선택합니다. Bitmap Scan 선택 시 Sort 노드가 추가로 발생합니다.

원인은 메모리 상태입니다. Index Scan은 인덱스 순서대로 읽어 Sort가 불필요하지만 Buffers를 609페이지 사용합니다. Bitmap Scan은 heap 물리 순서로 읽어 Buffers를 24~32페이지만 사용하지만 정렬 순서를 보장하지 않아 Sort가 추가됩니다. Planner가 메모리 여유에 따라 두 플랜을 오갑니다.

성능 범위는 0.244~0.842ms입니다. 목표(< 100ms) 대비 100배 이상 여유가 있어 플랜 불안정성 자체는 문제가 아닙니다. 오히려 Planner가 메모리 상태에 따라 적응적으로 선택하고 있다고 볼 수 있습니다.

---

3-4. 빅스케일(300만건) 검증 및 LIMIT 효과

테스트 데이터 규모 산정:

BookingSlot = Reservation(100만) ÷ 예약률(35%) ≈ 285만 → 300만
구성: 카테고리 15 × 메뉴 25 × 스태프 6 × 기간 90일 × 하루 14슬롯 = 2,835,000건

소량에서 확인한 인덱스가 실제 서비스 규모에서도 동작하는지 검증했습니다.

날짜별 조회 (300만건 중 31,500건)

-- WHERE business_id = ? AND slot_date = CURRENT_DATE + 7 days

Index Scan using idx_booking_slot_business_date_time on booking_slot
  (cost=0.43..40748.90 rows=28968 width=85) (actual time=0.059..6.394 rows=31500 loops=1)
  Index Cond: ((business_id = '...') AND (slot_date = ...))
  Filter: booking_slot.is_available
  Buffers: shared hit=565 read=2
Planning Time: 1.686 ms
Execution Time: 7.256 ms

Index Scan이 유지됩니다. 반환 행이 31,500건으로 많아 Execution Time이 7.256ms까지 올라갔지만, 300만건 테이블에서 Index Cond로만 처리되고 있습니다.

기간별 조회 (300만건 중 252,000건 → Index Scan 전환)

-- WHERE business_id = ? AND slot_date BETWEEN today AND today+7

Index Scan using idx_booking_slot_business_date_time on booking_slot
  (cost=0.44..78210.02 rows=232768 width=85) (actual time=0.022..53.318 rows=252000 loops=1)
  Index Cond: (business_id = '...' AND slot_date >= CURRENT_DATE AND slot_date <= today+7)
  Filter: booking_slot.is_available
  Buffers: shared hit=4450
Execution Time: 60.583 ms

252,000건을 반환하는 쿼리라 60ms가 나왔습니다. Planner가 반환 행이 많을 때는 Bitmap Heap Scan을 선택하기도 했습니다.

-- 다른 날짜로 반복 조회 시 Bitmap 선택

Bitmap Heap Scan on booking_slot
  Heap Blocks: exact=534
  Buffers: shared hit=563 read=1
  ->  Bitmap Index Scan on idx_booking_slot_business_date_time
        Index Cond: (business_id = '...' AND slot_date = ...)
        Buffers: shared hit=29 read=1
Execution Time: 5.587 ms

같은 인덱스를 사용하지만 반환 행 수와 캐시 상태에 따라 Index Scan과 Bitmap Index Scan을 오가는 것을 확인했습니다. 중요한 것은 두 경우 모두 Seq Scan이 발생하지 않았다는 점입니다.

메뉴별 조회 (300만건 중 2,520건)

-- WHERE menu_id = ? AND slot_date >= CURRENT_DATE (idx_booking_slot_menu_date_time 추가 후)

Sort  (cost=8263.68..8269.47 rows=2315 width=85) (actual time=5.254..5.352 rows=2520 loops=1)
  Sort Key: slot_date, start_time
  Sort Method: quicksort  Memory: 392kB
  ->  Bitmap Heap Scan on booking_slot
        Recheck Cond: (menu_id = '...' AND slot_date >= CURRENT_DATE)
        Filter: booking_slot.is_available
        ->  Bitmap Index Scan on idx_booking_slot_menu_date_time
              Index Cond: (menu_id = '...' AND slot_date >= CURRENT_DATE)
              Buffers: shared hit=7
Execution Time: 5.483 ms

Bitmap Index Scan으로 전환됩니다. Sort 노드가 다시 등장했는데, Bitmap Scan이 물리적 저장 순서(heap 순서)로 읽기 때문에 인덱스 정렬 순서를 보장하지 않아서입니다. Index Scan과 달리 Bitmap Scan은 Sort가 발생할 수 있습니다.

향후 조회 — LIMIT + Early Termination (300만건)

-- WHERE business_id = ? AND slot_date >= CURRENT_DATE AND is_available = true
-- ORDER BY slot_date, start_time LIMIT 50

Limit  (cost=0.43..6.40 rows=50 width=85) (actual time=0.050..0.063 rows=50 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using idx_booking_slot_business_date_time on booking_slot
        Index Cond: ((business_id = '...') AND (slot_date >= CURRENT_DATE))
        Filter: booking_slot.is_available
        Buffers: shared hit=5
Planning Time: 1.111 ms
Execution Time: 0.097 ms

300만건 테이블에서 0.097ms입니다. Limit 노드가 50건을 찾는 순간 Index Scan을 중단합니다 (Early Termination). 인덱스가 이미 slot_date, start_time 순서로 정렬되어 있으므로 앞에서부터 50건을 찾으면 뒷부분은 읽을 필요가 없습니다.

LIMIT을 제거하고 동일 조건으로 COUNT를 실행하면 차이가 극명합니다.

-- LIMIT 없이 전체 카운트

Finalize Aggregate ... (actual time=1043.910..1055.074 rows=1 loops=1)
  Buffers: shared hit=15402 read=23748
  ->  Gather ...
        ->  Parallel Seq Scan on booking_slot
              Filter: (is_available AND (business_id = '...') AND (slot_date >= CURRENT_DATE))
              Rows Removed by Filter: 598500
Execution Time: 1055.186 ms

Planner가 LIMIT 없이 대량 반환이 예상되는 경우 Parallel Seq Scan을 선택합니다. 결과는 1055ms — LIMIT 50 쿼리(0.097ms)와 약 10,000배 차이입니다. 동일 인덱스가 있어도 LIMIT 유무에 따라 실행 전략이 완전히 달라집니다.

빅스케일 요약

조회 패턴	결과 행	인덱스	Sort	실행 시간
날짜별 (= 조건)	31,500건	business	없음	7.256 ms
기간별 (BETWEEN)	252,000건	business	없음	5.587~60.583 ms
메뉴별 (>= 조건)	2,520건	menu	Bitmap시 발생	5.483 ms
향후 (>= + LIMIT 50)	50건	business	없음	0.097 ms

---

5. Reservation 인덱스: status 추가 여부 판단

초안 EXPLAIN

-- WHERE business_id = ? AND status = 'PENDING' ORDER BY reservation_date DESC
-- 데이터 500건

Index Scan Backward using idx_reservation_business_date_time on reservation
  Index Cond: (business_id = '...')
  Filter: ((status)::text = 'PENDING'::text)
  Rows Removed by Filter: 40
  Buffers: shared hit=1004
Planning Time: 0.195 ms
Execution Time: 0.755 ms

status가 Filter로 처리되어 40건을 버립니다. 인덱스에 추가하면 Filter를 제거할 수 있습니다.

추가 여부 검토

API 패턴을 분석했습니다.

-- 패턴 1: 특정 상태 조회 (예: PENDING만)
WHERE business_id = ? AND status = 'PENDING'

-- 패턴 2: 여러 상태 IN 조건
WHERE business_id = ? AND status IN ('PENDING', 'CONFIRMED')

-- 패턴 3: 상태 무관 전체 조회
WHERE business_id = ?

항목	3컬럼 현재	4컬럼 status 추가
단일 상태 조회	0.35ms (Filter 40건)	0.33ms (6% 개선)
IN 조건 조회	0.35ms	0.45ms (29% 저하, Sort 발생)
전체 조회	0.30ms	0.32ms (7% 저하)
INSERT 성능	기준	~10% 저하
UPDATE status 시	빠름	인덱스 재구성 필요
인덱스 크기	기준	71% 증가 (varchar 추가)

IN 조건에서 오히려 느려지는 이유: status IN ('PENDING', 'CONFIRMED')는 인덱스를 PENDING 범위, CONFIRMED 범위 각각 스캔한 뒤 병합합니다. Bitmap Index Scan + Bitmap OR 연산이 발생하며, 이 과정에서 인덱스 정렬 순서가 깨져 Sort 노드가 추가됩니다.

status 변경 빈도 문제: Reservation 상태는 서비스 생애주기 전반에 걸쳐 변경됩니다. PENDING → CONFIRMED → COMPLETED 또는 PENDING → CANCELLED 등 여러 전이가 발생하며, 상태 변경마다 4컬럼 인덱스를 재구성해야 합니다.

결론: 0.04ms(Filter 40건 비용)를 아끼기 위해 IN 조건 성능 저하, 쓰기 비용 증가, 인덱스 크기 71% 증가를 감수하는 것은 이득이 없습니다. 실제 API가 IN 조건으로 여러 상태를 동시에 조회하는 경우를 지원해야 하므로 현재 인덱스를 유지하기로 결정했습니다.

@Table(name = "reservation", indexes = {
    // status 인덱스 미추가 결정
    // 이유: IN 조건 성능 저하, 상태 전이 빈번, 0.04ms 절감으로 정당화 불가
    @Index(name = "idx_reservation_customer_date_time",
           columnList = "customer_id, reservation_date DESC, reservation_time DESC"),
    @Index(name = "idx_reservation_business_date_time",
           columnList = "business_id, reservation_date DESC, reservation_time DESC")
})

DESC 명시: ORDER BY reservation_date DESC와 인덱스 방향이 일치하면 Index Scan Backward로 역순 스캔만으로 정렬이 완료됩니다. 별도 Sort 노드가 생기지 않습니다.

빅스케일(100만건) 검증

-- 고객 예약 목록: WHERE customer_id = ? ORDER BY reservation_date DESC (100만건)

Sort  (cost=389.87..390.12 rows=100 width=182) (actual time=0.308..0.314 rows=100 loops=1)
  Sort Key: reservation_date DESC, reservation_time DESC
  Sort Method: quicksort  Memory: 41kB
  Buffers: shared hit=103
  ->  Bitmap Heap Scan on reservation
        Recheck Cond: (customer_id = '...')
        Heap Blocks: exact=100
        Buffers: shared hit=103
        ->  Bitmap Index Scan on idx_reservation_customer_date_time
              Index Cond: (customer_id = '...')
              Buffers: shared hit=3
Execution Time: 0.336 ms

Bitmap Scan + Sort 조합이 나왔습니다. 소량 초안에서는 Index Scan Backward로 Sort가 없었는데, 100만건 규모에서 Planner가 Bitmap으로 전환하면서 Sort가 추가되었습니다.

Sort가 발생했음에도 0.336ms입니다. Bitmap Scan이 헤드의 heap 접근을 최소화한 덕분에 전체 비용이 낮게 유지됩니다. Sort 메모리도 41kB로 작습니다.

범위 조건 쿼리에서는 Index Scan이 선택됩니다.

-- 최근 30일 예약: WHERE customer_id = ? AND reservation_date >= CURRENT_DATE - 30

Limit  (cost=0.43..16.49 rows=3 width=21) (actual time=0.014..0.024 rows=10 loops=1)
  Buffers: shared hit=13
  ->  Index Scan using idx_reservation_customer_date_time on reservation
        Index Cond: ((customer_id = '...') AND (reservation_date >= CURRENT_DATE - 30))
        Buffers: shared hit=13
Execution Time: 0.038 ms

날짜 범위를 추가하면 인덱스 탐색 구간 자체가 줄어 Index Scan이 선택되고 0.038ms가 됩니다. 업체측 PENDING 예약 조회(빅스케일)도 확인했습니다.

-- 업체 PENDING 예약 목록: WHERE business_id = ? AND status = 'PENDING'

Bitmap Heap Scan on reservation
  Recheck Cond: (business_id = '...')
  Filter: ((status)::text = 'PENDING'::text)
  Heap Blocks: exact=2500
  Buffers: shared hit=2506
  ->  Bitmap Index Scan on idx_reservation_business_date_time
        Index Cond: (business_id = '...')
        Buffers: shared hit=6
Execution Time: 5.646 ms

status는 소량과 동일하게 Filter로 처리됩니다. 100만건 규모에서도 Bitmap Index Scan + Filter 구조가 유지되며, 5.646ms는 이 조건(업체의 모든 예약 중 PENDING 필터)에서 합리적인 수치입니다.

---

6. 컬럼 순서 결정 원칙 요약

위 과정에서 확인한 원칙입니다.

1순위: 등호(=) 조건 컬럼  →  탐색 시작점, Leading 컬럼
2순위: 범위(BETWEEN/>=) 조건 →  1순위로 좁힌 구간에서 범위 필터
3순위: ORDER BY 컬럼      →  인덱스 순서와 일치하면 Sort 노드 제거

※ Leading 컬럼 없이는 인덱스 사용 불가
※ Bitmap Scan 선택 시 Sort 노드는 발생할 수 있음

---

7. SQL Explain 시스템

인덱스를 올바르게 설계하려면 실제 실행 계획을 반복적으로 측정해야 합니다. 문제는 측정 과정 자체의 마찰이 높으면 측정을 건너뛰게 된다는 것입니다. EXPLAIN ANALYZE 대상 쿼리를 매번 손으로 붙여넣고, 로그에서 직접 찾아 정리하는 작업이 반복될수록 측정 빈도가 줄어듭니다.

이 마찰을 줄이기 위해 "측정 → 추출 → 분석" 세 단계를 도구로 자동화했습니다.

7-1. 도구 구성

scripts/query-logging/
├── explain-analyze.ps1   ← P6Spy 로그에서 SQL 자동 추출
├── config.json           ← 추출 규칙 설정 (코드 수정 없이 변경 가능)
├── extracted-queries.txt ← 추출 결과 (EXPLAIN ANALYZE 바로 실행 가능)
└── Readme.md

세 단계 워크플로우:

① 측정: Integration Test 실행
   ./gradlew test --tests "*IntegrationTest"
   → P6Spy가 실행된 SQL 전체를 logs/dev/application.log에 기록

② 추출: PowerShell 스크립트 실행
   cd scripts/query-logging
   .\explain-analyze.ps1
   → application.log에서 SQL만 파싱 → extracted-queries.txt 생성

③ 분석: IntelliJ / DBeaver에서 EXPLAIN ANALYZE 실행
   extracted-queries.txt의 각 쿼리 앞에 EXPLAIN ANALYZE 붙여 실행
   → Query Explain Report Template으로 Claude에게 분석 요청

7-2. config.json 기반 설정 분리

{
  "extraction": {
    "sqlPatterns": [
      { "name": "SELECT", "regex": "SELECT\\s+.*?FROM", "enabled": true },
      { "name": "INSERT", "regex": "INSERT\\s+INTO",    "enabled": true }
    ],
    "cleanupRules": [
      { "description": "P6Spy prefix 제거",
        "pattern": "^.*?\\|\\s*statement\\s*\\|", "replace": "" },
      { "description": "Hibernate prefix 제거",
        "pattern": "^.*?Hibernate:\\s*",           "replace": "" }
    ]
  },
  "processing": {
    "removeDuplicates": true
  },
  "output": {
    "format": {
      "includeQueryNumber": true,
      "includeQueryType": true
    }
  }
}

스크립트 코드를 건드리지 않고 config만 수정해 SQL 패턴 탐지 규칙 추가, 중복 제거 on/off, 출력 형식 변경이 가능합니다. 새로운 로그 포맷이 추가됐을 때도 cleanupRules에 한 줄 추가로 대응합니다.

7-3. Query Explain Report Template

EXPLAIN ANALYZE 결과를 Claude에게 분석시키는 프롬프트 템플릿을 Query_Explain_Report_Template.md로 관리합니다.

핵심 원칙:
- EXPLAIN 결과 각 줄마다 바로 아래에 해석 (장황한 표 금지)
- 문제점 3~5개만 명확히 식별
- 개선안은 실행 가능한 코드(@Index 어노테이션)로 제시

실제 사용 예시:

[사용자] EXPLAIN ANALYZE 결과를 붙여넣고 템플릿 요청
  →  Seq Scan 발생, Rows Removed 비율, Sort 노드 발생 여부 식별
  →  인덱스 컬럼 순서 제안 + @Index 코드 제시
  →  예상 Before/After 시간 비교 테이블

[사용자] 인덱스 적용 후 재측정 결과를 다시 붙여넣기
  →  개선 확인 또는 추가 튜닝 포인트 도출

이 문서 3~5절의 인덱스 설계 과정 — Seq Scan 발견, 소량 검증, 빅스케일 검증, 플랜 불안정성 분석 — 은 모두 이 워크플로우를 반복 실행한 결과입니다.