PostgreSQL의 LEADING 힌트

 

 

 

 

 

PostgreSQL의 LEADING 힌트

PostgreSQL에 PG_HINT_PLAN 확장팩을 설치하면, LEADING 힌트를 사용할 수 있습니다.

설명에 앞서, 힌트의 사용법을 익히는 것보다, 왜 이와 같이 처리하면 성능이 좋아지는지를 알고 힌트를 사용해야 합니다.

(물론, 이 글에서는 힌트 사용법만 설명합니다.^^)

 

PG_HINT_PLAN을 설치하고 사용할 수 있는 힌트는 아래 페이지에서 확인할 수 있습니다.

- https://pg-hint-plan.readthedocs.io/en/latest/hint_list.html

 

해당 페이지에는 Leading 힌트에 대해 아래와 같이 설명되어 있습니다.

Leading(table table[ table...])
 - Forces join order as specified.
 - 지정된 대로 조인 순서를 강제한다
Leading(<join pair>)
 - Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure.
 - 지정된 대로 조인 순서와 방향을 강제한다. 조인 쌍은 괄호로 둘러싸인 테이블 또는 다른 조인 쌍의 쌍이며, 이를 통해 중첩 구조를 만들 수 있다

 

이처럼 두 가지 사용법이 적혀 있습니다. 하지만 이 설명만 봐서는 잘 이해할 수 없습니다.

Leading 힌트의 용법을 이해하기 위해서는 조인 순서와 조인 방향에 대해 정의할 필요가 있습니다.

• 조인 순서: 조인을 처리하는 순서
  • 조인 순서가 A, B, C: A와 B를 조인해, (A, B)를 만든 후에 (A, B)와 C를 조인하는 순서로 처리
  • 이처럼 조인 순서만 정의하면, 두 데이터 집합을 조인할 때는 어떤 방향으로 조인할지는 알 수 없다.
  • 예를 들어, A와 B를 조인할 때 어느 테이블을 먼저 처리할지는 알 수 없다.
  • (A, B)와 C를 조인할 때도, 어느 쪽을 먼저 접근해 처리할지는 알 수 없다.
• 조인 방향: 두 데이터 집합이 결합(조인)할 때 정해지는 두 데이터 집합 간의 접근 순서
  • A, B, C의 조인 순서이면서, A에서 B 방향으로 조인, C에서 (A, B) 방향으로 조인
    • 조인 순서와 함께 조인 방향이 정의되었다.
    • A에서 B 방향으로 조인 처리해서 (A, B) 생성, C에서 (A, B) 데이터 집합 방향으로 조인 처리
  • A, B, C의 조인 순서이면서, B에서 A 방향으로 조인, (B, A)에서 C 방향으로 조인
    • B에서 A 방향으로 조인 처리해서 (B, A) 생성, (B, A) 집합에서 C 방향으로 조인 처리

 

아마 이해가 될 듯하면서도 이해가 안될 수도 있습니다. 조인 순서나 조인 방향이나 얼핏 보기에 비슷하니까요.

 

아래와 같이 Leading(t1 t2) 로 힌트를 지정합니다. 이처럼 괄호를 하나만 사용하면 조인 순서만 지정됩니다. 이 경우 NL 조인을 한다고 가정하면 t1을 먼저 접근해 t2 방향으로 조인을 처리할 지, t2를 먼저 접근해 t1 방향으로 조인을 처리할지 알 수 없습니다. 이러한 조인 방향은 옵티마이져가 선택합니다. 사실 두 개 테이블만 있는 상황에서는 이 같은 조인은 불필요합니다.

/*+ Leading(t1 t2) */
SELECT  t1.cus_id ,SUM(t2.ord_qty) ord_qty 
FROM    m_cus t1
        INNER JOIN t_ord_join t2
            ON (t2.cus_id = t1.cus_id) 
WHERE   t1.cus_gd = 'B'
AND     t2.ord_dt >= TO_DATE('20170201','YYYYMMDD') 
AND     t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
GROUP BY T1.cus_id;

 

이번에는 Leading 힌트에 괄호를 중첩해서 사용합니다. 이와 같이 정의하면 조인 방향이 정해집니다. 단순한 NL 조인이라면, 앞에 있는 t1이 선행 집합이 되어 t2 방향으로 접근해 조인이 처리됩니다. 그런데, SQL이 HASH 조인으로 풀린다면 주의가 필요합니다. Leading 힌트의 뒤쪽에 있는 t2가 Build가 되고, t1이 Probe가 됩니다.

다시 말해, HASH JOIN에서는 Build가 일반적으로 먼저 만들어진다는 개념과, Leading의 서술에 따른 순서가 반대되므로 이 부분은 외워두기 바랍니다.

