인덱스를 통한 조회 성능 개선

현재 진행하고 있는 프로젝트에서, 항공권 조회 API 를 개발하던 중, 조회 쿼리에서 과도한 시간이 걸리는 것을 확인하였습니다.

조회 테이블 구조

조회 쿼리

항공권 조회시, 출발지와 도착지 및 출발 날짜를 검색 조건을 걸어주었고,
해당 내용을 포함하여 조회 쿼리에서는 다음의 조회 조건으로 작성하였습니다.
1. 출발지와 도착지
2. 출발 시간
3. 삭제 여부
4. 할인 진행 여부

SELECT
    flights_info.id,
    flights_info.departures,
    flights_info.arrivals,
    flights_info.departure_time,
    flights_info.arrival_time,
    flights_info_option.original_price,
    flights_info_option.discount_price,
    flights_info_option.available_seats,
    flights_info_option.discount_end_time
FROM
    flights_info
        JOIN flights_info_option ON flights_info_option.flights_info_id = flights_info.id
WHERE
    flights_info.departures = 'incheon'
  AND flights_info.arrivals = 'nhatrang'
  AND flights_info.departure_time BETWEEN '2024-07-15 00:00:00' AND '2024-07-15 23:59:59'
  AND flights_info_option.deleted = FALSE
  AND flights_info_option.discount_end_time > CURRENT_TIMESTAMP();

실행 결과 및 Explain / Analyze 쿼리

데이터는 총 1074만 건으로,
실행 결과는 다음과 같이 38 초가 소요되었으며, 현재 인덱스를 걸어주지 않았기 때문에 당연하게도 Full Scan 이 진행되었습니다.

쿼리 실행 결과

Explain 쿼리 실행 결과

위의 Explain 쿼리에서, type 을 살펴보면 flights_info_option 테이블은 외래키를 사용한 join 으로 ref /
flights_info 테이블은 전체 테이블 스캔이 일어나 ALL 로 결과가 출력되었습니다.
즉, rows 수 와 함께 보면, flights_info 테이블에서 1047만건의 전체 테이블 스캔이 일어나 오랜 시간이 걸린 것입니다.

Explain Analyze 쿼리 실행 결과

-> Nested loop inner join  (cost=1.1e+6 rows=582) (actual time=395..41422 rows=119 loops=1)
    -> Filter: ((flights_info.departures = 'incheon') and (flights_info.arrivals = 'nhatrang') and (flights_info.departure_time between '2024-07-15 00:00:00' and '2024-07-15 23:59:59'))  (cost=1.09e+6 rows=11633) (actual time=392..41415 rows=119 loops=1)
        -> Table scan on flights_info  (cost=1.09e+6 rows=10.5e+6) (actual time=2.24..31240 rows=10.7e+6 loops=1)
    -> Filter: ((flights_info_option.deleted = false) and (flights_info_option.discount_end_time > <cache>(now())))  (cost=1.01 rows=0.05) (actual time=0.043..0.0472 rows=1 loops=119)
        -> Index lookup on flights_info_option using FKsuc89yaueyp57opp0nuv62r15 (flights_info_id=flights_info.id)  (cost=1.01 rows=1.02) (actual time=0.0324..0.0359 rows=1 loops=119)

위 결과에서는 join 과정에서 395 - 41222 ms 가 걸리며,
전체 테이블을 스캔하는 과정에서 2.24 - 31240 ms 가 걸리고,
필터링하는데 392 - 41415 ms 가 걸리고 있습니다.

현재 문제는 전체 테이블을 스캔하는 것으로, 스캔 범위를 줄이기 위해 인덱스 적용을 고려하였습니다.

---

단일 인덱스 적용

주요 병목 지점은 flights_info 테이블이었으므로, 해당 테이블과 조건절을 보았을 때, 인덱스의 후보는 다음과 같습니다.
flights_info 테이블 - departures(출발지), arrivals(도착지), departure_time(출발 시간)

이 때, 출발지 및 도착지 칼럼은 중복 제거 후 count 로 조회하였을 때 124개의 유일한 값이 나왔고,
출발 시간은 43438개의 유일한 값이 나왔기 때문에 중복도가 낮은 출발 시간을 인덱스로 설정하여 테스트 해보았습니다.

쿼리 실행 결과

쿼리 실행 결과, 약 600 ms 로 기존 38초에 비해 약 63배 빨라진 결과를 보여주었습니다.

Explain 실행 결과

위 결과로 flights_info 테이블의 type 이 range 로, key 가 생성한 인덱스(idx_departure_time)를 사용하도록 변경된 것을 확인할 수 있었고, 스캔한 범위가 감소함에 따라 rows 도 1047만건이 아닌 5만건으로 준 것을 확인할 수 있었습니다.

Explain Analyze 실행 결과

