상관 서브쿼리를 개선하여 API 응답 2.5초에서 0.2초로 개선하기

서론

약 2.5초가 걸리던 대화 목록 조회 API의 응답 시간을 0.2초로 개선하며 배운 상관 서브쿼리와 Hash Join, Nested Loop Join 의 특징을 공유합니다.

문제

대화 목록 조회 API를 Postman으로 테스트한 결과, 응답 시간이 약 2.5초나 걸렸습니다.

 

원인 파악

해당 API는 DB 접근을 총 3번 하는 구조였기에 병목 지점을 빠르게 파악하기 위해 IntelliJ Profiler로 각 쿼리의 실행 시간을 측정했습니다.

IntelliJ Profiler 로 API 응답을 측정하여 나온 쿼리 별 시간

측정 결과

첫 번째 쿼리는 아래 사진의 조회 조건 5가지(검색어, 서비스, 카테고리 명, 사용자 반응, 대화일자)를 필터링해서 페이지 당 보여줄 대화 UUID 10개를 추출하는 쿼리로, 102ms 가 걸렸습니다.

대화 목록 조회 시, 필터링 조건 5가지

 

두 번째 쿼리는 아래 사진의 대화 UUID 마다 사용자 반응을 집계하는 쿼리로, 51ms 가 걸렸습니다.

대화 목록 값

 

세 번째 쿼리는 대화 UUID 마다 위 사진의 최초질의와 답변을 조회하는 쿼리로 가장 늦은 2.3초가 걸렸고, IntelliJ Profiler 를 통해 빠르게 병목지점을 파악할 수 있었습니다.

 

원인 분석

문제 쿼리 소개

세 번째 쿼리는 생각보다 단순했는데 2.3초나 걸려서 의아했습니다. 쿼리는 다음과 같습니다.

- 대화(Dialogue)와 채팅(Chat_log)은 1 : N 관계

- 대화 UUID 10개마다 공백이 아닌 채팅을 통해 최초 질의를 찾기 

select c.*, d.*
from chat_log c
    join dialogue d on c.dialogue_id = d.id
where d.id IN ( -- 대화(dialouge) UUID 10개
               '3f056f44-993c-49ce-9c35-8c64821ef270',
               ...
               '53186f20-784a-4f4b-bd0a-1e66061312eb')
      and c.seq = (
          select min(c2.seq)
          from chat_log c2
          where c2.question <> '' 
                and c2.dialogue_id = d.id -- 문제의 원인
      );

 

실행 계획 분석

사용 DB는 PostgreSQL 이며, EXPLAIN ANALYZE로 실행계획을 확인하니 쿼리가 기대한 대로 실행되지 않은 것을 확인할 수 있었습니다.

 

실행 계획에서 가장 두드러진 것은 서브 쿼리가 20번 반복되는 문제가 였습니다.

```

1~2. dialogue 10개 조회 완료

3~4. chat_log 해시 준비 완료

5~6. Hash Join 시작 → dialogue의 각 row와 chat_log 매칭 시도 → SubPlan 20번 실행

```

 

기대했던 쿼리는 Join 전에 WHERE 절에서 미리 필터링되는 것이었지만, 서브쿼리가 Hash Join의 조인 조건처럼 동작하면서 매번 외부 데이터를 참조하기 위해 불필요한 chat_log 스캔과 집계 작업을 20번 반복 수행했습니다.

 

이처럼 서브쿼리가 메인 쿼리의 d.id를 참조하여 메인 쿼리에 의존하는 것을 상관 서브쿼리라고 부릅니다.

상관 서브쿼리는 메인 쿼리에서 조회된 각 행마다 반복적으로 실행되기 때문에 성능 문제를 일으킵니다.

 

Hash Join 특징

또한, Hash Join 을 공부해보니 실행계획에 보인 Hash Join 이 제 경우에 적합한 Join 이 아님을 알게 되었습니다.

 

Hash Join 의 동작 원리는 1:N 관계에서 작은 테이블(1 테이블)을 메모리(PGA 영역, 세션끼리 공유하지 않는 고유한 메모리 영역)에 해시 테이블로 빌드하고, 큰 테이블(N 테이블)을 순차적으로 읽으며 조인합니다. 큰 테이블(N 테이블)을 순차 접근하기 때문에 Random Access 부하가 없습니다.

 

이 때, 해시 테이블을 빌드하며 Join 컬럼을 기준으로 해시 함수를 적용하기 때문에 Join 컬럼이 중복 없이 고유한 값을 가질 수록 성능에 좋고, equal join 만(범위 Join X) 가능하다는 것입니다.

 

이렇게 작은 테이블이 해시 영역(메모리)에 올라가기 때문에 올라가는 테이블의 크기가 충분히 작아야 합니다.(메모리에 Join 컬럼만 올라가면, 해당 테이블의 다른 컬럼의 값을 읽기 위해 디스크를 다시 읽어야 합니다. 그래서, 해시 영역에는 Join 컬럼만 메모리에 올라가는게 아니라, 테이블 전체가 올라갑니다). 만약, 테이블의 크기가 해시 영역의 크기를 넘어가면 디스크를 사용하기 때문에 주의해야 합니다.

 

