[PostgreSQL] Join

본 포스트에서는, 문법이 아니라 내부동작의 종류에 대해서 다룬다.

postgresql은 크게 중첩 루프 조인, 해시 조인, 병합 조인을 지원한다.

중첩 루프(Nested Loop:NL) 조인

중첩 루프 조인은 가장 간단하며, 기본적인 형태의 조인이다.
A와 B 테이블을 조인한다면, A로 반복 루프를 돌리고, 그 안에 B 중첩루프를 돌려서 일치하는걸 뱉어내는 것이다.

일반적으로 join 조건으로 비교연산 등을 행할 때 중첩루프 조인이 발생하는 듯 하다.
=로 동등 비교를 할 때는 중첩루프 조인이 잘 뜨지 않는다. 해시조인 쓰라고.

필터링 없이 무작정 다 긁어올 때도 중첩루프 조인이 뜬다.

일반적으로 중첩루프조인은 모든 각 데이터를 순회하면서 인덱스 스캔을 쏘는 방식을 가지기 때문에, 조인 중에서도 가장 시간적 비용이 많이 든다고 한다.
하지만 단순한 구조 덕에 데이터가 적을 때는 가장 효율적이다.
별도로 메모리를 먹는게 거의 없기 때문이다!

밑에 나오는 해시조인이나 병합조인이 속도면에서는 더 빠른 편이긴 하지만, CPU와 메모리를 좀 과도하게 사용하는 편이다.
그래서 NL조인이 아주 비효율적인 상황이 아니라면, NL 조인을 쓰는 것이 바람직하다.
NL 조인으로도 약 0.1초 걸리는 적당한 쿼리를 굳이 0.01 걸리는 해시조인으로 바꾸지는 말라는 말이다.

해시(Hash) 조인

해시조인은 내부적으로 한 테이블에 대한 해시테이블을 생성하고, 그걸 통해서 빠른 랜덤 매칭을 노리는 조인 방법이다. 조인 조건에 동등 비교를 걸 때 주로 발생한다.

위 쿼리플랜을 해석해보면,
Person 테이블로 해시테이블을 만들고, Person 해시테이블과 Text 테이블을 비교(Hash Cond)해 조인을 수행한다는 뜻이다.

그리고 해시테이블은 일반적으로 더 작은 테이블에 생성한다. 가급적 메모리에 올려서 처리하려고 하기 때문이다.
위의 경우에도 texts 테이블은 데이터가 수만개, persons 테이블은 열개밖에 되지 않았다.

해시조인은 병합조인의 조금 더 발전된 형태라 봐도 무방하다.
웬만해서는 hash join이 더 적용하기도 쉽고, 빠르기 때문이다.

게다가 집합 중 하나가 오버사이즈라도 다른 하나가 충분히 작다면 in-memory 조인이 가능하다.
둘다 오버사이즈라서 다 디스크에 쓰더라도 웬만큼은 빠르고.

병합(Merge) 조인

병합조인은 예전에는 해시조인의 위치를 차지하고 있던 조인 기법이지만, 해시조인이 등장하고 나서는 해시조인의 빈자리를 메꾸기만 하는 느낌이다. 병합조인도 마찬가지로 대량 처리에 효율적인 조인이다.

병합조인을 사용하기 위해서는 각 테이블에 대해서 인덱스 스캔이 떠야 한다.
때문에 text_id에 인덱스를 추가하겠다.

그리고 다시 조인을 시도해봤다.

음?
text_id과 t.id 둘다 인덱스가 걸렸음에도 순차스캔만으로 해시조인을 해버렸다.

사실 추가적인 조건이 또 있다.
양쪽 테이블이 인덱스 기준으로 정렬이 되어있어야 한다.

해시조인이 등장하고 나서는 쓰일 곳이 많이 줄어든 편이다.
추가로 정렬 처리도 해줘야 하고, 인메모리 연산도 해시조인보다 어렵기 때문이다.
해시조인은 하나만 메모리에 올라갈 크기면 되지만, 병합조인은 둘다 메모리에 올라가야 인메모리 연산이 가능하다.

그래도 병합조인이 빛을 발하는 한가지 장점이 있으니, 조인 조건이 동등 조건(=)이 아닌 경우에는 해시조인보다 유리하다는 것이다.
그럴 때는 병합조인이 최적이다.

그래서 조인 선택은

1. 작은 데이터 조인 -> NL조인
2. 큰 데이터 조인 -> 해시조인
3. 큰 데이터면서 조인 조건식이 =이 아닐때 -> 병합조인

정도로 정리하면 될 것이다.

참조
"『친절한 SQL 튜닝』, 조시형, 디비안(2018)"
https://severalnines.com/database-blog/overview-join-methods-postgresql
https://gs.saro.me/dev?page=20&tn=453