상관 서브쿼리(Correlated subquery) 와 비상관 서브쿼리(Uncorrelated) 조인에 대해 성능 평

1. 상관 서브쿼리 (Correlated Subquery)

서브쿼리가 바깥 쿼리의 컬럼을 직접 참조합니다.
즉, 바깥 쿼리의 각 행(row)마다 서브쿼리가 실행됩니다 → 반복 실행 때문에 성능 부담이 커질 수 있습니다.

 

-- 각 사원의 급여가 해당 부서의 평균보다 높은 사원 찾기
SELECT e.emp_id, e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

 

 

 

• 여기서 e.department_id는 바깥 쿼리 컬럼을 참조합니다.
• 각 사원의 department_id마다 AVG()가 다시 계산됨 → 실행 횟수 ↑ (loops 많음).

 

2. 비상관 서브쿼리 (Uncorrelated Subquery)

서브쿼리가 바깥 쿼리 컬럼을 전혀 참조하지 않습니다.
따라서 서브쿼리는 한 번만 계산되고, 이후 조인 또는 비교에 활용할 수 있습니다.

 

-- 모든 부서별 평균 급여를 구해두고,
-- 그 평균보다 급여가 높은 사원 찾기
SELECT e.emp_id, e.name, e.salary, e.department_id
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) d
ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

 

 

• 내부 서브쿼리 (SELECT department_id, AVG...)는 한 번만 실행됩니다.
• 그 결과를 바깥 쿼리와 동등 조인(e.department_id = d.department_id) 하므로 Hash Join 등이 가능 → 효율적.

 

 

 

해시 조인이 뭐가 좋은데?

비유로 가자면:

1. 작은 쪽으로 ‘사전(딕셔너리)’을 만든다.
2. 큰 쪽을 한 번 슥 훑으면서 사전에 있는지 O(1)로 확인해서 매칭한다.

즉, 작은 쪽 1번 + 큰 쪽 1번만 보면 끝. (대략 비용이 |작은쪽| + |큰쪽|)

반면 Nested Loop는:

• 바깥행 N개가 나오면 안쪽을 N번 뒤진다.
• 안쪽에 인덱스가 없거나 비싸면 N × (안쪽비용) → 금방 폭발.

Merge Join은:

• 둘 다 조인키로 정렬이 필요(이미 정렬돼 있지 않으면 정렬비용↑).
• 정렬이 비싼 상황에선 해시가 유리.

---

너의 상황을 이 틀에 딱 끼워보면

실제 플랜에서 보였던 핵심 숫자:

• 왼쪽(집계 결과) : 약 15,000행
  Finalize HashAggregate … rows≈15072
• 오른쪽(너가 미리 줄여 놓은 쪽) : 약 19행
  GroupAggregate … rows=19

그리고 조인 조건이 =(동등조인) 이었지:

 

Hash Cond: ("*SELECT* 1".data_collection_log_seq = max(dcl.data_collection_log_seq))

플래너 입장에선 계산이 이렇게 흘러가:

• “오른쪽 19행은 작네 → 이걸로 해시 테이블(build) 만들자.”
• “왼쪽 1.5만 행은 한 번만 훑으면서(probe) 매칭하면 되겠네.”
• “조인키가 =니까 해시로 딱 맞다. 정렬도 필요없고.”

그래서 Hash Join을 고른 거야.

한 문장: 작은 쪽(네가 WITH/그룹으로 줄인 쪽)을 build, 큰 쪽(집계 결과)을 probe → 해시 조인 1패스.

---

왜 예전엔 해시 조인이 안 나왔고, 지금은 나왔나?

Before (느릴 때)

• 바깥에서 **여러 행(예: 19행)**이 나오고,
• 내부 큰 집계를 행마다 다시 돌렸어(Nested Loop 경로).
• 게다가 조인키가 느슨하거나 Join Filter로 빠지면, 플래너가 “해시 한 번 만들어서 끝내기”보다 “그냥 바깥 행마다 내부 돌리기”를 더 싸다고 오판할 수 있어.

After (빠른 지금)

• 바깥을 더 작고 단정하게 만들었어(예: (mall, account, provider)별 최신 log_seq만 남김 → 19/99행 수준).
• 동등조인으로 깔끔하게 붙였어(가능하면 data_collection_log_seq + data_provider_mall_no 같이 키를 더 구체화하면 더 좋음).
• 그러니 플래너가 자연스럽게 작은 쪽 build → 큰 쪽 1패스 probe인 Hash Join을 선택.

---

진짜 초간단 수식 비교

• Nested Loop 대략 비용:
  비용 ≈ |outer| × (inner탐색비용)
  (인덱스 없거나 inner가 집계/스캔이면 위험)
• Hash Join 대략 비용:
  비용 ≈ |작은쪽| + |큰쪽|
  (build 한 번 + probe 한 번)

너의 플랜: 작은쪽=19, 큰쪽≈15000 → 해시 조인이 이길 확률이 매우 큼.

---

플래너(Planner)가 실제로 뭘 보고 결정하냐?

• 조인 조건: =(동등)인지, 범위/함수인지
• 크기 추정: 통계로 본 각 입력의 예상 행수(너는 작은쪽을 확 줄였음)
• 정렬 필요성: 머지조인은 정렬비용 필요, 해시는 필요X
• 인덱스/비용모델: 인덱스 유무, I/O/CPU 추정

이 조합에서 동등조인 + 작은쪽 확 줄임이 나오니 Hash Join을 고른 거야.

---

확인 포인트 (네 플랜에서 이미 보였던 것)

• Hash Join ... loops=1 → 조인 자체가 한 번에 끝남
• Finalize/Partial HashAggregate ... loops=1 → 내부 집계도 한 번만 계산
• (작은쪽) GroupAggregate rows=19 바로 위에 Hash 노드가 생김 → build 쪽
• (큰쪽) 1.5만행 집계 결과가 probe로 스윽 지나감

---

만약 더 유도하고 싶다면(팁)

• 동등조인 키를 충분히 명시(예: data_collection_log_seq + data_provider_mall_no)
• 바깥 입력 더 줄이기(지금처럼 최신 log_seq 확정)
• 비축약 조건 없애기(예: to_char(...) 대신 날짜 범위 조건 → 상류 I/O↓)
• 필요시 WITH ... AS MATERIALIZED/임시테이블로 한 번만 계산 강제
• (테스트용) SET enable_nestloop = off;로 대체 플랜 확인도 가능(운영 반영 X)

---

한 줄로 마무리

“작은 리스트(네가 미리 줄인 쪽)로 해시 테이블을 만들고, 큰 결과를 한 번에 대조하는 게 더 싸다”—플래너는 그걸 숫자로 계산해서 Hash Join을 고른 거고, 그래서 반복 없이 0.2~0.3초가 나온 거야.