오늘은 PostgreSQL에서 스칼라 서브 쿼리로 인해 성능 저하가 발생해서 CTE를 활용해 성능을 개선한 케이스를 정리하고자 한다.

들어가기에 앞서 스칼라 서브쿼리와 CTE에 대해 모르는 분들을 위해 정리를 먼저하고 들어가자.

PostgreSQL CTE로 성능 개선

CTE(Common Table Expressions)

CTE는 Common Table Expressions의 줄임말로서 서브쿼리와 유사하게 사용된다.
CTE와 비교 대상으로 view가 있으며 view는 만들기 위해 권한이 필요하고 사전에 정의를 해야한다. 다만 CT는 권한이 필요 없고 하나의 쿼리문이 끝날때 까지만 지속되는 일회성 테이블이다.

CTE의 장점은 쿼리의 가독성과 재사용성을 위해 사용한다.
그리고 CTE 구문은 AHSI-SQL99에 의한 표준 구문이다.(특정 DBMS에 의존한 문법이 아니다.)

스칼라 서브쿼리

서브 쿼리의 종류는 아래와 같이 3가지 종류로 구분된다.

• SELECT 절 서브쿼리 : 스칼라 서브쿼리
• FROM 절 서브쿼리 : 인라인 뷰
• WHERE 절 서브쿼리 : 중첩 서브쿼리

스칼라 서브쿼리 VS CTE

가독성 : CTE는 복잡한 쿼리를 여러 부분으로 나누기 때문에 가독성을 높인다. 다만 서브쿼리는 쿼리안에서 중첩되기 때문에 가독성이 떨어진다.
재사용성 : CTE는 동일한 쿼리내에서 여러번 재사용될 수 있다. 서브쿼리는 각기 별도로 재실행된다.

일반적으로 스칼라 서브쿼리는 행단위로 실행되기 때문에 CTE에 비해서 성능이 떨어진다.
따라서, 가능하다면 CTE나 Join을 통해서 성능을 개선해야한다.
그리고 필요에 따라 CTE에서 인덱스를 고려해야할수있다.

참고

https://lucy-the-marketer.kr/ko/sql-%EC%9D%B8%ED%84%B0%EB%B7%B0-cte%EC%99%80-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC%EC%9D%98-%EC%B0%A8%EC%9D%B4/
https://yahwang.github.io/posts/49