SQL 조인 성능 비교: 중첩 루프·병합·해시 조인의 선택 기준

1장. 주문 3건을 조인했는데 왜 결과가 6건이 되었을까#

고객 한 명이 주문 세 건을 가지고 있다고 하겠습니다.

주문 데이터는 다음과 같습니다.

주문ID 고객ID 금액
101 7 100
102 7 200
103 7 300

고객 데이터는 한 행입니다.

고객ID 이름
7 가람

두 테이블을 고객ID로 조인하면 결과는 당연히 세 행입니다.

101 | 가람 | 100
102 | 가람 | 200
103 | 가람 | 300

그런데 오른쪽 테이블을 고객이 아니라 연락처로 바꿔보겠습니다.

고객 7에게 전화번호가 두 개 있습니다.

고객ID 전화번호
7 010-1111-1111
7 010-2222-2222

주문과 연락처를 고객ID로 조인하면 결과는 몇 행일까요?

주문 3건
×
연락처 2건
=
6행

이 결과는 조인 알고리즘이 잘못 선택되어서 생긴 것이 아닙니다.

중첩 루프를 사용해도 6행입니다.

해시 조인을 사용해도 6행입니다.

병합 조인을 사용해도 6행입니다.

조인 성능을 비교하기 전에 먼저 조인 결과 자체가 올바른지 확인해야 합니다.

2장. 논리적 조인과 물리적 조인은 다른 개념이다#

SQL에는 다음과 같이 작성합니다.

SELECT
    o.order_id,
    c.name,
    o.amount
FROM orders AS o
JOIN customer AS c
    ON c.customer_id = o.customer_id;

여기서 SQL은 논리적인 요구를 표현합니다.

orders와 customer에서
customer_id가 같은 행을 연결하라.

하지만 DBMS가 실제로 두 테이블을 어떻게 연결할지는 별개의 문제입니다.

대표적인 물리 조인 방식은 다음과 같습니다.

Nested Loop Join
Merge Join
Hash Join

즉:

논리 조인
→ 어떤 행이 결과가 되는가

물리 조인
→ 그 결과를 어떤 방식으로 찾아낼 것인가

입니다.

3장. 실습 데이터 만들기#

다음 테이블을 사용하겠습니다.

CREATE TABLE customer (
    customer_id integer PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer NOT NULL
        REFERENCES customer(customer_id),
    amount integer NOT NULL
);

데이터를 넣습니다.

INSERT INTO customer VALUES
    (1, '가람'),
    (2, '나래'),
    (3, '다온');

INSERT INTO orders VALUES
    (101, 1, 5000),
    (102, 1, 7000),
    (103, 3, 9000);

조인합니다.

SELECT
    o.order_id,
    c.name,
    o.amount
FROM orders AS o
JOIN customer AS c
    ON c.customer_id = o.customer_id
ORDER BY o.order_id;

결과는 다음과 같습니다.

주문ID 고객 금액
101 가람 5,000
102 가람 7,000
103 다온 9,000

나래는 주문이 없으므로 내부 조인 결과에 나오지 않습니다.

4장. 조인 알고리즘이 달라도 결과는 같아야 한다#

DBMS가 중첩 루프를 사용하든 해시 조인을 사용하든 결과의 의미는 같아야 합니다.

flowchart LR
    O["orders"] --> J["JOIN customer_id"]
    C["customer"] --> J
    J --> R["101 가람 5000<br/>102 가람 7000<br/>103 다온 9000"]

물리적인 작업 방법만 달라집니다.

따라서 조인 튜닝에서 반드시 지켜야 할 원칙이 있습니다.

같은 결과를 만드는 실행 계획끼리 성능을 비교해야 한다.

행을 누락시켜 빨라졌다면 튜닝이 아니라 다른 SQL입니다.

5장. Nested Loop Join은 바깥 행마다 안쪽을 찾는다#

중첩 루프는 이름 그대로 반복 구조로 이해할 수 있습니다.

개념적으로 다음과 같습니다.

바깥쪽 행 하나 읽기
↓
안쪽에서 일치 행 찾기
↓
결과 출력
↓
다음 바깥쪽 행

의사 코드로 표현하면 다음과 같습니다.

for outer_row in outer_table:
    for inner_row in find_matching_rows(outer_row):
        emit(outer_row, inner_row)

주문을 바깥 입력으로 사용하면:

주문 101
→ 고객 1 찾기

주문 102
→ 고객 1 찾기

주문 103
→ 고객 3 찾기

가 됩니다.

6장. 중첩 루프의 핵심은 안쪽 탐색 비용이다#

고객 테이블에 기본키 인덱스가 있다고 하겠습니다.

