[PostgreSQL] 조인 알고리즘 3종 — Nested Loop·Hash·Merge를 플래너가 언제 고르나

설치·접속: PostgreSQL 설치와 접속

부제: 같은 두 테이블 조인인데 어떤 쿼리는 Nested Loop, 어떤 건 Hash Join으로 잡힐 때 그 판단 기준

-- (A) 한쪽이 아주 좁다: user_id 한 명의 주문만
EXPLAIN (ANALYZE, COSTS OFF)
SELECT o.id, u.name FROM m5_orders o JOIN m5_users u ON u.id=o.user_id
WHERE o.user_id = 12345;

-- (B) 양쪽 대부분을 맞붙인다: 전체 주문 × 전체 회원
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM m5_orders o JOIN m5_users u ON u.id=o.user_id;

-- (A) Nested Loop (rows=40)  0.196 ms
 Nested Loop
   ->  Index Scan using m5_users_pkey on m5_users u   (Index Cond: id=12345, rows=1)
   ->  Bitmap Heap Scan on m5_orders o                (user_id=12345, rows=40)
-- (B) Parallel Hash Join (rows=2,000,000)  180 ms
 Parallel Hash Join   (Hash Cond: o.user_id = u.id)
   ->  Parallel Seq Scan on m5_orders o     (rows=666667 ×3)
   ->  Parallel Hash on m5_users u          (Memory Usage: 2528kB, Batches: 1)

세 알고리즘은 "몇 행을 맞붙이느냐"로 갈린다. Nested Loop은 바깥 행 하나마다 안쪽을 인덱스로 찔러본다 — (A)처럼 바깥이 1건(user_id 한 명)이고 안쪽에 인덱스가 있으면 최고. 하지만 바깥이 100만 건이면 100만 번 반복이라 최악이다. Hash Join은 작은 쪽(m5_users)으로 메모리에 해시 테이블을 만들고 큰 쪽을 한 번 훑으며 조회한다 — (B)처럼 양쪽 대량을 등호(=)로 붙일 때 지배적이다. Batches: 1은 해시가 work_mem 안에 다 들어갔다는 뜻(넘치면 디스크로 분할). Merge Join은 양쪽을 정렬해 지퍼처럼 맞물린다 — 두 입력이 이미 인덱스로 정렬돼 있거나 부등호 범위 조인일 때 유리하다. 즉 플래너는 임의로 고르는 게 아니라 예상 행 수와 인덱스·정렬 상태를 보고 비용이 가장 싼 것을 택한다. 엉뚱한 알고리즘이 잡혔다면 대개 행 수 추정이 틀린 것이라 ANALYZE부터 의심한다.

이렇게도 쓴다

Merge Join을 강제로 유도해 정렬 기반 조인을 관찰한다. (조합: enable_ 플래그)

SET enable_hashjoin=off; SET enable_nestloop=off;
EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM m5_orders o JOIN m5_users u ON u.id=o.user_id;
RESET enable_hashjoin; RESET enable_nestloop;

 

Nested Loop이 100만 번 반복하며 폭주하는지 loops로 확인한다. (조합: EXPLAIN loops)

EXPLAIN (ANALYZE) SELECT * FROM m5_orders o JOIN m5_users u ON u.id=o.user_id WHERE u.grade='vip';

 

조인 키에 인덱스가 없으면 Hash로 쏠린다 — 인덱스를 주면 선택지가 는다. (조합: 조인 키 인덱스)

CREATE INDEX idx_m5_o_uid ON m5_orders(user_id);

 

Hash가 work_mem을 넘겨 Batches가 여러 개로 쪼개지는지 본다. (조합: work_mem)

SET work_mem='64kB';
EXPLAIN (ANALYZE) SELECT count(*) FROM m5_orders o JOIN m5_users u ON u.id=o.user_id;
RESET work_mem;

 

특정 조인만 Nested Loop을 막아 대안 계획의 비용을 비교한다. (조합: 단발 튜닝)

SET enable_nestloop=off;
EXPLAIN SELECT * FROM m5_orders o JOIN m5_users u ON u.id=o.user_id WHERE o.user_id=1;
RESET enable_nestloop;

 

추정 행 수와 실제(rows)가 크게 어긋나면 통계를 다시 모은다. (조합: ANALYZE)

ANALYZE m5_orders; ANALYZE m5_users;