SQL: CTE 튜닝

※ SQL: CTE Optimization from the Standard SQL Perspective.
 
안녕하세요. 듀스트림입니다.
 
튜닝 대상을 식별하다 보면 CTE(Common Table Expression)가 많은 쿼리가 항상 존재합니다.
 
이유는 간단합니다. CTE가 많으면 최적화가 제대로 이루어지지 않는 경우가 많기 때문입니다.
그래서 CTE를 단계적으로 하나씩 살펴봐야 하며, 특히 다음과 같은 패턴에서 성능 저하가 자주 발생합니다.

1. 정렬과 집계가 많은 경우
   
   • CTE 내부에서 ORDER BY나 SUM, COUNT 등의 집계 연산이 반복되면, 불필요한 임시 테이블 생성과 디스크 I/O가 증가합니다.
2. 타입 캐스트나 복잡한 표현식 사용
   
   • CAST나 함수 기반 연산은 기존 인덱스를 활용하지 못해 풀 스캔(seq scan)으로 전환될 수 있습니다.
3. CTE별 윈도우 함수 사용
   
   • ROW_NUMBER(), RANK() 같은 윈도우 함수가 CTE별로 계산되면서 메모리와 CPU 부하가 늘어납니다.

 
따라서 CTE가 많은 쿼리를 다룰 때는

• 가능하면 CTE를 인라인화
• 필요한 경우 표현식 기반 인덱스 생성
• 불필요한 정렬/집계 제거 또는 LIMIT 적
• 윈도우 함수 사용 최소화

에 신경쓰면 성능 개선에 큰 도움이 됩니다.

---

핵심 원칙

1. 목표를 명확히 쪼개기
   
   • CTE는 단계적 문제 분해와 가독성을 높여줍니다. 하지만 단계가 많아질수록 옵티마이저의 전역 최적화 여지가 줄어들 수 있습니다.
   • CTE는 가능한 최소 단계만 유지하고, 중복되거나 불필요한 CTE는 제거/병합하는 게 좋습니다.
2. 하위 단계에서 필터·프로젝션·집계를 최대한 줄이기
   
   • 각 CTE 내부에서 WHERE로 행 수를 줄이고, SELECT에는 꼭 필요한 컬럼만 명시해야 하며, 가능한 집계/윈도우를 빨리 적용해 상위 단계의 데이터 부피를 최소화하는 게 좋습니다. (상위 단계에서 필터를 강하게)
3. 다회 참조 CTE는 공통 부분식 제거 효과를 의식
   
   • 동일 CTE를 여러 번 참조하면 DBMS에 따라 인라인된 CTE는 재계산될 수 있습니다.
   • 재사용 빈도가 높고 비용이 큰 중간 결과는 MATERIALIZED로 고정하는 설계도 고려해야 합니다.
4. 정렬/제한(ORDER BY/LIMIT)은 가장 마지막으로
   
   • 중간 단계 CTE에서의 정렬은 비싸고 병렬/푸시다운을 막습니다.
   • 정렬은 진짜 정말 반드시 필요할 때만 해야 하고 가능하면 최후단에서 해야합니다.
5. RECURSIVE(재귀) CTE는 '앵커 최소화 + 엄격한 종료 + 인덱스'
   
   • 앵커(Anchor) 쿼리 결과를 작게해야 합니다.
   • 레벨 제한(level/depth)과 중복 제거 전략에도 신경 써야 합니다.
   • 종료 조건을 촘촘히 설계해야합니다.
   • 조인 키 인덱스도 신경 써야 합니다.
6. 측정-검증 루프
   
   • 실행계획/통계/카디널리티 예측을 확인하고, 단계별 데이터량을 샘플링해 병목(큰 해시/소트/스필)을 찾아서 튜닝 포인트를 잡아야 합니다.

---

튜닝 체크리스트