customer_id
→ Primary Key Index

주문 101을 읽고 고객 1을 찾습니다.

인덱스를 통해 빠르게 한 행을 찾을 수 있습니다.

주문 102에서도 같은 방식입니다.

이 경우 개념적으로:

바깥 주문 3행
×
안쪽 인덱스 탐색 3회

입니다.

작은 바깥 입력과 저렴한 안쪽 탐색이 결합되면 중첩 루프는 매우 효율적일 수 있습니다.

7장. 안쪽에 인덱스가 없으면 반복 비용이 커진다#

고객 테이블에 적절한 인덱스가 없다고 가정하겠습니다.

주문 한 건마다 고객 전체를 스캔해야 할 수도 있습니다.

작은 예에서는:

주문 3행
×
고객 3행
=
9번 비교 기회

입니다.

하지만 규모가 커지면 문제가 달라집니다.

바깥 100,000행
×
안쪽 1,000,000행

을 단순 반복한다면 엄청난 작업량이 됩니다.

물론 실제 DBMS는 여러 최적화를 사용할 수 있지만 기본 원리는 같습니다.

8장. 원본 테이블 크기보다 필터 후 바깥 행 수가 중요하다#

orders 테이블이 1억 건이라고 하겠습니다.

이것만 보면 중첩 루프가 위험해 보입니다.

하지만 다음 조건으로 먼저 3행만 남는다면:

WHERE order_id IN (101, 102, 103)

실제 조인 노드에 들어오는 바깥 입력은 3행뿐일 수 있습니다.

따라서 다음 두 숫자를 구분해야 합니다.

원본 테이블
→ 1억 행

조인 직전 실제 입력
→ 3행

조인 비용은 후자의 영향을 크게 받습니다.

9장. 같은 조인 키가 반복되면 Memoize가 도움이 될 수도 있다#

주문 101과 102는 둘 다 고객 1입니다.

101 → customer_id 1
102 → customer_id 1

안쪽 고객 탐색 결과가 같기 때문에 같은 검색을 반복할 필요가 없다고 생각할 수 있습니다.

PostgreSQL은 상황에 따라 Memoize 노드를 사용해 같은 조인 키의 결과를 재사용할 수 있습니다.

개념적으로:

customer_id 1 검색
↓
결과 캐시

다시 customer_id 1
↓
캐시 재사용

처럼 동작할 수 있습니다.

따라서 중첩 루프라고 해서 항상 같은 인덱스 탐색을 처음부터 반복한다고 단정해서는 안 됩니다.

10장. Nested Loop 실행 계획에서는 loops를 본다#

다음과 같은 가상 실행 계획이 있다고 하겠습니다.

Nested Loop
  -> Seq Scan on orders
       actual rows=200000 loops=1

  -> Index Scan on customer
       actual rows=1 loops=200000

안쪽 Index Scan의 actual rows=1만 보면 작은 작업처럼 보입니다.

하지만:

loops=200000

입니다.

실제로는 20만 번 호출되었습니다.

Nested Loop에서는 다음 두 값을 함께 봐야 합니다.

안쪽 1회 비용

×

바깥 반복 횟수

11장. Merge Join은 두 입력을 정렬된 순서로 맞춘다#

병합 조인은 두 입력이 조인 키 순서로 정렬되어 있다고 생각하면 이해하기 쉽습니다.

주문 쪽:

customer 1 | order 101
customer 1 | order 102
customer 3 | order 103

고객 쪽:

customer 1 | 가람
customer 2 | 나래
customer 3 | 다온

두 입력을 앞에서부터 비교합니다.

12장. Merge Join의 진행 과정을 따라가 보자#

첫 번째 비교입니다.

주문 customer_id = 1

고객 customer_id = 1

같습니다.

따라서 가람과 주문 두 건을 연결합니다.

101 | 가람
102 | 가람

다음 주문 키는 3입니다.

고객 쪽 현재 키는 2입니다.

3 > 2

이므로 고객 쪽을 다음으로 이동합니다.

customer 3 | 다온

이제 키가 같습니다.

주문 103과 다온을 연결합니다.

13장. 병합 조인은 정렬이 이미 되어 있다면 매력적일 수 있다#

두 입력이 조인 키 기준으로 이미 정렬되어 있다면 별도의 정렬 비용을 줄일 수 있습니다.

예를 들어 적절한 인덱스를 이용해 다음 순서로 데이터를 읽을 수 있다고 하겠습니다.

customer_id 순서

그러면 Merge Join에 필요한 입력 순서를 얻기 쉬울 수 있습니다.

하지만 정렬이 없으면 DBMS가 Sort 노드를 추가할 수 있습니다.