/*+ Leading((t1 t2)) */
SELECT  t1.cus_id ,SUM(t2.ord_qty) ord_qty 
FROM    m_cus t1
        INNER JOIN t_ord_join t2
            ON (t2.cus_id = t1.cus_id) 
WHERE   t1.cus_gd = 'B'
AND     t2.ord_dt >= TO_DATE('20170201','YYYYMMDD') 
AND     t2.ord_dt < TO_DATE('20170301','YYYYMMDD')
GROUP BY T1.cus_id;

 

이번에는 세 개 테이블을 조인해봅니다. 아래와 같이 Leading에서 순서만 지정해봅니다. t1, t2, t3, 순서의 조인만 유지합니다. 두 데이터 집합이 조인되는 순간에는 어느쪽을 선행을 할지, HASH 조인이라면 어느쪽을 Build로 할지는 옵티마이져가 알아서 선택합니다.

/*+ Leading(t1 t2 t3) */
SELECT  t1.itm_id ,t1.itm_nm ,t2.ord_st ,count(*) ord_qty 
FROM    m_itm t1 
        INNER JOIN t_ord_join t2 on (t2.itm_id = t1.itm_id) 
        INNER JOIN m_cus t3 ON (t3.cus_id = t2.cus_id) 
WHERE   t1.itm_tp = 'ELEC' 
AND     t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND     t2.ord_dt <  TO_DATE('20170301','YYYYMMDD')
AND     t3.cus_gd = 'B' 
GROUP BY t1.itm_id ,t1.itm_nm ,t2.ord_st;

 

조인순서와 함께 조인 방향을 모두 지정하고 싶다면 아래와 같이 괄호를 조인이 발생하는 두 데이터 집합간에 추가 중첩합니다. 아래 SQL의  조인되는 순서와 방향을 정리하면 다음과 같습니다.

• Leading(((t1 t2) t3)) 괄호가 세 번 중첩, t1과 t2를 묶고, (t1 t2)와 t3를 한 번 더 묶었습니다.
  • (t1 t2) NL 처리시: t1을 선행 접근해 t2를 후행 접근
  • (t1 t2) HASH 처리시: t2를 Build로 처리해 t1을 Probe 처리
  • ((t1 t2) t3) NL 처리시: (t1 t2)를 선행 t3를 후행 접근
  • ((t1 t2) t3) HASH 처리시: t3를 Build로 처리, (t1 t2)를 Probe 처리

/*+ Leading(((t1 t2) t3)) */
SELECT  t1.itm_id ,t1.itm_nm ,t2.ord_st ,count(*) ord_qty 
FROM    m_itm t1 
        INNER JOIN t_ord_join t2 on (t2.itm_id = t1.itm_id) 
        INNER JOIN m_cus t3 ON (t3.cus_id = t2.cus_id) 
WHERE   t1.itm_tp = 'ELEC' 
AND     t2.ord_dt >= TO_DATE('20170201','YYYYMMDD')
AND     t2.ord_dt <  TO_DATE('20170301','YYYYMMDD')
AND     t3.cus_gd = 'B' 
GROUP BY t1.itm_id ,t1.itm_nm ,t2.ord_st;

 

조인 순서와 함께 조인 방향, 그리고 조인 방법까지 컨트롤하고 싶다면 아래와 같이 힌트를 사용해 볼 수 있습니다.

/*+ Leading((((t1 t2) t3) t4)) NestLoop(t1 t2) NestLoop(t1 t2 t3) HashJoin(t1 t2 t3 t4) */
SELECT  t1.itm_id ,t1.itm_nm ,t2.ord_seq ,t3.cus_id ,t4.*
FROM    m_itm t1 
        INNER JOIN t_ord_join t2 on (t2.itm_id = t1.itm_id) 
        INNER JOIN m_cus t3 ON (t3.cus_id = t2.cus_id) 
        LEFT OUTER JOIN t_itm_evl t4 on (t4.itm_id = t2.itm_id and t4.cus_id = t3.cus_id)
WHERE   t2.ord_dt >= TO_DATE('20170101','YYYYMMDD')
AND     t2.ord_dt <  TO_DATE('20170501','YYYYMMDD');

 

준비한 내용은 여기까지입니다.

중요한 것은 힌트를 사용할줄 아는 것이 아닙니다. 왜 이와 같이 처리하면 성능이 좋아지는지를 알고 힌트를 사용해야 합니다.

 

 

SQL 튜닝 입문 강의가 필요하신 분은 아래 링크를 참고해주세요.!

• StartUP Tuning For PostgreSQL 강의: https://cafe.naver.com/dbian/7181
• StartUP Tuning For MySQL 강의: https://cafe.naver.com/dbian/6958