조인 최적화 (With PostgreSQL)

개요

• 나는 단일 테이블에 대한 쿼리에서의 인덱스 동작 방식은 잘 알고있다.
• 하지만 조인이 들어간 쿼리에서는 인덱스 설계를 잘 하지 못하는 경우가 있었다.
• 그래서 이번 글에서는 조인에서 인덱스가 어떻게 사용되는지를 정리해 보려고 한다.
• PostgreSQL의 옵티마이저는 조인을 처리하기 위해서 NL 조인, Hash Join, Merge Join을 선택한다.
• 여기서 개발자가 최적화할 수 있는 조인 방식은 NL 조인인것 같다.
  • NL 조인으로 사용될 수 없는 경우, 옵티마이저가 알아서 Hash Join, Merge Join으로 실행계획을 수립한다.
• 그렇기 때문에 이번 글에서는 NL 조인에서의 인덱스 설계 방법에 대해서 정리해 볼 것이다.

 

필수 사전 지식

• 인덱스 (In PostgreSQL)
• PostgreSQL 실행계획 분석하기 3편 (Join Methods)

NL 조인 성능 최적화

NL 조인은 가장 기본적은 조인방식이며 인덱스를 이용한 조인 방식이다.

 

NL 조인에서 사용되는 용어 정리

NL 조인을 설명하기 위해서 먼저 용어 정리부터 해보도록하자. 만약 아래와 같은 실행계획이 있다고 가정하자.

• NL 조인은 for문과 비슷하게 동작한다.
• 따라서 먼저 스캔하는 테이블을 Outer Table이라고 하고, Outer Table에서 추출된 건수 만큼 Inner Table을 반복해서 스캔한다.
• Outer Table은 Driving Table 또는 기준 테이블이라고 부르기도 한다.
• Inner Table은 Driven Table이라고 부르기도 한다.
• 정리하면 아래와 같이 테이블을 용어를 정리할 수 있다.
  • Outer Table = Driving Table = 기준 테이블
  • Inner Table = Driven Table

NL 조인 동작 방식

NL 조인의 동작원리를 아래와 같이 설명할 수 있다. 우선 두 테이블 모두 적절한 인덱스가 있다고 가정하자.

• 먼저 Outer Table의 인덱스를 통해서 7건의 데이터가 추출됐고, Outer Table에서 필터링된 후 3건의 데이터가 Inner Table의 인덱스로 넘어갔다.
• 위에 표시해둔 1번 영역을 보면 화살표가 1개이다. 그렇다는 건 Outer Table은 1번만 스캔한다는 의미이다.
  • 하지만, 2번 영역을 보면 Outer Table에서 3건이 넘어왔기 때문에 화살표가 3개인 것을 확인할 수 있다. 그렇다는 건 Inner Table을 3번 스캔했다는 의미다.
• 정리해 보면, Outer Table에서 넘어온 데이터 건수만큼 Inner Table을 스캔한다는 것이다.
  • 그렇기 때문에 Outer Table에서 데이터가 많이 넘어오면 그만큼 Inner Table의 스캔 횟수가 올라가고 더 비효율 적인 탐색이 된다.
• 옵티마이저는 위와 같은 이유로 Outer Table과 Inner Table을 선택할 때, 결과 수가 더 적을 것 같은 테이블을 Outer Table로 선택한다.
• 또한, 위에서 설명 했듯이 Outer Table에서 추출된 Row 만큼 Inner Table을 스캔하기 때문에 Inner Table의 경우는 인덱스를 꼭 사용하는 것이 좋다.
• 만약, Inner Table에서 인덱스를 사용하지 않는다면 Outer Table에서 추출된 Row 만큼 Inner Table을 Table Full Scan을 반복해야 하기 때문이다.

 

Outer Table, Inner Table 예측해보기

먼저 team 테이블과 member 테이블이 있다고 가정하자. team 테이블에 데이터가 1000건 member 테이블에 10000건 있다고 가정하자. 아래의 에서 옵티마이저는 어떤 테이블을 Outer Table로 선택할까?

select *
from team t
left join member m 
    on m.team_id = t.team_id;

• team 테이블에 데이터가 더 적기 때문에 team 테이블을 Outer Table 테이블로 선택한다.
• 아래와 같은 where절이 추가된 경우는 어떨까?