flowchart TD
    O["orders"] --> SO["Sort customer_id"]
    C["customer"] --> SC["Sort customer_id"]
    SO --> M["Merge Join"]
    SC --> M

이 경우 정렬 비용도 조인 비용에 포함됩니다.

14장. Merge Join에서 정렬은 공짜가 아니다#

입력이 100행이라면 정렬 비용이 작습니다.

입력이 1억 행이라면 상황이 다릅니다.

정렬이 메모리를 넘으면 디스크를 사용할 수도 있습니다.

따라서 Merge Join을 볼 때는 조인 노드만 보지 말고 그 아래에 있는:

Sort

도 함께 확인해야 합니다.

15장. 같은 조인 키가 양쪽에 여러 개 있으면 결과는 곱으로 늘어난다#

왼쪽 데이터가 다음과 같습니다.

key 1 | A
key 1 | B

오른쪽도:

key 1 | X
key 1 | Y

라면 조인 결과는:

A-X
A-Y
B-X
B-Y

입니다.

즉:

2 × 2
=
4행

입니다.

Merge Join이라고 이 결과를 줄이는 것은 아닙니다.

같은 키 그룹의 모든 유효한 조합을 만들어야 합니다.

16장. Hash Join은 한쪽 입력으로 해시 테이블을 만든다#

해시 조인은 등가 조인에서 흔히 사용할 수 있는 방식입니다.

예:

ON c.customer_id = o.customer_id

개념적으로 한쪽 입력으로 해시 테이블을 만듭니다.

flowchart LR
    C["customer"] --> H["Hash Table"]
    O["orders"] --> P["Probe"]
    H --> P
    P --> R["Join Result"]

예를 들어 고객 세 행을 해시에 저장합니다.

hash(1) → 가람
hash(2) → 나래
hash(3) → 다온

그다음 주문을 하나씩 읽으면서 해당 버킷을 찾습니다.

17장. Hash Join의 두 단계는 Build와 Probe다#

첫 번째는 Build 단계입니다.

customer 읽기
↓
해시 테이블 생성

두 번째는 Probe 단계입니다.

order 읽기
↓
customer_id 해시
↓
해당 버킷 탐색
↓
실제 키 확인
↓
결과 출력

이 두 단계를 구분하면 실행 계획도 이해하기 쉽습니다.

18장. 작은 쪽을 Build 입력으로 사용하면 메모리 부담을 줄이기 쉽다#

고객이 10만 건이고 주문이 1억 건이라고 하겠습니다.

단순하게 보면 고객 쪽으로 해시 테이블을 만드는 것이 메모리 측면에서 더 유리할 수 있습니다.

고객 10만 행
→ Hash Build

주문 1억 행
→ Probe

하지만 실제 옵티마이저 판단은 행 크기와 필터 결과, 병렬 실행 등 다양한 요소를 고려합니다.

단순히 원본 행 수만 보고 결정한다고 생각해서는 안 됩니다.

19장. 해시 테이블이 메모리를 넘으면 비용이 커질 수 있다#

Build 입력이 예상보다 커졌다고 하겠습니다.

메모리에 모두 들어가지 못하면 여러 배치로 나누거나 임시 저장소를 사용할 수 있습니다.

개념적으로:

Hash Build
↓
메모리 초과
↓
Batch 분할
↓
임시 파일 사용 가능

이런 상황에서는 디스크 I/O가 증가할 수 있습니다.

그래서:

Hash Join
=
항상 빠름

은 틀린 설명입니다.

20장. PostgreSQL Hash 노드에서는 Batches와 Memory Usage를 볼 수 있다#

실행 계획에 다음과 비슷한 정보가 나올 수 있습니다.

Hash
  Buckets: ...
  Batches: ...
  Memory Usage: ...

확인할 내용은 다음과 같습니다.

Build 입력이 예상보다 커졌는가?

Batches가 증가했는가?

메모리 사용이 과도한가?

배치 수 증가가 반드시 문제라는 뜻은 아니지만 메모리 안에서 한 번에 처리하지 못했다는 단서가 될 수 있습니다.

21장. 해시 조인은 등가 조건에 적합하다#

다음 조건은 해시 조인의 전형적인 후보입니다.

ON o.customer_id = c.customer_id

반면 다음과 같은 순수 범위 조건:

ON a.value > b.value

은 해시 키의 동일성 비교만으로 해결할 수 없습니다.

따라서 해시 조인은 주로 등가 조인 조건과 연결해서 이해하는 것이 좋습니다.

22장. 등가 조건과 추가 필터를 함께 사용할 수도 있다#

