CTE를 활용한 쿼리 최적화

오늘은 CTE(Common Table Expressions)를 활용한 쿼리 최적화라는 흥미진진(그리고 살짝 무서울 수도 있는) 세계로 들어가 볼 거야. 이미 CTE 만드는 법은 익혔다면(이전 강의에서 다뤘지), 이제는 그 내부 구조의 디테일, 숨겨진 함정, 그리고 최대한 효율적으로 쓰는 방법에 대해 얘기해볼 차례야.

처음 보면 CTE는 완벽해 보여: 코드가 깔끔하고, 쓰기도 쉽고, 논리적으로 블록을 나눌 수 있지. 하지만 한 가지 작은(혹은 꽤 큰) 포인트가 있어. PostgreSQL은 CTE를 처리할 때 특이한 전략을 쓰는데, 이게 성능에 영향을 줄 수 있어.

PostgreSQL이 WITH를 만나면, 보통 CTE 결과를 materialize(물리화)해. 즉, CTE가 반환하는 데이터를 먼저 계산해서 임시 테이블로 저장하고, 그걸 메인 쿼리에서 사용하는 거지. 재사용에는 편하지만, 이런 경우엔 문제가 될 수 있어:

1. CTE에 담긴 데이터가 엄청 많은데, 실제로는 그 중 일부만 쓰는 경우.
2. CTE가 너무 자주 호출돼서 오버헤드가 커지는 경우.
3. 사실 필요 없는 복잡한 CTE를 괜히 만들어버린 경우.

Materialization이란?

Materialization은 PostgreSQL이 CTE 결과를 메모리나 디스크에 저장하는 과정이야(데이터 크기에 따라 다름). 즉, 데이터는 한 번만 뽑아오지만, CTE를 한 번만 쓰는 거라면 materialization은 오히려 불필요할 수 있어. 예를 들어:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

이 경우 PostgreSQL은 먼저 전체 CTE(grade > 60) 결과로 임시 테이블을 만들고, 그 다음에 grade > 90인 행만 필터링해. 이건 쓸데없는 중간 단계를 추가해서 성능에 영향을 줄 수 있어.

불필요한 materialization 피하기

PostgreSQL 12부터는 필요 없을 때 materialization을 피할 수 있게 됐어. MATERIALIZED(기본값)나 NOT MATERIALIZED 키워드를 쓰면 돼. 예시:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

여기서는 PostgreSQL에게 large_set 데이터를 materialize하지 말고, 쿼리를 메인 쿼리에 바로 인라인하라고 알려주는 거야. 이렇게 하면 중간 테이블이 안 만들어져서 더 효율적이지.

Materialization이 유용할 때?

materialization이 항상 나쁜 건 아니야! CTE 데이터를 쿼리에서 여러 번 쓰거나, 독립적으로 계산해야 할 때는 materialization이 오히려 좋아. 예시:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

여기서는 materialization 덕분에 grade > 60 필터를 여러 번 계산하지 않아도 돼.

인덱스를 활용한 쿼리 최적화

CTE가 더 빠르게 동작하려면, 데이터를 뽑아오는 기본 테이블에 인덱스를 만들어주는 게 좋아. 예를 들어:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

grade 컬럼에 인덱스가 있으면 PostgreSQL이 grade > 90 조건에 맞는 행을 훨씬 빨리 찾아낼 수 있어. 특히 테이블이 크면 더 중요하지.

큰 CTE를 더 작은 단계로 쪼개기

CTE가 많은 데이터를 반환하고, 그걸 또 필터링하거나 집계한다면, 여러 단계로 쪼개는 게 좋아. 복잡한 CTE 하나 대신, 작은 CTE 여러 개로 나누는 거지:

나쁜 예(큰 CTE):

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

더 좋은 예(단계별로 쪼개기):

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

이런 식으로 하면 PostgreSQL이 쿼리 실행을 더 잘 최적화할 수 있어.

실전 예시: 구조 분석과 최적화

좀 더 복잡한 예를 보자. 학생, 강의, 성적 테이블이 있다고 치자. 평균 점수가 높은 학생을 찾아서, 그 학생과 관련된 강의 리스트를 뽑고 싶어:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

이 쿼리는 grades와 enrollments 테이블에 인덱스를 추가하면 필터링과 조인이 더 빨라져서 최적화할 수 있어.

Monitoring: 성능 분석

쿼리가 얼마나 효율적인지 확인하려면 EXPLAIN이나 EXPLAIN ANALYZE를 써봐. 예시:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

이 쿼리는 각 단계가 얼마나 오래 걸리는지 보여주고, 어디서 성능을 개선할 수 있는지 파악하는 데 도움을 줘.

EXPLAIN ANALYZE에 대해서는 다음 단계에서 더 자세히 다뤄볼 거야 :P

CTE 최적화에서 자주 하는 실수

1. 인덱스를 깜빡함. CTE에서 데이터를 필터링하는데, 기본 테이블에 인덱스가 없으면 성능이 확 떨어져.
2. 너무 큰 CTE를 사용함. 한 쿼리에서 너무 많은 걸 하려고 하면, 엄청난 양의 데이터를 materialize하게 돼.
3. NOT MATERIALIZED를 남용함. 어떤 경우엔 materialization이 꼭 필요해서, CTE를 반복 계산하지 않게 해줘야 할 때도 있어.
4. 모니터링을 무시함. EXPLAIN으로 분석 안 해보면, 쿼리가 느린 줄도 모를 수 있어.

이제 CTE를 활용해서 쿼리를 최적화할 준비가 됐어! 함정은 피하고, 성능은 높이고! CTE는 도구일 뿐 만능 해결책은 아니니까, 잘 생각해서 쓰면 PostgreSQL에서 최고의 친구가 되어줄 거야.