PostgreSQL CTE의 MATERIALIZED: 재사용과 조건 적용의 차이

PostgreSQL 18에서 비재귀적이고 volatile 함수를 포함하지 않은 SELECT CTE는 상위 쿼리와 결합해 최적화할 수 있습니다. 한 번 참조하면 기본적으로 결합하고, 여러 번 참조하면 기본적으로 별도 계산을 유지합니다.

MATERIALIZED는 별도 계산을, NOT MATERIALIZED는 가능한 경우 결합을 지정합니다. 어느 쪽이 항상 더 빠른지는 정해져 있지 않습니다. 필요한 행만 일찍 좁히는 이점과 같은 계산을 반복하는 비용을 나누어 살펴야 합니다.

CTE라는 이름과 물리적 실행을 구분하기

WITH로 중간 결과에 이름을 붙였다고 항상 디스크의 임시 테이블을 만든다고 생각하면 안 됩니다. CTE는 쿼리를 읽기 좋게 나누는 표현이고, 실제 실행 방식은 별도 문제입니다. 비교 대상은 재귀, 데이터 변경, volatile 함수가 없는 SELECT입니다. 적용 전제를 제외하고 '한 번이면 무조건 합친다'라고 외우지 마세요.

별도 계산은 두 참조가 공통 결과를 나눠 쓰도록 할 수 있지만, 각 참조의 바깥 조건을 원래 데이터까지 내려 보내는 데 제약이 생길 수 있습니다. 결합은 반대의 이점을 줄 수 있습니다. '결과를 공유한다'와 '조건을 일찍 적용한다'라는 두 축을 그려 놓으면 옵션의 철자가 기억나지 않아도 선택 이유를 설명할 수 있습니다.

지점 두 곳의 금액을 비교하는 자작 예제

WITH w AS MATERIALIZED (
  SELECT branch_id, product_id, amount
  FROM branch_amount
)
SELECT a.product_id, b.amount - a.amount AS difference
FROM w AS a
JOIN w AS b ON a.product_id = b.product_id
WHERE a.branch_id = 10 AND b.branch_id = 20
ORDER BY a.product_id;

먼저 성능 이야기를 잠시 빼고 결과를 계산합니다. 상품 1은 130−100=30, 상품 2는 180−200=−20입니다. 지점 30의 상품 3은 어느 쪽 조건에도 맞지 않습니다. 두 행의 결과를 적은 뒤 MATERIALIZED를 NOT MATERIALIZED로 바꾸어 봅니다. 여기처럼 단순한 SELECT에서는 바뀌는 관심사가 반환값이 아니라 계산을 묶는 방식입니다.

MATERIALIZED에서는 공통 SELECT를 별도로 계산해 두 참조가 읽습니다. NOT MATERIALIZED로 결합되면 각 참조의 지점 조건을 원래 표의 읽기에 적용할 여지가 생깁니다. 큰 표에서 지점 10과 20이 아주 일부라면 이 차이를 살펴볼 가치가 있습니다. 하지만 이 다섯 행만으로 더 빠른 쪽이나 인덱스 사용 여부를 측정한 것처럼 결론 내릴 수는 없습니다.

계산이 비싸면 반대 선택도 검토하기

창작 상황을 바꾸어 상품마다 부작용 없는 비용 큰 계산의 점수를 만든 뒤 그 결과를 두 군데에서 비교한다고 생각해 보세요. 원래 표의 많은 행을 두 참조 모두 필요로 한다면 결합 과정에서 같은 계산을 반복하는 것이 불리할 수 있습니다. 별도 계산은 이런 중복을 줄이는 선택이 될 수 있습니다. 조건을 줄이는 이점만 보고 NOT MATERIALIZED를 모든 CTE에 붙이는 것은 적절하지 않습니다.

비용 큰 계산이라는 설명만으로 함수를 실제 작성하거나 실행한 것은 아닙니다. 판단 연습에서는 '참조마다 필요한 원래 행의 비율'과 '한 행을 계산하는 비용'을 두 칸에 적어 보세요. 필요한 행이 적고 계산이 가벼운 경우와, 대부분의 행을 쓰고 계산이 비싼 경우는 같은 결론을 미리 내릴 이유가 없습니다.

계획을 확인할 때 보는 순서

1. CTE가 비재귀 SELECT이며 volatile 함수를 포함하지 않는지 확인합니다.
2. 같은 CTE의 참조 횟수와 각 참조가 필요한 행의 범위를 적습니다.
3. 별도 계산과 결합 두 쿼리의 결과가 같은지 작은 자작 입력으로 먼저 확인합니다.
4. 연습 DB에서 EXPLAIN으로 CTE Scan과 원래 테이블 접근, 조건 위치를 비교합니다. 특정 계획 노드가 반드시 나온다고 미리 정하지 않습니다.
5. 실제 비용을 측정해야 한다면 EXPLAIN ANALYZE가 질의를 실행한다는 점을 확인합니다. 추정 cost를 밀리초로 읽지 않습니다.

계획의 추정 행 수와 실제 행 수, 반복 횟수는 서로 다른 정보입니다. 실행 시간이 한 번 짧게 나왔다고 모든 데이터에서도 유리하다는 규칙으로 만들지 마세요. 이 글은 PostgreSQL 18 공식 문서와 작은 결과 계산을 기준으로 하며, 운영 데이터의 계획이나 성능을 확인한 보고서는 아닙니다.

재귀 CTE나 부작용 없는 SELECT가 아닌 WITH에 NOT MATERIALIZED를 붙여도 이 글의 결합 규칙을 강제할 수 없습니다. PostgreSQL은 이런 경우 해당 지정을 무시한다고 설명합니다. 복습 답안에는 'CTE는 항상 한 번'이나 'NOT MATERIALIZED는 항상 빠름' 대신 적용 조건과 잃거나 얻는 이점을 함께 적어 보세요.