다음 조인을 보겠습니다.

SELECT ...
FROM orders AS o
JOIN customer AS c
    ON c.customer_id = o.customer_id
   AND o.amount > 5000;

customer_id 등가 조건으로 후보를 찾은 뒤 amount > 5000 조건을 추가로 검사할 수 있습니다.

실행 계획에서는:

Hash Cond

Join Filter

또는 다른 필터 형태가 구분되어 나타날 수 있습니다.

23장. 세 조인 방식을 한눈에 비교하면#

방식 기본 원리 유리해질 수 있는 상황 주요 비용
Nested Loop 바깥 행마다 안쪽 탐색 바깥 결과가 작고 안쪽 탐색이 저렴 반복 횟수
Merge Join 정렬된 두 입력을 병합 입력이 이미 정렬되어 있거나 정렬을 활용 정렬 비용
Hash Join 한쪽을 해시화 후 다른 쪽 탐색 큰 등가 조인과 충분한 메모리 해시 구축·spill

이 표는 절대 규칙이 아닙니다.

실제 데이터 크기와 분포, 인덱스, 캐시, 병렬 처리 등에 따라 결과가 달라집니다.

24장. “OLTP는 Nested Loop, DW는 Hash Join”으로 외우면 부족하다#

실무에서 이런 설명을 종종 접할 수 있습니다.

OLTP
→ Nested Loop

DW
→ Hash Join

경향을 이해하는 데는 도움이 될 수 있지만 규칙은 아닙니다.

OLTP에서도 대량 집계는 Hash Join이 유리할 수 있습니다.

분석 시스템에서도 한 건 조회는 Nested Loop가 효율적일 수 있습니다.

따라서 시스템 종류보다 실제 조인 입력 크기와 접근 비용을 봐야 합니다.

25장. 작은 바깥 입력 + 좋은 인덱스는 Nested Loop의 전형적인 강점이다#

orders 1억 건 중 다음 조건으로 10건만 남았다고 하겠습니다.

WHERE order_id BETWEEN 100 AND 109

그리고 customer_id는 고객 기본키를 참조합니다.

이 경우:

주문 10행
×
고객 PK 탐색 10회

정도의 구조가 될 수 있습니다.

전체 고객 테이블을 해시로 구축하는 것보다 중첩 루프가 더 저렴할 수 있습니다.

26장. 바깥 입력을 잘못 작게 예상하면 Nested Loop가 폭발할 수 있다#

옵티마이저는 READY 주문이 100건이라고 예상했습니다.

그래서 중첩 루프를 선택했습니다.

실제로는 500,000건이었습니다.

그러면:

예상
100회 안쪽 탐색

실제
500,000회 안쪽 탐색

이 됩니다.

중첩 루프 자체가 나쁜 것이 아니라 입력 규모 추정이 틀린 것이 문제입니다.

27장. Merge Join은 정렬 결과를 다른 연산에서도 활용할 수 있다#

다음 SQL이 있다고 하겠습니다.

SELECT ...
FROM a
JOIN b
    ON a.key = b.key
ORDER BY a.key;

조인 결과가 이미 필요한 정렬 순서와 잘 맞는다면 Merge Join의 정렬 비용이 다른 작업에도 활용될 수 있습니다.

반대로 Hash Join을 사용하면 최종 ORDER BY를 위해 별도 정렬이 필요할 수 있습니다.

즉 조인 연산 하나만 분리해서 비교하면 전체 계획의 비용을 놓칠 수 있습니다.

28장. Hash Join은 첫 결과를 얻기 전에 Build 단계가 필요할 수 있다#

Nested Loop는 첫 바깥 행의 안쪽 탐색이 끝나면 빠르게 첫 결과를 반환할 수 있습니다.

Hash Join은 먼저 한쪽 입력으로 해시 테이블을 만들어야 할 수 있습니다.

Build 완료
↓
Probe 시작
↓
결과 출력

따라서 전체 처리량은 좋아도 첫 행 응답까지 시간이 더 걸릴 수 있습니다.

업무가 첫 화면 응답에 민감한지 대량 처리량에 민감한지도 중요합니다.

29장. 조인 결과가 늘어나는 문제를 성능 문제와 혼동하지 말자#

다음 데이터가 있다고 하겠습니다.

고객:

C1

주문:

O1
O2
O3

연락처:

P1
P2

주문과 연락처를 고객ID로 연결하면:

3 × 2
=
6행

입니다.

이것은 알고리즘 문제가 아닙니다.

관계 자체가 1:N과 1:N으로 이어져 있기 때문입니다.

30장. 조인 뒤 금액을 합치면 중복 계산이 발생할 수 있다#

