NestedLoop Join을 Hash Join으로 변경하여 쿼리 속도 개선

실무에서 정산 쿼리가 있었는데 쿼리 길이만 152줄(with절을 통해 cte 테이블을 만들고 이를 중첩 join 사용)이 되었다. 실제로 매우 느렸던 문제를 이론으로 접하고 있던 hashJoin을 적용함으로써 성능 개선했던 경험을 작성하고자 한다.

상황

3.5만개의 데이터를 O(n**2)으로 처리-> 12억번 조회해야한다. 실행계획을 확인하면 NestedLoop Join이 여러번 사용되었다.

대량의 데이터는 NestedLoop Join(O(N*M)보다 Hash Join(O(N+M) 혹은 Sort Merge JoinO(N log N + M log M)을 사용하면 개선 여지가 있다. 

Hash Join

• Build Phase:
  • 조인 대상 테이블 중 작은 테이블(보통 메모리에 올릴 수 있는 쪽)을 기준으로 해시 테이블을 생성 (조인 키 기준)
• Probe Phase
  • 큰 테이블의 각 행을 순회하면서, 조인 키를 해시 테이블에 탐색하여 매칭되는 값 찾음.

• 해시의 특성상 동등(=) 조인 조건에서만 사용 가능하다. 
• 단점으로는 CPU사용률이 높아진다.

Sort Merge Join

• 랜덤 액세스가 많을때 적용하기 좋다.
• 정렬된 테이블을 조인할때 성능이 더 좋다.

사용 방법

적용법은 간단하다. Postgresql에서 아래처럼 nestloop 옵션을 꺼줍니다. (해당 세션에만 적용됩니다.)

show enable_nestloop;

SET enable_nestloop = OFF;

SET enable_nestloop = ON;

 

결과

규모가 큰 데이터들에 대해서는 2시간 가까이 걸리는 쿼리가 3분만에 나오기도 한다. 하지만 규모가 작은 데이터에 Hash Join을 적용하면 오히려 느려지는 경우를 확인할 수 있었다.