-> Nested loop inner join  (cost=62912 rows=28.2) (actual time=315..543 rows=119 loops=1)
    -> Filter: ((flights_info.departures = 'incheon') and (flights_info.arrivals = 'nhatrang'))  (cost=62278 rows=564) (actual time=315..539 rows=119 loops=1)
        -> Index range scan on flights_info using idx_departure_time over ('2024-07-15 00:00:00' <= departure_time <= '2024-07-15 23:59:59'), with index condition: (flights_info.departure_time between '2024-07-15 00:00:00' and '2024-07-15 23:59:59')  (cost=62278 rows=56388) (actual time=313..511 rows=29512 loops=1)
    -> Filter: ((flights_info_option.deleted = false) and (flights_info_option.discount_end_time > <cache>(now())))  (cost=1.02 rows=0.05) (actual time=0.0219..0.0253 rows=1 loops=119)
        -> Index lookup on flights_info_option using FKsuc89yaueyp57opp0nuv62r15 (flights_info_id=flights_info.id)  (cost=1.02 rows=1.02) (actual time=0.0198..0.0229 rows=1 loops=119)

 

실제 실행 시간은
join 과정에서 (395 - 41222) ms -> (315 - 543) ms,
테이블 스캔 과정에서 (2.24 - 31240) ms -> (313 - 511) ms
필터링하는데 (392 - 41415) ms -> (315 - 539) ms 가 소요되어 
실행 시간이 크게 감소한 것을 확인할 수 있었습니다.

---

복합 인덱스 사용

위의 단일 인덱스 사용으로 인해 600 ms 까지 실행 시간을 줄였지만, 사실 600 ms 는 실제 서비스에서는 매우 오래걸리는 시간이라고 생각했습니다.
따라서 추가적인 성능 개선을 위해 복합 인덱스 적용을 시도해보았습니다.

이전에 인덱스의 후보였지만, 카디널리티에 의해 제외된 칼럼은 departures(출발지), arrivals(도착지) 가 있었습니다.
따라서 현재 인덱스인 출발 시간과 출발지, 도착지를 복합 인덱스로 구성하기로 했습니다.

복합 인덱스의 순서

이 때, 복합 인덱스의 순서는 출발 시간과 출발지, 도착지 순으로 구성하였습니다.

현재 쿼리에서는 출발 시간은 between 으로 범위 검색을, 출발지와 도착지는 = 검색을 하고 있습니다.
따라서 범위 검색의 칼럼(출발 시간)을 첫 번째나 두 번째로 지정할 경우, 조건에 부합하는 첫 번째 리프 노드에 도달했을 때, 이후에 순차 탐색이 불가능해 집니다.

반면, 출발 시간을 마지막 순서로 지정한다면 출발지와 도착지로 범위가 좁혀진 후, 범위 검색에 의해 첫 번째 리프 노드 접근 후 순차적으로 탐색할 수 있게 됩니다.

출발 시간 - 출발지 - 도착지 순의 복합 인덱스에서의 리프노드 상태
(순차 탐색 불가)

출발지 - 도착지 - 출발 시간 순의 복합 인덱스에서의 리프노드 상태
(순차 탐색 가능)

쿼리 실행 결과

쿼리 실행 결과, 약 40 ms 로 인덱스를 하나만 적용한 600 ms 에 비해 약 15배 빨라진 결과를 보여주었습니다.

Explain 실행 결과

key 값을 통해 생성한 복합 인덱스가 사용한 것을 확인하였고, rows 를 확인해보니 기존 단일 인덱스보다 
스캔 범위가 줄어들었음을 확인하였습니다.

Explain Analyze 실행 결과

-> Nested loop inner join  (cost=278 rows=5.95) (actual time=1.2..14.3 rows=119 loops=1)
    -> Index range scan on flights_info using idx_departures_arrivals_departure_time over (departures = 'incheon' AND arrivals = 'nhatrang' AND '2024-07-15 00:00:00' <= departure_time <= '2024-07-15 23:59:59'), with index condition: ((flights_info.departures = 'incheon') and (flights_info.arrivals = 'nhatrang') and (flights_info.departure_time between '2024-07-15 00:00:00' and '2024-07-15 23:59:59'))  (cost=144 rows=119) (actual time=0.684..6.93 rows=119 loops=1)
    -> Filter: ((flights_info_option.deleted = false) and (flights_info_option.discount_end_time > <cache>(now())))  (cost=1.02 rows=0.05) (actual time=0.0467..0.0574 rows=1 loops=119)
        -> Index lookup on flights_info_option using FKsuc89yaueyp57opp0nuv62r15 (flights_info_id=flights_info.id)  (cost=1.02 rows=1.02) (actual time=0.0413..0.0511 rows=1 loops=119)

실제 실행 시간은
join 과정에서 (1.2 - 14.3) ms -> (315 - 543) ms,
테이블 스캔 과정에서 (313 - 511) ms -> (0.684 - 6.93) ms,
필터링하는데 (315 - 539) ms -> (0.0467 - 0.0574) ms 가 소요되어 
실행 시간이 크게 감소한 것을 확인할 수 있었습니다.

---

최종 결과

초기 인덱스를 사용하지 않은 38 s 에서 복합 인덱스를 적용하여 40 ms 로 약 950 배 빨라진 결과를 보여주었습니다.