PostgreSQL CTE materialization 기준

PostgreSQL의 WITH 절은 복잡한 SQL을 여러 조각으로 나누어 읽기 쉽게 만드는 도구다. 하지만 CTE(Common Table Expression)는 단순한 문법 정리 이상의 의미도 갖는다. PostgreSQL에서는 CTE가 부모 쿼리 안으로 접히기도 하고, 별도로 계산된 임시 결과처럼 materialize되기도 한다.

기준은 명확하다. 한 번만 참조되는 부작용 없는 비재귀 SELECT CTE는 기본적으로 부모 쿼리와 함께 최적화될 수 있다. 반대로 여러 번 참조되는 CTE는 기본적으로 한 번 계산된 결과를 재사용하는 쪽으로 동작한다. 이 기본 판단을 바꾸고 싶을 때 MATERIALIZED와 NOT MATERIALIZED를 사용한다.

CTE는 언제 최적화 경계가 될까?

PostgreSQL 공식 문서 기준으로 WITH 쿼리의 유용한 성질 중 하나는 부모 쿼리에서 여러 번 참조되어도 보통 한 번만 평가된다는 점이다. 비용이 큰 계산을 여러 곳에서 재사용하거나, 부작용이 있는 함수가 여러 번 실행되는 것을 피하고 싶을 때 이 성질이 도움이 된다.

다만 이 성질에는 반대쪽 비용도 있다. 여러 번 참조되는 CTE가 별도로 계산되면 부모 쿼리의 WHERE 조건이 CTE 내부로 내려가지 못할 수 있다. 그러면 실제로 필요한 행은 일부인데도 CTE 전체 결과를 먼저 만든 뒤 바깥에서 버리는 형태가 될 수 있다.

따라서 CTE를 볼 때는 “읽기 쉽게 나눈 이름 있는 서브쿼리”인지, “일부러 한 번 계산하고 재사용하려는 결과”인지 구분해야 한다. 같은 WITH 문법이라도 실행 계획 관점에서는 두 의미가 달라질 수 있다.

기본 동작은 어떻게 나뉠까?

PostgreSQL 18 문서는 부작용 없는 비재귀 SELECT CTE를 부모 쿼리 안으로 접을 수 있다고 설명한다. 여기서 부작용이 없다는 말은 SELECT 안에 volatile 함수가 없는 경우를 뜻한다. 기본적으로 부모 쿼리가 그 CTE를 한 번만 참조하면 접히고, 여러 번 참조하면 접히지 않는다.

상황	기본 동작	확인할 기준
비재귀, 부작용 없음, 한 번 참조	부모 쿼리로 접힐 수 있음	바깥 조건이 내부 scan에 적용되는지 본다.
비재귀, 부작용 없음, 여러 번 참조	기본적으로 materialize	중복 계산을 피하는 이득과 조건 pushdown 손실을 비교한다.
재귀 CTE	재귀 평가 규칙을 따름	RECURSIVE, 종료 조건, cycle 처리를 따로 본다.
데이터 변경 CTE	별도 규칙 적용	RETURNING 결과와 실행 순서 제약을 확인한다.

MATERIALIZED는 언제 쓸까?

MATERIALIZED는 CTE를 부모 쿼리와 합치지 말고 별도 계산하도록 강제하는 표시다. CTE 결과를 명시적으로 한 번 계산하고 싶거나, 부모 쿼리의 최적화가 CTE 내부로 들어오는 것을 막고 싶을 때 검토한다.

WITH expensive_rows AS MATERIALIZED (
  SELECT id, very_expensive_function(value) AS score
  FROM events
)
SELECT a.id, b.id
FROM expensive_rows a
JOIN expensive_rows b ON a.score = b.score;

문서가 드는 대표 예시는 비용이 큰 함수 계산이다. CTE를 두 번 참조하는데 NOT MATERIALIZED로 접히면 같은 계산이 참조마다 반복될 수 있다. 계산 비용이 크고 전체 결과를 재사용하는 쪽이 낫다면 materialize가 자연스럽다.

다만 MATERIALIZED는 성능을 보장하는 마법 키워드가 아니다. 별도 결과를 만들면 메모리나 임시 파일 비용이 생길 수 있고, index 조건이 원본 테이블 scan으로 직접 내려가지 못할 수 있다. 그래서 “중복 계산 방지”가 이득인지, “조건 pushdown과 index 활용”이 더 중요한지 실행 계획으로 확인해야 한다.