1. CTE 구조 개선
   • 단일 사용 CTE는 인라인 테스트
     • 한 번만 사용되는 CTE는 서브쿼리 형태로 쿼리 본문에 직접 삽입해 성능을 비교해보시는 것이 좋습니다.
   • 다회 사용·고비용 CTE는 명시적 물리화 테스트
     • PostgreSQL에서는 WITH c AS MATERIALIZED (...)와 NOT MATERIALIZED 옵션을 비교해보면서 어떤 방식이 효율적인지 확인합니다.
     • Oracle에서는 힌트 /*+ MATERIALIZE */와 /*+ INLINE */을 사용하여 비교할 수 있습니다.
     • SQL Server에서는 임시테이블(#t)로 결과를 분리하여 한 번만 계산하고 재사용하는 방법도 있습니다.
   • 불필요한 계층 줄이기
     • CTE가 “CTE → CTE → CTE”처럼 여러 단계로 연결되면, 상위 쿼리에서 조인이나 필터 푸시다운이 제한될 수 있습니다.
     • 가능하다면 상위 단계로 조인·필터를 끌어올리는 리팩터링을 통해 쿼리 구조를 단순화하는 것이 좋습니다.
       
       
2. 데이터량 억제
   • 각 CTE 내부에서는 선필터링(WHERE)과 필요 컬럼만 SELECT하도록 설계합니다.
   • 가능한 경우 초기 집계를 통해 데이터량을 줄이는 것도 중요합니다.
   • DISTINCT나 UNION 같은 연산은 비용이 크므로, 꼭 필요할 때만 사용하며, 조인 키에 인덱스가 있는지 확인해야 합니다.
     
     
3. 조인 및 집계 전략
   • 조인 순서가 성능에 큰 영향을 줄 수 있으므로, 큰 테이블은 가능한 늦게 조인하도록 조정합니다.
   • 조건 검증용 조인은 anti-join 선행 또는 EXISTS/SEMI JOIN으로 처리하면 효율적입니다.
   • 윈도우 함수는 필요한 파티션과 정렬만 적용하고, 중복된 창 정의는 통합하여 한 번만 계산하도록 합니다.
   • GROUP BY는 컬럼 수와 카디널리티를 최소화하여 불필요한 연산을 줄입니다.
     
     
4. 정렬, 제한, 중간 정렬 제거
   • CTE 내부의 ORDER BY는 불필요하면 제거하고, 최종 결과에서만 정렬을 수행하도록 합니다.
   • 상위 쿼리에서 LIMIT이나 TOP을 활용해 조기 중단할 수 있는 구조인지 확인하고, 필요 시 재설계합니다.
     
     
5. 재귀 CTE 튜닝
   • 앵커 쿼리 결과를 최소화하고, 조인 키에 인덱스를 적용하며, 엄격한 종료 조건을 설정합니다.
   • 최대 깊이 제한과 중복/사이클 제거 전략을 반드시 설계해야 합니다.
   • 분기 폭이 큰 경우에는 재귀 CTE 대신 임시 테이블 + 배치 처리로 전환하는 것도 고려해보세요.
     
     
6. 통계와 실행 계획 검증
   • 실행 계획에서 큰 Hash/Sort Spill이나 비정상적인 카디널리티를 찾아내야 합니다.
   • 필요한 컬럼에 통계나 히스토그램을 보강하고, 파티션 프루닝이 정상적으로 작동하는지 확인합니다.
   • 쿼리 변경 시에는 실측을 통해 시간, I/O, 메모리, 재실행 횟수 등을 기록하고 비교하여 개선 효과를 검증합니다.

---

리팩터링 예시

▸ 중간 정렬 제거

-- BEFORE: 중간 CTE에서 ORDER BY
WITH c AS (
  SELECT * FROM fact WHERE cond ORDER BY ts
)
SELECT ... FROM c ORDER BY ts;  -- 중복 정렬

-- AFTER: 최종 단계에서만 정렬
WITH c AS (
  SELECT * FROM fact WHERE cond
)
SELECT ... FROM c ORDER BY ts;

---

▸ RECURSIVE CTE 최적화 골격

WITH RECURSIVE graph AS (
  -- anchor: 작고 선택적
  SELECT id, parent_id, 1 AS depth
  FROM nodes
  WHERE id = :root

  UNION ALL

  -- recursive: 종료 조건과 인덱스 활용
  SELECT n.id, n.parent_id, g.depth + 1
  FROM nodes n
  JOIN graph g ON n.parent_id = g.id
  WHERE g.depth < :max_depth
)
SELECT ...
FROM graph;

---

의사결정 트리

1. CTE가 한 번만 쓰이나?
   → 서브쿼리로 바꿔보고 비교.
   
   
2. CTE를 여러 번 재사용? 고비용?
   → 물리화(임시테이블/힌트/MATERIALIZED) 고려 후 A/B.
   
   
3. 중간에 ORDER BY/DISTINCT 있음?
   → 꼭 필요한지 재검토. 가능하면 최종 단계로 이동.
   
   
4. 재귀?
   → 앵커 축소·종료조건 명시·인덱스·최대깊이.
   
   
5. 측정 결과가 미미?
   → CTE 구조보다 조인 순서/카디널리티/통계 문제일 수 있음. 실행계획 재점검.

---

오늘은 여기까지~
 
+ 아래는 참고용 포스팅 링크.

PostgreSQL(ANSI SQL): CTE와 서브쿼리 비교