주문 금액이:

100
200
300

이라고 하겠습니다.

원래 합계는:

600

입니다.

연락처 두 행과 조인하면:

100
100

200
200

300
300

이 됩니다.

이 상태에서 SUM을 하면:

1200

이 나옵니다.

조인 알고리즘을 아무리 빠르게 바꿔도 잘못된 1200을 더 빨리 계산할 뿐입니다.

31장. DISTINCT로 조인 중복을 가리는 것은 위험할 수 있다#

다음처럼 처리할 수 있습니다.

SELECT DISTINCT
    o.order_id,
    o.amount
FROM orders AS o
JOIN customer_phone AS p
    ON p.customer_id = o.customer_id;

주문 목록만 필요하다면 맞을 수도 있습니다.

하지만:

어떤 연락처를 사용할 것인가?

라는 업무 문제는 해결하지 않습니다.

대표 연락처가 필요하다면 대표 규칙을 먼저 정의해야 합니다.

32장. 대표 연락처 하나를 고른 뒤 조인할 수 있다#

연락처에 다음 정보가 있다고 하겠습니다.

customer_id
phone
is_primary
updated_at

대표 연락처 규칙이:

is_primary = true

라면 먼저 대표 연락처 한 행으로 줄인 뒤 조인할 수 있습니다.

또는 명확한 정렬 기준으로 한 행을 선택할 수 있습니다.

중요한 것은 중복을 제거하는 것이 아니라 원하는 한 행을 정의하는 것입니다.

33장. 존재 여부만 필요하면 JOIN보다 EXISTS가 의미에 더 잘 맞을 수 있다#

질문이 다음과 같다고 하겠습니다.

연락처가 하나 이상 있는 고객을 찾아라.

연락처 자체를 출력할 필요는 없습니다.

그러면:

SELECT
    c.customer_id,
    c.name
FROM customer AS c
WHERE EXISTS (
    SELECT 1
    FROM customer_phone AS p
    WHERE p.customer_id = c.customer_id
);

처럼 존재 여부를 표현할 수 있습니다.

이 경우 고객 한 명을 여러 연락처 수만큼 반복할 이유가 없습니다.

34장. Semi Join은 “상대 행이 존재하는가”를 위한 개념이다#

EXISTS 같은 조건은 실행 계획에서 세미 조인 형태로 구현될 수 있습니다.

세미 조인의 핵심은 다음과 같습니다.

왼쪽 행
↓
오른쪽에 일치 행 하나라도 존재?
↓
예 → 왼쪽 행 한 번 반환

오른쪽에 100개 행이 있어도 왼쪽 행을 100번 복제하는 것이 목적이 아닙니다.

35장. Anti Join은 “상대 행이 존재하지 않는가”를 확인한다#

다음 질문도 있습니다.

주문이 한 번도 없는 고객은?

SELECT
    c.customer_id,
    c.name
FROM customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

이런 조회는 Anti Join 형태로 처리될 수 있습니다.

논리적인 목적을 정확하게 표현하면 DBMS가 적합한 물리 계획을 선택하기도 쉬워집니다.

36장. 조인 순서는 SQL에 적힌 순서와 항상 같지 않다#

다음 SQL이 있다고 하겠습니다.

FROM A
JOIN B ...
JOIN C ...

DBMS가 반드시 A → B → C 순서로 실제 실행하는 것은 아닙니다.

옵티마이저가 비용을 비교해 조인 순서를 바꿀 수 있습니다.

예를 들어 필터 후 C가 매우 작다면:

C
↓
B
↓
A

순서가 더 저렴할 수도 있습니다.

37장. 조인 순서에서 중요한 것은 중간 결과 크기다#

다음 세 테이블이 있다고 하겠습니다.

A = 1,000만 행
B = 100만 행
C = 100행

하지만 C 조건을 먼저 적용하면 1행만 남습니다.

이 1행을 이용해 B와 A를 좁힐 수 있다면 중간 작업량을 크게 줄일 수 있습니다.

따라서 단순히 원본 테이블 크기만 보는 것보다:

필터 후 행 수

가 중요합니다.

38장. 잘못된 카디널리티 추정은 조인 순서도 망칠 수 있다#

옵티마이저가 C가 1행이라고 예상했는데 실제로는 100만 행이라면 문제가 생깁니다.

C를 가장 먼저 사용하도록 계획했다가 예상보다 큰 중간 결과를 만들 수 있습니다.

조인 성능 분석에서도 다음을 확인해야 합니다.

estimated rows

actual rows

조인 방식과 행 수 추정은 서로 연결되어 있습니다.

39장. 데이터 편중도 조인 비용을 바꾼다#

