흑백과 디지털

나는 복잡한 SQL 쿼리를 사용해야 하는 경우, 쉽게 유지보수하기 위해 아래와 같이 with cte  구문을 개인적으로 많이 사용한다. 물론 실질적으로 Python 에서 sqlalchemy 을 사용하기는 하지만 아래와 같이 코드가 나오도록 하는 편이다. 

with 테이블1 as (
    select ....
),
테이블2 as (
    select ....
),
테이블3 as (
    select ...
)
select ...
    from 테이블4
    left join  테이블1 과 테이블4
    left join  테이블2 과 테이블4
    left join  테이블3 과 테이블4

이렇게 만들어 두면 뭔가 더 해석하기 쉬워지는 느낌이 있다. 물론 테이블1, 테이블2, 테이블3 이런 것 내에서도 내부적으로 left join 이 들어 있을 수 있다. 그리고 이렇게 하는 추가적인 이유는 테이블1, 테이블2, 테이블3 이 row 수가 적당해서 괜찮은 속도를 내 줄거라는 기대도 있는 편이다. 
그런데 오늘 where 조건이 걸렸을 때 늦어지는 현상을 발견했다. 원인을 분석해 봤는데 내 의도대로 테이블1, 테이블2, 테이블3 가 만들어지지 않고 Postgresql 알아서 최적화 하면서 다른 방법으로 동작해지면 속도가 늦어졌다. 

 그래서 이 문제는 google gemini 물었더니 materialized  를 사용하라는 답변을 받았다. (물론 gemini 가 추천해준  SQL구문이 틀렸지만 나에게 도움이 되는 답변이었다.) 이런 구문을 cte materialized  라고 하는 것 같다. 

with 테이블1 as MATERIALIZED (
    select ....
),
테이블2 as MATERIALIZED (
    select ....
),
테이블3 as MATERIALIZED (
    select ...
)
select ...
    from 테이블4
    left join  테이블1 과 테이블4
    left join  테이블2 과 테이블4
    left join  테이블3 과 테이블4

이렇게 해 주면 내 의도대로 임시로 테이블1, 테이블2, 테이블3 를 만든다고 한다. 

만일 sqlalchemy 를 사용한다면 다음 처럼 사용할 수 있다. 

s = select([orders.c.order]).cte("regional_sales")
s = s.prefix_with("MATERIALIZED", dialect="postgresql")