개요
종종 상황에 따라 서브쿼리를 변환해야 하는 일이 있다.
예를 들어서, QueryDSL-JPA를 사용하면, From절에 서브쿼리를 지원하지 않기 때문에 Where절의 서브쿼리로 변환해야 한다.
하지만 서브쿼리의 동작원리를 잘 알지 못하면, 성능이 급격히 안 좋아질 수 있다.
그래서 이번글에서는 서브쿼리를 변환하는 방법과 변환했을 때의 성능적인 차이에 대해서 정리해보려고 한다.
필수 사전 지식
이저 본격적으로 서브쿼리를 변환해 보자.
From 절 서브쿼리 <-> Where 절 서브쿼리
테이블 세팅
create table team
(
team_id bigint,
team_name varchar(100),
created_at timestamp with time zone
);
create table member
(
member_id bigint,
member_name varchar(100),
team_id integer,
join_date timestamp with time zone,
use_yn boolean default false
);
데이터 세팅
아래와 같이 team 테이블에는 6개의 데이터, member 테이블에는 20개의 데이터를 넣었다.
데이터의 특징은 아래와 같다.
team_id가 100인 Team Empty에 속한 멤버는 없다.
team_id가 5인 Team Epsilon에 속한 멤버들의 use_yn값은 모두 false이다.
INSERT INTO team (team_id, team_name, created_at)
VALUES (1, 'Team Alpha', CURRENT_TIMESTAMP),
(2, 'Team Beta', CURRENT_TIMESTAMP),
(3, 'Team Gamma', CURRENT_TIMESTAMP),
(4, 'Team Delta', CURRENT_TIMESTAMP),
(5, 'Team Epsilon', CURRENT_TIMESTAMP),
(100, 'Team Empty', CURRENT_TIMESTAMP);
INSERT INTO member (member_id, member_name, team_id, join_date, use_yn)
VALUES (1, 'Member 1', 1, CURRENT_TIMESTAMP, false),
(11, 'Member 11', 1, CURRENT_TIMESTAMP, false),
(6, 'Member 6', 1, CURRENT_TIMESTAMP, true),
(16, 'Member 16', 1, CURRENT_TIMESTAMP, true),
(2, 'Member 2', 2, CURRENT_TIMESTAMP, true),
(7, 'Member 7', 2, CURRENT_TIMESTAMP, false),
(12, 'Member 12', 2, CURRENT_TIMESTAMP, true),
(17, 'Member 17', 2, CURRENT_TIMESTAMP, false),
(3, 'Member 3', 3, CURRENT_TIMESTAMP, false),
(8, 'Member 8', 3, CURRENT_TIMESTAMP, true),
(13, 'Member 13', 3, CURRENT_TIMESTAMP, false),
(18, 'Member 18', 3, CURRENT_TIMESTAMP, true),
(4, 'Member 4', 4, CURRENT_TIMESTAMP, true),
(9, 'Member 9', 4, CURRENT_TIMESTAMP, false),
(14, 'Member 14', 4, CURRENT_TIMESTAMP, true),
(19, 'Member 19', 4, CURRENT_TIMESTAMP, false),
(5, 'Member 5', 5, CURRENT_TIMESTAMP, false),
(10, 'Member 10', 5, CURRENT_TIMESTAMP, false),
(15, 'Member 15', 5, CURRENT_TIMESTAMP, false),
(20, 'Member 20', 5, CURRENT_TIMESTAMP, false);
INNER JOIN + (Group By)를 포함한 서브쿼리
먼저 From절에 INNER JOIN + (Group By)를 포함한 서브쿼리를 가진 쿼리를 살펴보자.
위와 같이 Group By절을 포함한 서브쿼리와 inner join 예시를 들어보자.
서브쿼리에서 5건이 선택 됐고, inner join 하고 있기 때문에 최종결과가 5건인 것을 확인할 수 있다.
이제, 이 쿼리를 where 절의 서브쿼리로 변환해 보자.
위와 같이 From절에 있는 서브쿼리를 Where 절로 옮겼다. 또한, m.member_id 정보가 필요하기 때문에 추가적으로 member 테이블과 INNER JOIN을 했다.
select절에서 m.member_id를 가져와야 하기 때문에, member 테이블과 조인은 어쩔 수 없다. 대신 조인한 후에 결과를 In 조건으로 필터링하는 방식이다.
결과를 보면 From절의 서브쿼리와 Where절의 서브쿼리의 결과가 같다.
INNER JOIN + (Where + Group By)를 포함한 서브쿼리
다음으로는 From절에 INNER JOIN + (Where + Group By)를 포함한 서브쿼리를 가진 쿼리를 살펴보자.
위 사진에서 우측상단의 쿼리결과를 보면, where 절에서 use_yn 값이 true 인 것만 조회하기 때문에 use_yn값이 전부 false인 team_id(5)인 team 데이터는 빠지게 된다.
그 결과와 team 테이블이 INNER JOIN을 하기 때문에 당연히 조인 결과에서도 team_id(5)인 team 데이터는 빠지게 된다.
이제, 이 쿼리를 where 절의 서브쿼리로 변환해 보자.
이번에도 쿼리결과가 같은 것을 확인할 수 있다.
정리해 보면, INNER JOIN에서는 From절의 서브쿼리를 Where절로 바꿔도 쿼리의 결과는 같다. (성능적으로는 Where절의 서브쿼리가 더 안 좋을 가능성이 높다.)
LEFT JOIN + (Group By)를 포함한 서브쿼리
다음으로는 From절에 LEFT JOIN + (Where + Group By)를 포함한 서브쿼리를 가진 쿼리를 살펴보자.
위 쿼리의 결과를 보면, LEFT JOIN이기 때문에 team_id(100)인 team 데이터가 살아있다.
이번에도 위 쿼리를 Where절의 서브쿼리로 바꿔보자.
위 결과를 보면, Where절의 서브쿼리에서는 team_id(100)인 team 데이터가 빠져있다. Where절에서 IN으로 필터링했기 때문에 당연하게도 서브쿼리에 포함되어있지 않은 데이터는 사라지게 된다.
위 문제를 해결하기 위해서는 아래와 같은 OR m.team_id IS NULL을 포함시켜 줘야 한다.
결과를 보면, From절의 서브쿼리와 Where절의 서브쿼리의 결과가 같게 유도할 수 있다.
LEFT JOIN + (Where + Group By)를 포함한 서브쿼리
이번에는 From절에 LEFT JOIN + (Where + Group By)를 포함한 서브쿼리를 가진 쿼리를 살펴보자.
위 쿼리 결과를 보면 LEFT JOIN을 하기 때문에 team_id(5)인 team 데이터가 살아있다.
이번에도 위 쿼리를 Where절의 서브쿼리로 바꿔보자.
결과를 보면, From절의 서브쿼리와 Where절의 서브쿼리의 결과가 다르다.
Where절이 포함됐다고, 무조건 결과가 다른 것이 아니라 Where절로 인해서 메인쿼리와 조인할 레코드의 수가 줄어들게 되면 결과가 달라지는 것이다.
정리해 보면, LEFT JOIN에서는 경우에 따라 From절의 서브쿼리를 Where절의 서브쿼리로 바꾸지 못할 수도 있다. 서브쿼리 내에서 메인쿼리와 조인될 레코드가 필터링되면, Where절의 서브쿼리 변환할 수 없기 때문이다.
From절 서브쿼리 <-> Select절 서브쿼리 <-> Where절 서브쿼리
From절의 서브쿼리를 Select절의 서브쿼리로 변환할 수 있다.
위와 같이, 상관관계(Correlated) 서브쿼리형태로 Select절 서브쿼리를 구성하면 From절 서브쿼리와 같은 결과를 낼 수 있다.
상관관계(Correlated) 서브쿼리는 메인쿼리를 기준으로 값을 전달받아서 처리되기 때문에 메인쿼리의 데이터는 그대로 살아있는 마치 Outer Join과 같은 결과가 나온다.상관관계(Correlated) 서브쿼리는 메인쿼리의 행만큼 서브쿼리를 실행시키기 때문에 성능적으로 매우 안 좋다. 따라서 웬만해서는 사용하지 않는 것이 좋다. (이 부분은 아래 성능비교 부분에서 자세히 설명하겠다.)쿼리의 결과적인 측면만 고려했을 때, 아래 세 개의 쿼리의 결과는 같다고 볼 수 있다.
이제 위 세 가지 유형의 서브쿼리에 대한 성능을 비교해 보자.
서브쿼리 성능 비교
먼저, team 테이블에 1만 건, member 테이블에 10만 건의 데이터를 넣었다.
truncate team;
truncate member;
INSERT INTO team (team_id, team_name, created_at)
SELECT
generate_series,
'Team ' || generate_series,
NOW() - INTERVAL '1 year' * RANDOM()
FROM generate_series(1, 10000);
INSERT INTO member (member_id, member_name, team_id, join_date, use_yn)
SELECT
generate_series,
'Member ' || generate_series,
(RANDOM() * 9999 + 1)::integer,
NOW() - INTERVAL '2 years' * RANDOM(),
RANDOM() < 0.5
FROM generate_series(1, 100000);
From절 서브쿼리 실행계획 분석
먼저 From절 서브쿼리의 실행계획부터 살펴보자
루트 노드는 Hash Left Join을 수행하고 있다.
그렇다는 건 왼쪽에 있는 team 테이블을 Probe 테이블로 선정하고, 서브쿼리를 Build 테이블로 선정한다는 뜻이다.
Hash Join의 특징이 작은 테이블을 Build 테이블로 선정하는 것이기 때문에 크기가 작은 서브쿼리를 Build 테이블로 선정한 것이다.
또한, Hash 테이블을 만드는 과정에서 HashAggregate 연산이 이루어졌고, group by 결과가 10000건인 것을 확인할 수 있다.
정리해 보면, From절 서브쿼리를 마치 일반 조인과 같은 형태로 처리한다. 따라서 성능적으로 문제가 없다.
총 걸린 실행 시간은 34.353 ms이다.
Where절 서브쿼리 실행계획 분석
다음으로 Where절 서브쿼리의 실행계획을 살펴보자
select절에서 m.member_id를 가져오기 위해서 member 테이블과 조인도 해야 하기 때문에 From절에 비해서 실행계획의 내용이 길다.
루트 노드는 Hash Right Join을 하는 것을 확인할 수 있다.
member 테이블이 team 테이블보다 크기 때문에 오른쪽 테이블을 Probe 테이블로 선정하는 Hash Right Join이 수행됐다.
그다음 주목할 점은 SubPlan이다. SubPlan은 서브쿼리를 처리하는 방법 중 하나로 필터 방식이다.
따라서, Filter 연산에서 SubPlan이 사용되는 것을 확인할 수 있다.
정리해 보면, 서브쿼리를 처리하기 위해서 SubPlan이 1번 실행되었고, 그 결과를 메인쿼리에서 Filter 처리하는 데 사용했다.
조인 + 서브쿼리결과 필터링 방식이기 때문에 조인만 수행하는 From절 서브쿼리에 비해서는 성능이 떨어진다.또한 SubPlan을 실행하는 과정에서 인덱스가 없기 때문에 Seq Scan이 발생하는데, 만약 member의 크기가 크면 이것도 큰 성능 저하를 유발한다.
결론은, where절의 서브쿼리도 지양해야 된다. 꼭 사용하고 싶으면 인덱스를 설정해야 한다.
총 걸린 실행 시간은 65.737 ms이다.
Select절 서브쿼리 실행계획 분석
마지막으로 Select절 서브쿼리의 실행계획을 살펴보자
루트 노드는 Seq Scan을 수행하고 있다.
여기서 주목할 점은 SubPlan의 실행 횟수인데, 충격적이게도 10000번 반복하는 하는 것을 확인할 수 있다.
그 이유는 상관관계(Correlated) 서브쿼리는 메인 쿼리의 건수만큼 서브쿼리를 실행하기 때문이다.
이러한 이유로 상관관계(Correlated) 서브쿼리를 사용하는 것은 매우 지양해야 한다.
총 걸린 실행 시간은 25178.399 ms이다. (매우 오래 걸린다...)