고객별 주문 수가 균등하다고 가정하면:

고객 1명당 평균 100건

이라고 계산할 수 있습니다.

하지만 실제로 특정 고객 C999에게:

500만 건

의 주문이 몰려 있다면 해당 고객 조건에서 조인 결과가 매우 커집니다.

평균값만으로는 실제 작업량을 예측하기 어렵습니다.

40장. 조인 키의 고유성은 결과 행 수를 크게 좌우한다#

다음 관계를 비교해 보겠습니다.

고객 PK와 주문 FK#

customer 1
:
orders N

조인하면 주문 수만큼 결과가 나옵니다.

주문과 주문항목#

order 1
:
order_item N

주문 한 건이 여러 행이 됩니다.

주문과 연락처#

고객ID를 통해:

orders N
×
phones M

형태가 되면 행 수가 더욱 증가할 수 있습니다.

성능 계산 전에 관계의 카디널리티를 알아야 합니다.

41장. 실행 계획에서 Nested Loop를 볼 때 확인할 것#

다음 항목을 확인합니다.

바깥 입력 실제 행 수

안쪽 노드 loops

안쪽 한 번의 실제 비용

안쪽 인덱스 여부

같은 키 반복 여부

Memoize 사용 여부

특히 다음 구조는 주의가 필요합니다.

안쪽은 매우 빠름

하지만
loops = 수백만

작은 비용도 반복되면 큰 비용이 됩니다.

42장. Merge Join을 볼 때 확인할 것#

다음 항목을 봅니다.

양쪽 입력 행 수

Sort 노드 존재 여부

정렬이 메모리에서 끝났는가

이미 인덱스로 정렬 순서를 얻었는가

같은 키의 중복이 얼마나 많은가

결과 행 수가 얼마나 증식하는가

Merge Join 자체만 보고 평가하면 안 됩니다.

정렬을 만들기 위해 소비한 비용까지 함께 봐야 합니다.

43장. Hash Join을 볼 때 확인할 것#

다음 항목을 확인합니다.

어느 쪽이 Build 입력인가

Build 입력 실제 행 수

Memory Usage

Buckets

Batches

spill 여부

Probe 입력 규모

조인 키 분포

Build 쪽 추정이 크게 틀리면 해시 메모리 계획도 어긋날 수 있습니다.

44장. PostgreSQL에서 실제 계획을 확인하는 방법#

ANALYZE customer;
ANALYZE orders;

실행 계획을 확인합니다.

EXPLAIN (
    ANALYZE,
    BUFFERS
)
SELECT
    o.order_id,
    c.name,
    o.amount
FROM orders AS o
JOIN customer AS c
    ON c.customer_id = o.customer_id
ORDER BY o.order_id;

실제 계획은 데이터 규모와 통계, PostgreSQL 버전과 설정에 따라 달라질 수 있습니다.

작은 테이블에서는 Seq Scan과 Nested Loop가 나올 수도 있고 다른 계획이 나올 수도 있습니다.

45장. 조인 알고리즘을 강제로 바꾸는 것이 튜닝의 목적은 아니다#

실행 계획을 보고 다음처럼 생각하기 쉽습니다.

Hash Join으로 바꾸면 빨라질 것 같다.

하지만 실제 문제는:

추정 행 수 오류

잘못된 조인 조건

필터 누락

인덱스 부재

행 증식

일 수 있습니다.

조인 알고리즘 이름을 바꾸는 것보다 원인을 찾는 것이 먼저입니다.

46장. PostgreSQL과 Oracle의 힌트 체계를 혼동하면 안 된다#

Oracle에는 다음과 같은 힌트를 사용하는 환경이 있습니다.

USE_NL

USE_HASH

LEADING

ORDERED

이 문법을 PostgreSQL SQL에 그대로 넣어 동일하게 조인 계획을 강제할 수 있다고 생각하면 안 됩니다.

DBMS마다 옵티마이저 제어 방식과 지원 기능이 다릅니다.

47장. 같은 조인도 데이터 규모가 달라지면 최적 계획이 바뀔 수 있다#

개발 환경에서는:

customer = 100행
orders = 1,000행

입니다.

운영에서는:

customer = 1,000만 행
orders = 10억 행

일 수 있습니다.

작은 테스트 데이터에서 Nested Loop가 빨랐다고 대규모 운영에서도 동일한 결과를 보장하지 않습니다.

실제 분포와 규모에 가까운 테스트가 중요합니다.

48장. 캐시 상태도 조인 비용에 영향을 준다#

Nested Loop 안쪽 인덱스 페이지가 모두 메모리에 있다면 탐색이 매우 빠를 수 있습니다.

