문제 상황
백오피스 이용 내역 조회 시 IN절을 사용하여 DB 조회를 할 필요성이 있었다.
이전에도 이야기했지만 많지 않은 데이터를 사용하고 인덱스를 탈 수 있다면 IN절도 좋은 선택이다.
이용내역 조회 시에는 10, 50, 100개의 페이징 처리를 통해 조회를 해오기 때문에 IN절로도 충분했다.
그리고 이번 같은 경우는 JOIN으로 해결할 수 없었던 것이 외부 API를 통해 가져온 값으로 내부 DB 조회를 해야 했기 때문이다.
그러나 문제는 이 내역을 엑셀로 다운로드할 수 있는 기능으로 인해서 전체 내역 조회가 필요했기 때문이었다.
전체 내역 조회로 넘어갈 경우 IN절 값으로 1000개 이상의 데이터가 들어갈 가능성이 높았다.
1000개 이상의 대용량 데이터를 다루게 될 경우 IN 절로 처리하는 경우 성능 문제가 생길 가능성이 높아 다른 방식의 처리 방안을 고민했다.
해결 방안
이로 인해 결정했던 것이 임시 테이블을 사용하는 것이었다.
WITH절을 사용하여 임시 테이블을 작성하고 이것을 JOIN 하는 방식으로 하면 EXPLAIN ANALYZE로 확인해봤을 때 1000개 미만일 경우에도 IN절과 성능 차이가 없었고, 1000개 이상일 경우에는 20% 정도 더 빨라진 것을 살펴볼 수 있었다.
PostgreSQL에서 IN절이 많은 경우에는 Seq Scan을 유발하여 성능이 저하될 가능성이 높으나, 임시테이블로 JOIN하는 경우엔 Hash Join, Index Scan 활용으로 최적화가 가능해진다.
WITH ids(id) AS (
VALUES
<foreach collection="ids" item="id" index="index" separator=",">
(#{id})
</foreach>
)
SELECT
a.*
FROM A a
INNER JOIN ids b ON a.id = b.id;여기서 중요한 점은 아이디마다 () 괄호 처리를 해야 id 값 하나당 개별 행으로 삽입된다는 점이다.
처음에 <foreach collection="ids" item="id" index="index" separator="," open="(" close=")">#{id}</foreach> 로 하나의 괄호 안에 처리되도록 하였다가 하나의 행만 생성되는 문제를 겪었다.
