CodeGym /행동 /SQL SELF /CTE 사용할 때 흔히 하는 실수와 피하는 방법

CTE 사용할 때 흔히 하는 실수와 피하는 방법

SQL SELF

레벨 28 , 레슨 4

사용 가능

이제 CTE 쓸 때 생기는 어두운 면, 즉 흔히 하는 실수들 얘기해볼게. 아무리 멋진 쿼리여도, 이 강력한 도구를 잘못 쓰면 망가질 수 있어. 근데 걱정하지 마! 여기 실수 찾고 막는 방법 다 정리해놨어!

1. 실수: CTE materialization과 그 후폭풍

PostgreSQL에서 CTE 쓸 때 제일 중요한 특징 중 하나가 기본적으로 materialization 된다는 거야. 이게 무슨 뜻이냐면, CTE 결과가 한 번 처리돼서 메모리(아니면 데이터 많으면 디스크)에 잠깐 저장된다는 거지. 쿼리가 많거나 데이터가 크면, 이게 진짜 느려질 수 있어.

예시:

WITH heavy_data AS (
    SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;

딱 보면 CTE가 그냥 데이터 필터링하는 것 같지? 근데 실제로는 heavy_data가 먼저 다 불러와서 materialization 되고, 그 다음에야 필터링이 들어가. 시간 엄청 잡아먹을 수 있어.

어떻게 피하지?

PostgreSQL 12부터는 CTE를 inline expression (subquery처럼)으로 쓸 수 있어서 materialization 문제를 해결할 수 있어. 한 번만 쓰고 중간 결과 저장 필요 없으면 이렇게 쓰면 돼.

최적화된 예시:

WITH inline_data AS MATERIALIZED (
    SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;

팁: materialization이 꼭 필요하면 MATERIALIZED 쓰고, 아니면 NOT MATERIALIZED 써!

2. 실수: recursive CTE가 무한루프 도는 경우

recursive CTE 진짜 강력한데, 반복 횟수 제한 없이 쓰면 무한루프에 빠질 수 있어. 이러면 쿼리도 느려지고, 리소스도 다 잡아먹어.

예시:

WITH RECURSIVE endless_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM endless_loop
)
SELECT * FROM endless_loop;

이 쿼리는 멈출 조건이 없어서, 계속해서 무한대로 row를 만들어내.

어떻게 피하지?

WHERE로 명확하게 멈추는 조건을 넣어줘. 예를 들면:

WITH RECURSIVE limited_loop AS (
    SELECT 1 AS value

    UNION ALL

    SELECT value + 1
    FROM limited_loop
    WHERE value < 10
)
SELECT * FROM limited_loop;

팁: 큰 hierarchy에서 recursive CTE 쓸 땐, PostgreSQL의 max_recursion_depth 옵션으로 recursion 깊이 제한해!

3. 실수: `UNION`이랑 `UNION ALL` 잘못 쓰는 경우

CTE에서 base 쿼리랑 recursive 쿼리 합칠 때, UNION이랑 UNION ALL 잘못 고르면 결과가 이상해질 수 있어. 예를 들어, UNION은 중복 row를 없애는데, 이게 추가 연산도 필요하고, 중요한 row가 사라질 수도 있어.

예시:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION  -- 여기선 UNION ALL이 더 좋아

    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

여기서 UNION 쓰면, hierarchy에서 중복된 row가 실수로 날아갈 수도 있고, 쿼리도 느려져!

어떻게 고치지?

중복 제거가 꼭 필요하지 않으면 UNION ALL 써:

UNION ALL

4. 실수: 한 쿼리에 CTE를 너무 많이 쓰는 경우

쿼리를 구조적으로 만들려고 하다 보면, CTE를 막 10개, 20개씩 넣는 경우가 있어. 이러면 코드도 헷갈리고, PostgreSQL 쿼리 플래너도 힘들어져.

예시:

WITH cte1 AS (...),
     cte2 AS (...),
     cte3 AS (...),
     ...
     cte20 AS (...)
SELECT ...
FROM cte20;

이건 진짜 개발자 악몽이야.

어떻게 고치지?

— 쿼리를 여러 개의 더 간단한 쿼리로 나눠. 한 번에 다 하려고 하지 말고, 독립적인 쿼리 여러 개로 쪼개!

— 중간 결과를 여러 번 써야 한다면, 임시 테이블에 저장하는 것도 방법이야.

5. 실수: 인덱스 없이 복잡한 CTE 쓰는 경우

CTE에서 데이터가 많을 때, 테이블에 인덱스 안 달면 쿼리 엄청 느려져. 인덱스는 DB의 doping 같은 거야.

예시:

WITH filtered_data AS (
    SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;

어떻게 고치지?

CTE 쓰기 전에 테이블이 최적화됐는지 꼭 확인해:

CREATE INDEX idx_large_table ON large_table(unindexed_column);

6. 실수: CTE로 여러 번 데이터 호출하려는 경우

CTE는 한 번 만들어지고, 실행되고, 그 결과가 "고정"돼. 여러 군데에서 결과를 쓰고 싶어도, 데이터가 다시 계산되지 않아 — 이게 가끔 문제를 일으켜.

예시:

WITH data AS (
    SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- 만약 data를 다시 계산해야 하면, 그건 안 돼.

어떻게 고치지?

동적으로 다시 계산하거나, 결과가 계속 바뀌어야 한다면 CTE 말고 subquery를 써.

7. 실수: 주석이 없는 경우

CTE 진짜 좋은 도구지만, 복잡한 SQL 쿼리에 주석 없으면, 2주 뒤에 본인도 무슨 쿼리인지 모를걸?

예시:

WITH data_filtered AS (
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

한 달 뒤엔 왜 이 데이터 필터링했는지 아무도 기억 못 해!

특히 복잡하거나 recursive CTE 쓸 땐 꼭 주석 달아줘:

WITH data_filtered AS (
    -- some_column > 100 조건으로 데이터 필터링
    SELECT *
    FROM large_table
    WHERE some_column > 100
)
SELECT * FROM data_filtered;

8. 실수: 임시 테이블 대신 CTE 남용하는 경우

가끔은 임시 테이블이 훨씬 나아. 예를 들어, 결과를 여러 쿼리에서 반복해서 쓰거나, 데이터셋이 엄청 클 때.

예시:

WITH temp_data AS (
    SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;

이렇게 하면 CTE 쿼리가 두 번 실행돼, 데이터는 안 바뀌는데도!

어떻게 고치지?

데이터를 여러 번 써야 한다면, 임시 테이블 만들어:

CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;

SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;