반대로 매 탐색마다 저장 장치 읽기가 필요하면 비용이 크게 증가합니다.

Hash Join의 Build 입력도 메모리에 잘 들어가는지 중요합니다.

Merge Join의 정렬도 메모리와 디스크 사용 여부가 중요합니다.

즉 같은 알고리즘도 캐시와 메모리 상태에 따라 비용이 달라집니다.

49장. 병렬 처리까지 들어오면 단순한 비교가 더 어려워진다#

대규모 데이터에서는 병렬 Hash Join이나 병렬 스캔이 사용될 수 있습니다.

개념적으로:

flowchart TD
    O["큰 orders"] --> W1["Worker 1"]
    O --> W2["Worker 2"]
    O --> W3["Worker 3"]

    W1 --> J["Parallel Join"]
    W2 --> J
    W3 --> J

이 경우 단일 프로세스 기준의 단순 비교만으로는 실제 비용을 설명하기 어렵습니다.

50장. 첫 결과 시간과 전체 처리 시간도 구분해야 한다#

사용자가 검색 화면을 열었다고 하겠습니다.

상위 20건만 빨리 보여주면 됩니다.

이런 경우 첫 행 또는 첫 20행을 얼마나 빨리 만들 수 있는지가 중요할 수 있습니다.

반면 야간 정산 작업은 전체 데이터를 모두 처리해야 합니다.

온라인 조회
→ 빠른 첫 결과 중요

배치 집계
→ 전체 처리량 중요

같은 조인이라도 목표가 다릅니다.

51장. 조인 뒤 GROUP BY가 있다면 행 증식 비용을 반드시 계산해야 한다#

다음 SQL을 생각해 보겠습니다.

SELECT
    c.customer_id,
    SUM(o.amount)
FROM customer AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id
JOIN customer_phone AS p
    ON p.customer_id = c.customer_id
GROUP BY c.customer_id;

고객에게 전화번호가 여러 개라면 주문 행이 연락처 수만큼 반복됩니다.

합계가 커질 수 있습니다.

성능도 나빠지고 결과도 틀립니다.

52장. 집계 후 조인으로 행 단위를 먼저 맞출 수 있다#

먼저 고객별 주문 합계를 만듭니다.

WITH order_total AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.name,
    ot.total_amount
FROM customer AS c
JOIN order_total AS ot
    ON ot.customer_id = c.customer_id;

이제 한 고객당 주문 합계 한 행이 됩니다.

필요한 경우 연락처도 대표 한 행으로 먼저 정리한 뒤 연결할 수 있습니다.

53장. 같은 결과를 만드는 쿼리끼리 비교해야 한다#

다음 SQL A는 주문 상세를 모두 반환합니다.

SELECT
    o.order_id,
    c.name,
    o.amount
...

SQL B는 고객별 합계만 반환합니다.

SELECT
    c.customer_id,
    SUM(o.amount)
...
GROUP BY c.customer_id;

B가 훨씬 빠르다고 해서 A를 B로 튜닝했다고 할 수 없습니다.

반환하는 업무 의미가 다릅니다.

성능 비교는 결과 단위가 같아야 합니다.

54장. 조인 성능 문제의 대표적인 다섯 패턴#

증상 우선 확인
Nested Loop가 느림 안쪽 loops 폭증
Merge Join이 느림 큰 Sort 또는 spill
Hash Join이 느림 Build 크기와 Batches
조인 후 행 수 급증 키의 1:N·N:M 관계
조인 후 합계가 커짐 같은 업무 행이 여러 번 복제됐는지

알고리즘보다 결과 행 구조를 먼저 확인하는 이유가 여기에 있습니다.

55장. 조인 튜닝의 첫 단계는 관계를 적는 것이다#

예를 들어 다음처럼 정리해 봅니다.

CUSTOMER 1 : N ORDERS

CUSTOMER 1 : N PHONE

그러면:

ORDERS
JOIN PHONE
BY customer_id

의 잠재 결과 규모가 보입니다.

고객 한 명이 주문 10개, 연락처 3개라면:

10 × 3
=
30행

까지 나올 수 있습니다.

조인 관계를 적는 것만으로도 많은 오류를 사전에 발견할 수 있습니다.

56장. 조인 선택을 위한 실전 질문#

