[Postgresql] WITH문 사용하기, WITH가 성능에 미치는 영향

🚀  들어가며...

• Query을 작성하다보면 SubQuery를 사용하는 경우가 많습니다. 한두개 정도 사용하면 괜찮지만 그 수가 많아질 수록 가독성이 떨어지며, 재사용할 수가 없어서 매번 같은 Query를 작성해줘야 하는 불편함이 있습니다. 이 문제점을 해소하는데 도움이 되는 WITH문에 대해서 정리해보았습니다.

💌 소스코드

WITH [ 별명1 ] [ (컬럼명1 [,컬럼명2]) ] AS (
    SUB QUERY
)[, 별명2 AS ... ]
MAIN QUERY

기본형식은 다음과 같습니다.

📑 내용

• WITH문의 특징은 다음과 같습니다.
  • 이름을 가진 SubQuery를 정의한 후 사용하는 구문입니다.
  • Query의 전체적인 가독성을 높이고, 재사용할 수 있는 장점이 있습니다.
  • 대부분의 DBMS에서 지원합니다.  
  • 모든 DML에서 사용할 수 있습니다.

with base_data as (
          select sub.gisu, sub.str_1, fix.ty_cost, fix.da_acquire
          from fta_carbu_v2_mat_1_1_sub as sub 
          join UNNEST(STRING_TO_ARRAY(%(code)s, ',')) as codes on sub.gisu = %(gisu)s and sub.str_1 = codes
          join fta_fixast as fix on sub.gisu = fix.gisu and fix.cd_fixast = sub.str_7 and fix.mn_1_1_1 = 1
    ), update_main as (
        update fta_carbu_v2_mat_1_1 main
        set str_8 = base.da_acquire
        from base_data as base 
        where base.str_1 = main.str_1
        AND MAIN.GISU = %(gisu)s
        returning main.str_1, main.str_8
    ), update_sub as (
        update fta_carbu_v2_mat_1_1_sub sub
        set mn_3 = base.ty_cost
        from base_data as base 
        where base.gisu = sub.gisu and base.str_1 = sub.str_1
        returning sub.str_1, sub.mn_3
    )
    select main.str_1, main.str_8, sub.mn_3::varchar
    from update_main as main
    left outer join update_sub as sub on main.str_1 = sub.str_1;

위 소스코드는 두개의 테이블을 한번에 update한 후 업데이트 한 데이터를 조회하는 쿼리문입니다.

WITH문을 이용하면 한번에 여러개의 SubQuery를 만들수 있다는 장점이 있습니다.

 

Q:

쿼리를 짜다보면 WITH 이름 AS (쿼리)해서 임시테이블을 만들어 쓰는데 실행계획을 보면 일반 서브쿼리로 짜서 돌리는 것보다 메모리로드가 약간 더 많이 걸리는거 같습니다.

유지보수 할때 쿼리 이해도가 좋아지는거 같아서 필요할 때만 쓰는데 WITH로 임시테이블을 만들어 쓰는건 좋지 못한 습관일까요?

성능테스트를 하다 쿼리에서 WITH를 제거해달라는 요구사항을 받았는데 성능에 얼마나 영향을 주는지 잘 모르겠습니다.

 

A:

WITH로 만드는건 그 쿼리내에서 임시 테이블을 생성해 버퍼캐쉬에 올린다는 건데 아마도 그 부분에서 메모리 사용량이 올라갈 거로 보이네요. 상황에 따라 다르겠지만 WITH구문을 쓸 때의 장점은 같은 쿼리문을 여러 번 서브뤄리로 사용할 시는 각각의 실행 계획이 생성되지만, WITH구문은 버퍼에서 풀스캔을 하기에 쿼리문의 내용을 재사용 시 버퍼에서 계속 가져다 쓴 장점이 있다. 서브쿼리로 한다면 쿼리 변환을 통해 옵티마이저가 더 좋은 실행 계획을 생성할 수 있지만, WITH문으로 사용하면 해당 쿼리를 버퍼에 올리는 과정에서 처리 범위를 줄이지 못한다면 단점이 될 수 있습니다. 실제 WITH구문을 이용해 튜닝을 하는 경우도 있기에 WITH구문을 쓰는건 자체가 문제가 되는 습관이라 할 수는 없겠죠. 결론적으로 말씀드리자면 WITH구문이 좋고 나쁘고라기 보다 상황에 맞게 사용해야 할 듯 하네요. 무조건 적으로 안좋게 보기 보다, WITH구문으로 충분히 범위를 줄이고, 재사용하기 좋은 쿼리라면 사용하는게 좋을 수 있습니다.

 

🙋🏻‍♂️ 후기

제가 테이블 가공할때 자주 이용하는 구문중 하나인 WITH문에 대해 정리해 보았습니다.

 

🔗  참고한 글

https://www.postgresql.org/docs/current/queries-with.html