NOT MATERIALIZED는 언제 쓸까?

NOT MATERIALIZED는 CTE를 부모 쿼리와 합쳐 함께 최적화하도록 강제하는 표시다. 특히 여러 번 참조되는 CTE라도 각 참조가 CTE 전체가 아니라 작은 일부만 필요로 할 때 유리할 수 있다.

WITH w AS NOT MATERIALIZED (
  SELECT *
  FROM big_table
)
SELECT *
FROM w AS w1
JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

PostgreSQL 문서의 예시처럼 큰 테이블을 CTE로 감싼 뒤 자기 자신과 조인하면, 기본 materialization은 큰 임시 결과를 만들고 index 이점을 잃을 수 있다. 이때 NOT MATERIALIZED를 쓰면 바깥 조건이 원본 테이블 scan으로 내려가고, 조건에 맞는 행만 읽는 계획이 가능해진다.

반대로 CTE 안에 비싼 계산이 있고 그 결과를 여러 번 재사용한다면 NOT MATERIALIZED가 손해일 수 있다. 같은 CTE를 여러 위치에서 참조하는 쿼리는 “원본 테이블 필터링 이득”과 “중복 계산 비용” 중 어느 쪽이 더 큰지에 따라 답이 달라진다.

EXPLAIN으로 무엇을 봐야 할까?

판단은 실행 계획에서 시작하는 편이 안전하다. PostgreSQL의 EXPLAIN은 쿼리 실행 계획을 보여 주고, EXPLAIN ANALYZE는 실제 실행까지 수행해 걸린 시간과 실제 row 수를 함께 보여 준다. 쓰기 쿼리에 ANALYZE를 붙이면 실제 변경이 일어나므로, 필요하면 트랜잭션 안에서 테스트하고 롤백해야 한다.

EXPLAIN (ANALYZE, BUFFERS)
WITH w AS NOT MATERIALIZED (
  SELECT * FROM big_table
)
SELECT *
FROM w
WHERE key = 123;

확인할 지점은 세 가지다. 첫째, CTE 전체를 먼저 만드는 계획인지 원본 테이블 조건 scan으로 접혔는지 본다. 둘째, 실제 row 수가 예상 row 수와 크게 어긋나는지 확인한다. 셋째, 임시 파일이나 buffer 사용이 늘어나는지 본다. 키워드 하나보다 중요한 것은 실제 데이터 분포와 참조 방식이다.

읽기 좋은 SQL과 빠른 SQL 사이

CTE를 쓰면 긴 SQL을 단계별 이름으로 나눌 수 있어 유지보수성이 좋아진다. 하지만 PostgreSQL 12 이후의 CTE는 항상 고정된 최적화 경계라고 보는 것도, 항상 인라인된다고 보는 것도 모두 부정확하다. 기본 동작은 참조 횟수와 부작용 여부에 따라 달라진다.

실무 기준은 간단하게 잡을 수 있다. 가독성을 위해 한 번만 참조하는 CTE는 먼저 기본 형태로 작성한다. 여러 번 참조하면서 원본 테이블의 일부만 필요하다면 NOT MATERIALIZED를 후보로 둔다. 비싼 계산을 한 번만 수행하고 그 결과를 재사용해야 한다면 MATERIALIZED를 후보로 둔다. 최종 선택은 EXPLAIN 결과와 실제 데이터 규모로 확인한다.

FAQ

CTE를 쓰면 항상 느려지나?

항상 그렇지는 않다. PostgreSQL은 부작용 없는 비재귀 CTE를 부모 쿼리와 함께 최적화할 수 있다. 특히 한 번만 참조되는 CTE는 기본적으로 접힐 수 있다.

NOT MATERIALIZED를 기본으로 붙이면 좋을까?

그렇지 않다. NOT MATERIALIZED는 조건 pushdown과 index 활용에 도움이 될 수 있지만, CTE 내부 계산이 참조마다 반복될 위험도 있다. 여러 번 재사용하는 비싼 계산에는 손해가 될 수 있다.

MATERIALIZED는 임시 테이블과 같은가?

영구 객체를 만드는 CREATE TEMP TABLE과는 다르다. 여기서는 해당 쿼리 실행 안에서 CTE 결과를 별도 계산 대상으로 다룬다는 의미에 가깝다.

참고 자료

• PostgreSQL 공식 문서: WITH Queries
• PostgreSQL 공식 문서: SELECT
• PostgreSQL 공식 문서: Using EXPLAIN