조인 성능을 분석할 때는 다음을 확인합니다.

  1. 결과 한 행은 무엇을 의미하는가?
  2. 조인 키는 어느 쪽에서 유일한가?
  3. 1:1, 1:N, N:M 중 어떤 관계인가?
  4. 필터 후 각 입력은 몇 행인가?
  5. 옵티마이저 예상 행과 실제 행은 얼마나 다른가?
  6. Nested Loop라면 안쪽 loops는 몇 번인가?
  7. 안쪽 탐색에 적절한 인덱스가 있는가?
  8. Merge Join이라면 정렬은 어디서 만들어지는가?
  9. 정렬이 메모리 안에서 끝나는가?
  10. Hash Join이라면 Build 입력은 얼마나 큰가?
  11. Batches와 spill이 발생하는가?
  12. 같은 키의 데이터 편중은 심한가?
  13. 조인 뒤 행 수가 얼마나 증가하는가?
  14. 조인 후 집계가 중복 계산되지 않는가?
  15. 같은 결과를 기준으로 전후 성능을 비교했는가?

57장. 세 조인 방식의 핵심을 한 문장으로 정리하면#

Nested Loop:

바깥 행마다 안쪽을 얼마나 싸게 찾을 수 있는가?

Merge Join:

두 입력을 조인 키 순서로 준비하는 비용이 얼마나 드는가?

Hash Join:

한쪽 입력으로 만든 해시 구조를 메모리 안에서 얼마나 효율적으로 유지할 수 있는가?

이 세 질문으로 각 조인 방식의 본질을 이해할 수 있습니다.

58장. 조인 알고리즘보다 더 중요한 질문#

조인 계획을 볼 때 다음 질문부터 하는 것이 좋습니다.

왜 이 입력이 이렇게 커졌는가?

왜 같은 키가 이렇게 많이 반복되는가?

왜 안쪽 노드가 수십만 번 호출되는가?

왜 조인 뒤 행 수가 예상보다 많아졌는가?

이 질문에 답하지 않고 조인 알고리즘 이름만 바꾸면 근본 원인을 놓칠 수 있습니다.

59장. 실행 계획 비교 기록 예시#

항목 변경 전 변경 후
조인 방식 Nested Loop Hash Join
바깥 실제 행 300,000 300,000
안쪽 loops 300,000 1 Build
버퍼 접근 420,000 95,000
임시 디스크 0 0
결과 행 300,000 300,000
실행 시간 2.8초 0.7초

이런 기록이라면 결과는 그대로 유지하면서 실제 반복 작업을 줄였다고 설명할 수 있습니다.

반대로 결과 행 수까지 달라졌다면 먼저 SQL 의미가 동일한지 확인해야 합니다.

60장. 핵심 정리#

SQL 조인의 성능을 이해하려면 두 가지를 반드시 분리해야 합니다.

첫 번째는 어떤 행이 결과가 되어야 하는가입니다.

두 번째는 그 결과를 어떤 물리적인 방법으로 만들 것인가입니다.

대표적인 물리 조인 방식은 다음과 같습니다.

Nested Loop Join

Merge Join

Hash Join

Nested Loop는 바깥 입력의 각 행마다 안쪽 입력을 찾습니다.

따라서:

바깥 실제 행 수
×
안쪽 탐색 비용

이 중요합니다.

안쪽 접근이 인덱스로 매우 싸고 바깥 결과가 작다면 효율적일 수 있습니다.

반대로 예상보다 바깥 행이 커지면 반복 비용이 급격하게 증가할 수 있습니다.

Merge Join은 두 입력을 조인 키 순서로 맞춰 진행합니다.

따라서:

입력이 이미 정렬되어 있는가?

정렬을 새로 해야 하는가?

같은 키가 얼마나 반복되는가?

를 확인해야 합니다.

Hash Join은 한쪽 입력으로 해시 테이블을 만들고 다른 입력을 탐색합니다.

핵심은:

Build 입력 크기

메모리 사용량

Batches

spill 여부

입니다.

하지만 가장 중요한 것은 어떤 조인 알고리즘이 빠르냐가 아닙니다.

조인 결과 자체가 올바른지부터 확인해야 합니다.

주문 3건
×
연락처 2건
=
6행

은 알고리즘 오류가 아니라 데이터 관계의 결과입니다.

이 상태에서 금액을 그대로 합산하면 실제 주문 합계가 두 배로 계산될 수도 있습니다.

따라서 조인 성능 분석은 항상 다음 순서로 진행하는 것이 좋습니다.

관계 확인
↓
예상 결과 행 수 계산
↓
실제 결과 검증
↓
실행 계획 확인
↓
반복·정렬·해시 비용 비교
↓
같은 결과를 기준으로 재측정

좋은 조인 튜닝은 특정 알고리즘을 고집하는 것이 아닙니다.

같은 결과를 유지하면서 불필요한 반복 탐색, 정렬, 메모리 사용과 데이터 이동을 줄이는 것입니다.

이 페이지의 목차