select *
from team t
left join member m 
    on m.team_id = t.team_id
where member_id > 9900;

• team 테이블의 경우 1000건이지만, member 테이블의 경우 where 조건이 있기 때문에 100건만 추출된다.
• 따라서 위의 경우 Outer Table은 member 테이블이 선택된다.

정리해 보면, 옵티마이저는 두 테이블 중 넘겨줄 데이터가 더 적은 테이블을 Outer Table로 선택한다. 이 기준은 절대적인 것은 아니며 옵티마이저는 더 다양한 요소를 체크한 후 Outer Table을 선정한다.

 

 

Outer Table Index, Inner Table Index 설계 고민 해보기

먼저 team 테이블과 member 테이블이 있다고 가정하자. 또한, team 테이블을 Outer Table, member 테이블을 Inner Table이라고 가정하자.
아래와 같은 쿼리가 있다고 했을 때, 어떤 식으로 인덱스를 설계하면 가장 스캔 효율이 좋을까?

select *
from team t
left join member m
    on m.team_id = t.team_id
where t.team_name = 'team1'
  and t.team_code = 'T1'
  and m.member_name = 'member1';

• Outer Table인 team 테이블부터 생각해 보자.
• where절에 team_name과 team_code가 있기 때문에 인덱스는 아래와 같이 생성하면 될 것 같다.

create index idx_team_team_name_team_code
on team(team_name, team_code); -- 카디널리티가 team_name이 더 높다는 전제

• Inner Table인 member 테이블은 어떨까?
• where절에 member_name이 있기 때문에 아래와 같이 인덱스를 생성하면 될 것 같다.

create index idx_member_member_name
on member(member_name);

• 하지만, Inner Table 같은 경우는 조인 조건도 같이 고려해줘야 한다.

• Outer Table에서 추출된 데이터를 조인 컬럼을 기준으로 필터하기 때문에 Inner Table의 인덱스를 설계할 때에는 조인 컬럼을 꼭 생각해 줘야 한다.
• 그렇기 때문에 아래와 같이 인덱스를 설계하는 것이 가장 스캔 효율이 좋게 인덱스를 설계하는 것이다.

create index idx_member_team_id_member_name
on member(team_id, member_name);

• 위와 같이 인덱스를 생성한다면, 아래와 같은 구조일 것이다.

위에서 설명한 내용은 테이블을 스캔하는 관점에서만 설명한 것이다. 실제로는 인덱스 컬럼의 사용 빈도수와 카디널리티 그리고 데이터양과 같은 추가적인 정보들을 고려해서 신중하게 인덱스를 설계해야 한다.

 

 

NL조인 시 Materialize 가 발생하는 경우

종종 Materialize 오퍼레이션이 나타는 경우가 있다. 이 오퍼레이션은 Inner 테이블에 적절한 인덱스가 존재하지 않을 때에 주로 발생한다. 특히, 조인 컬럼에 대한 인덱스가 없을 경우 주로 발생한다.

• Materialize를 유도하기 위해 다른 조인 방법을 비활성화했다.
• Materialize는 반복적으로 Inner 테이블을 액세스 하는 부하를 줄이기 위한 방식이다.
• Materialize는 Inner Table의 반복적인 액세스를 줄이기 위해서 Inner Table 처리 결과를 Temp 테이블에 저장한다.
  • 결과적으로 Inner Table에 대한 Seq Scan은 1번이다.
• 하지만 Materialize는 차선책일 뿐이다. 그렇기 때문에 Materialize가 발생했다면 조인 조건 컬럼에 대한 인덱스 생성 여부를 고려해 봐야 한다.

 

정리

위 예시를 테스트해 보기 위해서는 아래와 같은 설정이 필요하다.

set enable_hashjoin = false;
set enable_mergejoin = false;

• 옵티마이저가 판단했을 때, 효율적인 인덱스가 없다고 판단하면 주로 Hash Join 또는 Merge Join을 사용하기 때문이다.
• 그렇다고 하더라도 조인 시 어떤 식으로 인덱스가 동작하는지 아는 것은 매우 중요하다.
• 나 또한 그림을 그려보고 나서야 조인 시 인덱스의 동작 과정이 정확히 이해되었다.