이런 특징을 가지고 있기 때문에 해시 조인의 장점은 배치(대용량 테이블)에서 쓰면 좋은 Join 입니다. 큰 테이블(N 테이블)을 Join 할 때, 메모리에 올린 작은 테이블을 바탕으로 순차적으로 읽기 때문입니다.  

해시 조인의 단점은 실행 빈도가 높은 OLTP 환경(주문, 재고 처리, 상품 조회 등 짧은 트랜잭션)입니다. 짧은 쿼리를 위해 매번 해시 테이블을 빌드해 메모리에 올리는 일은 오버헤드를 발생시키기 때문입니다. 따라서, 실행빈도가 높은 OLTP 환경이라면 Join 이 해시 조인으로 풀리고 있진 않은지 검토할 필요가 있습니다.

 

이 특징을 바탕으로 큰 테이블(chat_log)이 해시 테이블로 빌드 된다는 점에서 Join 방식을 바꿀 필요가 있었습니다.  

 

해결 방안

먼저, 서브쿼리가 메인 쿼리에 의존하지 않도록 독립적으로 변경했습니다.

이를 통해 서브 쿼리가 Hash Join 의 조건으로 쓰이지 않게 되는 것을 기대했습니다.

select c.*, d.*
from chat_log c
    join dialogue d
    on c.dialogue_id = d.id
where (d.id, c.seq) IN (
                    select c2.dialogue_id ,min(c2.seq)
                    from lingo_chat_log c2
                    where c2.question <> '' 
                          and c2.dialogue_id IN ('3f056f44-993c-49ce-9c35-8c64821ef270',
                                                 ...
                                                 '53186f20-784a-4f4b-bd0a-1e66061312eb')
                     group by c2.dialogue_id);

 

쿼리 변경 후 실행 계획 

변경한 쿼리를 실행 계획으로 분석해보았습니다.

먼저, 조인 방식이 Hash Join에서 Nested Loop Join으로 변경되었습니다.

 

Nested Loop Join 특징

Nested Loop Join 의 동작 원리는 중첩 for문 처럼 1 : N 테이블 관계에서 outer 테이블(1 테이블)의 각 행마다 inner 테이블(N 테이블)을 탐색합니다.

for( i ... outer ) -- 1 테이블
  for ( j ... inner ) -- N 테이블

 

이런 동작 원리 때문에 Nested Loop Join 에서 inner table 의 Join 컬럼에 인덱스가 걸려있지 않으면 매우매우 비효율적입니다.

inner table 에 인덱스가 없으면 outer table 에서 고른 1개의 row 마다 inner table 을 모두 full scan 해야 되기 때문입니다.

 

또한, outer 테이블의 1건 row 마다 inner table의 모든 행을 탐색하기 때문에 대량의 테이블을 Join 하는 경우 바람직 하지 않습니다.

이런 특징 때문에 1 : N 테이블 관계에서 1 쪽이 outer 테이블로 되는 것이 일반적으로 성능에 유리한 이유 역시, outer 테이블 크기만큼 for문을 돌기 때문입니다. 예를 들어, 만약 N 테이블의 레코드가 10만개면 10만번을 도는 반면, 1 테이블의 레코드가 100개면 for문을 100번만 돌기 때문입니다.

 

본론으로 돌아와 바뀐 쿼리의 실행 계획을 보니 Nested Loop 의 outer table 인 chat_log 테이블이 필터링에 의해 25건(rows=25)이 남아 있고, inner table 인 dialogue 테이블은 pk를 활용해 인덱스 탔으며, 효율적으로 스캔 됨을 볼 수 있었습니다.

 

추가 개선할 점

1. 복합 인덱스 설정

먼저 실행계획의 "Rows Removed by Filter: 4184" 를 통해 question <> '' 조건과 dialogue_Id IN (...) 조건 때문에 불필요한 4184건의 행을 읽고 있었습니다. 그렇기에 question 과 dialogue_id 에 복합 인덱스를 걸면 불필요한 4184건의 행을 읽지 않아도 될 것이라는 기대가 있었습니다.

마침, Sort Key 로 dialogue_id 를 쓰니(복합 인덱스 특성상 두 번째 컬럼부터는 직전 컬럼을 기준으로 정렬이 되니), 복합 인덱스를 (dialouge_id, question)으로 걸면 Sort(정렬)도 실행계획에서 빠질 것 같은 기대할 수 있었습니다.

 

2. PostgreSQL 의 partial Index 설정

PostgreSQL 은 partial Index 라고 Create Index 로 Index 를 생성할 시, WHERE 조건에 해당하는 행만 인덱싱을 할 수 있는 기능이 있습니다.(https://www.postgresql.org/docs/current/indexes-partial.html)

이를 바탕으로 인덱스 저장 용량을 줄일 수 있을 것입니다.

 

하지만, 결과를 보면 충분한 성능이 이미 나오고 있었기에 현재 문제에 맞는 해결책만 적용했습니다.

 

결과

그 결과로 인덱스 추가 없이 불필요한 상관 서브쿼리를 제거하여, 2.5초 걸리던 API 응답을 0.2초로 개선 할 수 있게 되었습니다.

 

 

참고자료

- Join 수행 원리 (https://www.youtube.com/@SQL)

- PostgreSQL Partial Index (https://www.postgresql.org/docs/current/indexes-partial.html)