숏컷 코드
WITH recent_posts AS (
SELECT id, user_id, title
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) AS post_count
FROM recent_posts
GROUP BY user_id;문법
CTE 는 복잡한 쿼리를 단계별로 분리해 의도를 명확히 한다
WITH name AS (...) 은 뒤따르는 본문 쿼리에서 그 이름을 테이블처럼 참조할 수 있게 해 줍니다. 깊이 중첩된 서브쿼리와 달리 각 단계에 의미 있는 이름을 붙일 수 있어 쿼리의 흐름이 글처럼 읽힙니다. 여러 CTE 를 순서대로 나열하면 "활성 사용자를 먼저 추린 뒤, 그 중 최근 7일 글을 쓴 사람을 다시 필터링한다"처럼 분석 쿼리의 단계를 자연스럽게 표현할 수 있습니다.
WITH active_users AS (
SELECT id, name
FROM users
WHERE active = true
),
recent_writers AS (
SELECT DISTINCT user_id
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT u.id, u.name
FROM active_users AS u
JOIN recent_writers AS rw ON rw.user_id = u.id;CTE와 서브쿼리는 둘 다 중간 결과를 만들지만 읽는 흐름이 다르다
CTE는 쿼리를 위에서 아래로 단계별로 읽게 만들고, 서브쿼리는 필요한 위치에 바로 끼워 넣는 방식입니다. 여러 단계를 이름 붙여 설명해야 하면 CTE가 더 읽기 쉽고, 한 번만 쓰는 짧은 중첩이라면 서브쿼리가 더 짧을 수 있습니다.
-- CTE: 단계에 이름 붙이기
WITH recent_posts AS (
SELECT *
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT COUNT(*) FROM recent_posts;
-- 서브쿼리: 짧게 한 번만 감싸기
SELECT COUNT(*)
FROM (
SELECT *
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
) AS recent_posts;PostgreSQL 12 이전 CTE 는 항상 물리적 실체화로 최적화 장벽이 됐다
PostgreSQL 12 이전에는 CTE 가 항상 별도 임시 결과로 실체화(materialize)됐습니다. 본문 쿼리의 필터 조건이 CTE 안쪽으로 밀려 들어가지 않았고, 인덱스를 활용할 기회도 없었습니다. PostgreSQL 12 부터는 CTE 가 한 번만 참조되고 부수효과(쓰기 연산)가 없으면 옵티마이저가 인라인 처리(subquery fold)를 선택해 이 제약이 사라졌습니다. MATERIALIZED / NOT MATERIALIZED 힌트로 동작을 명시적으로 제어할 수도 있습니다.
-- PostgreSQL 12+: 옵티마이저가 자동으로 인라인 처리 가능
WITH filtered AS (
SELECT id, title FROM posts WHERE user_id = 42
)
SELECT * FROM filtered WHERE id > 100;
-- 강제 실체화: 중간 결과를 캐시하고 싶을 때
WITH MATERIALIZED expensive_result AS (
SELECT ... FROM large_table WHERE ...
)
SELECT * FROM expensive_result
UNION ALL
SELECT * FROM expensive_result WHERE ...; -- 두 번 참조, 한 번만 실행
-- 강제 인라인: 실체화 비용을 피하고 싶을 때
WITH NOT MATERIALIZED simple AS (
SELECT id FROM users WHERE active = true
)
SELECT * FROM simple WHERE id > 1000;CTE 는 윈도 함수 결과를 필터링하거나 다단계 집계를 표현할 때 가장 유용하다
윈도 함수는 WHERE 절에서 직접 참조할 수 없습니다. CTE 로 한 단계 감싸면 윈도 함수 결과를 다음 단계의 필터 조건으로 쓸 수 있습니다. 다단계 집계(전체 집계 → 다시 집계하는 패턴)에서도 CTE 를 단계별로 나누면 중간 결과를 검증하기 쉽습니다.
-- 윈도 함수 결과 필터링 패턴
WITH ranked_posts AS (
SELECT
id, user_id, title, created_at,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY created_at DESC
) AS rn
FROM posts
)
SELECT id, user_id, title
FROM ranked_posts
WHERE rn = 1;CTE와 JOIN은 대체 관계가 아니라 역할이 다르다
CTE는 단계를 나누는 도구이고, JOIN은 관계를 결합하는 도구입니다. "여러 테이블을 붙이는 작업" 자체는 여전히 JOIN이 맡고, 그 앞뒤를 읽기 좋게 분리할 때 CTE가 붙습니다. CTE만으로 관계 표현을 대신하려고 하면 오히려 같은 테이블을 여러 번 훑는 구조가 되기 쉽습니다.
WITH recent_posts AS (
SELECT id, user_id
FROM posts
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT u.id, u.name
FROM users AS u
JOIN recent_posts AS rp ON rp.user_id = u.id;쓰기 연산(INSERT / UPDATE / DELETE)을 포함하는 CTE 는 실체화가 보장된다
CTE 안에 INSERT, UPDATE, DELETE 가 있으면 PostgreSQL 은 항상 실체화합니다. 이런 데이터 수정 CTE 는 부수효과가 있으므로 최적화 대상에서 제외됩니다. RETURNING 과 결합하면 쓰기 결과를 다음 단계로 넘기는 파이프라인을 단일 쿼리로 표현할 수 있습니다.
-- 쓰기 + RETURNING 파이프라인
WITH inserted_user AS (
INSERT INTO users (email, name)
VALUES ('new@example.com', 'New User')
RETURNING id
)
INSERT INTO user_settings (user_id, theme)
SELECT id, 'default'
FROM inserted_user;선택 기준
| 상황 | 적합한 선택 |
|---|---|
| 중첩 서브쿼리를 읽기 좋게 나눌 때 | WITH name AS (...) CTE |
| 윈도 함수 결과로 행 필터링이 필요할 때 | CTE 로 감싸고 바깥에서 WHERE |
| CTE 를 여러 번 참조하고 계산 비용이 클 때 | WITH MATERIALIZED ... 강제 실체화 |
| CTE 가 한 번만 쓰이고 인라인 최적화가 필요할 때 | WITH NOT MATERIALIZED ... (PostgreSQL 12+) |
| 쓰기 결과를 다음 쓰기 입력으로 연결할 때 | CTE + RETURNING 파이프라인 |
주의할 점
PostgreSQL 12 이전에서는 CTE 가 항상 실체화되어 내부 필터가 바깥으로 전파되지 않습니다.
이 경우 CTE 를 쓰면 인덱스를 타지 못하는 성능 문제가 생길 수 있습니다.
PostgreSQL 12 이상에서도 CTE 를 여러 번 참조하면서 MATERIALIZED 를 쓰지 않으면 각 참조마다 재실행될 수 있으므로
비용이 큰 CTE 는 WITH MATERIALIZED 로 명시하는 것이 안전합니다.
WITH filtered AS (
SELECT * FROM posts
)
SELECT * FROM filtered WHERE user_id = 42;단순한 한 단계 쿼리를 습관적으로 CTE로 감싸면 읽기만 길어질 수 있습니다. 단계 이름이 실제 의미를 더해 주지 못하면 오히려 바로 쓴 쿼리가 더 낫습니다.
WITH posts AS (
SELECT id, user_id
FROM posts
)
SELECT * FROM posts;CTE 이름을 실제 테이블 이름과 똑같이 짓거나 너무 포괄적으로 짓기 시작하면, 본문 쿼리를 읽을 때 어느 posts를 말하는지 바로 헷갈립니다. CTE 이름은 recent_posts, ranked_posts처럼 단계의 의미가 드러나게 두는 편이 좋습니다.
참고 링크
1 sources