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
);
필터 방식 (Subplan)
Subplan은 서브쿼리를 별도의 쿼리 계획(Plan)으로 처리하는 방식이다.
필터 방식은 세미 조인이 개발되기 전에 사용된 방식이다.
서브쿼리가 메인 쿼리의 각 행에 대해 반복적으로 실행되어야 할 때 주로 사용한다.
그렇기 때문에, 필터 방식은 세미 조 인 중에서 성능적으로 NL 세미 조인과 비슷하다. 따라서 옵티마이저는 보통의 경우 필터 방식보단 세미 조인 방식을 더 선호한다.
일반적으로 서브쿼리가 상관 서브쿼리(correlated subquery)일 때 이 방식이 사용된다.
이제 실행계획에서 Subplan을 확인해 보자. 데이터는 team 테이블에 15건, member 테이블에 100건의 데이터를 넣었다.
INSERT INTO team (team_id, team_name, created_at)
SELECT i,
'Team ' || i,
CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 365)
FROM generate_series(1, 15) AS i;
INSERT INTO member (member_id, member_name, team_id, join_date)
SELECT i,
'Member ' || i,
(i % 15) + 1,
CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 365)
FROM generate_series(1, 100) AS i;
위 결과를 보면, loops가 15인 것을 확인할 수 있다. 이는 team 테이블의 건수만큼 member 테이블을 순회하는 것이다.
Rows Removed by Filter: 7는 15번의 반복에서 평균 7개씩의 레코드만큼을 필터링했다는 것이다.
따라서, 대략 15 * 7 인 105개의 레코드가 제거됐다는 의미로 생각할 수 있다.
세미 조인
세미 조인은 서브쿼리의 결과가 메인 쿼리의 행 선택에만 영향을 미치고, 결과 집합에 직접 포함되지 않는 경우 사용된다.
EXISTS 또는 IN 연산자와 함께 사용되는 서브쿼리에 주로 사용된다.
세미 조인은 서브쿼리의 결과가 필요한 메인 쿼리의 행을 효율적으로 필터링하는 데 사용된다.
이제 실행계획을 확인해 보자. 데이터는 team 테이블에 15건, member 테이블에 10000건의 데이터를 넣었다.
truncate member;
truncate team;
INSERT INTO team (team_id, team_name, created_at)
SELECT i,
'Team ' || i,
CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 365)
FROM generate_series(1, 15) AS i;
INSERT INTO member (member_id, member_name, team_id, join_date)
SELECT i,
'Member ' || i,
(i % 15) + 1,
CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 365)
FROM generate_series(1, 10000) AS i;
NL Semi Join은 첫 번째 테이블의 행이 두 번째 테이블과 매칭되면 반복을 중단하는 방식으로 동작한다.
(actual time=0.001..0.002 rows=8 loops=15) 이 부분을 해석해보면, 15번에 반복에서 평균 8개의 행을 검사했다는 의미이다.
member가 10000건인데 평균 8밖에 되지 않은 이유는 위에서 언급했듯이 NL Semi Join은 조건에 만족하는 순간 검사를 중단하기 때문이다.
이번에는 member를 기준 테이블로 조인을 걸어서 실행계획을 살펴보자.
Hash Semi Join은 보통 서브쿼리의 집합이 메인쿼리의 집합보다 작은 경우에 동작한다.
Hash Join과의 차이점은 Hash Semi Join 경우에 Probe 테이블의 행이 Build 테이블의 해시 테이블과 매칭되면 반복을 중단한다.
정리하면, NL Semi Join 과 Hash Semi Join 모두 테이블의 행을 효율적으로 필터링하는데 유용한 조인방식이다. 그렇기 때문에 EXISTS, IN에서 사용되는 서브쿼리를 처리하는데 주로 사용된다,
서브쿼리 Collapse (서브쿼리 병합)
Subquery Collapse는 서브쿼리를 메인 쿼리와 병합하여 하나의 쿼리로 처리하는 최적화 기법이다.
병합으로 인해 성능상의 이점이 있을 때 사용된다.
이번에는 데이터를 team 테이블에 1000건, member 테이블에 100000건의 데이터를 넣었다.
이제 실행계획을 살펴보자.
서브쿼리를 사용했음에도 불구하고, 옵티마이저가 판단하기에 Hash Join으로 처리하는 것이 더 효율적이라고 판단한 경우에는, 서브쿼리 대신 Hash Join을 사용하여 쿼리를 최적화한 것을 확인할 수 있다.
서브쿼리를 메인 쿼리와 병합함으로써, 옵티마이저는 Hash Join과 같은 더 유용한 조인 방식을 사용할 수 있기 때문에 옵티마이져는 Collapse를 선택한 것 같다.
