PostgreSQL NL 조인 - 연습 문제 풀이

PostgreSQL NL 조인 - 연습 문제 풀이

 

StartUP Tuning(PostgreSQL, MySQL을 활용한 SQL 튜닝 입문) 강의에서는,

Booster QUIZ를 통해, SQL 튜닝을 각자 직접 실습해볼 수 있도록 하고 있습니다.

이와 같은 실습과 고민의 시간은 SQL 튜닝 능력 향상에 많은 도움이 됩니다.

오늘은 Booster Quiz 중에 NL 조인 관련 연습문제를 하나 풀어보도록 하겠습니다.

 

 

 

Booster Quiz: 아래 SQL을 NL 조인으로 처리한다고 했을때, 어느 방향으로 NL 처리하는 것이 좋은지 파악하기 위해 아래 질문에 차례대로 답해보세요.

SELECT  t1.itm_id
        ,COUNT(*) CNT
FROM    m_itm t1
        INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE   t1.itm_tp = 'CLOTHES'
AND     t1.unt_prc <= 1000
AND     t2.evl_pt = 5
GROUP BY t1.itm_id;

 

 

SQL을 보면 m_itm(아이템/품목)과 t_itm_evl_big(아이템평가), 두 개의 테이블이 사용되고 있습니다.

위 SQL에 대한 실행계획과 인덱스를 확인하지 않고 아래 질문에 차례대로 답해봅니다.

 

1. t1 > t2 방향으로 NL 조인한다면, t2에 방문(loop) 횟수는?

아래와 같이 원래 SQL에서 t1 부분만 추출해 카운트하면 알 수 있습니다.(답: 10번)

SELECT  COUNT(*)
FROM    m_itm t1
WHERE   t1.itm_tp = 'CLOTHES'
AND     t1.unt_prc <= 1000;

count|
-----+
   10|

 

2. t2 > t1 방향으로 NL 조인한다면, t1에 방문(loop) 회수는?

아래와 같이 원래 SQL에서 t2 부분만 추출해 카운트하면 알 수 있습니다.(답:25,000번)

 

SELECT  COUNT(*)
FROM    t_itm_evl_big t2
WHERE   t2.evl_pt = 5;

count|
-----+
25000|

 

 

1번과 2번 질문을 통해, 2번보다는 1번 방법이 더 유리할 수 있겠구나 판단할 수 있습니다.

t1>t2 방향으로 NL 조인하면 t2에는 10번만 접근하면 되고, t2>t1 방향으로 NL 조인하면 t1에는 무려 25,000번 반복 접근해야 합니다. 반복적인 접근 횟수를 줄이는 것은 SQL 성능에 도움이 됩니다. (물론 적절한 인덱스 구성이 선행되어야 합니다.)

이어서 다음 질문에도 답해봅니다.

 

3. 힌트를 사용해 t1 > t2 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인

아래와 같이 힌트를 적용해 실행계획을 확인해보면, t2의 loops가 10인 것을 알 수 있습니다.

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t1 t2)) NestLoop(t1 t2) */
SELECT  t1.itm_id
        ,COUNT(*) CNT
FROM    m_itm t1
        INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE   t1.itm_tp = 'CLOTHES'
AND     t1.unt_prc <= 1000
AND     t2.evl_pt = 5
GROUP BY t1.itm_id;

GroupAggregate (actual time=9.148..10.760 rows=2 loops=1)
  Group Key: t1.itm_id
  Buffers: shared hit=296
  ->  Nested Loop (actual time=0.035..10.411 rows=3500 loops=1)
        Buffers: shared hit=296
        ->  Index Scan using m_itm_pk on m_itm t1 (actual time=0.012..0.035 rows=10 loops=1)
              Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
              Rows Removed by Filter: 90
              Buffers: shared hit=2
        ->  Index Scan using t_itm_evl_big_pk on t_itm_evl_big t2 (actual time=0.807..1.004 rows=350 loops=10)
              Index Cond: ((itm_id)::text = (t1.itm_id)::text)
              Filter: (evl_pt = '5'::numeric)
              Rows Removed by Filter: 1550
              Buffers: shared hit=294
Planning Time: 0.179 ms
Execution Time: 10.792 ms

 

 

4. 힌트를 사용해 t2 > t1 방향으로 NL 조인되도록 처리하고, 실제 실행계획을 확인

아래와 같이 힌트를 적용해 실행계획을 확인해보면, t1의 실제 loops는  16번만 발생했습니다. 하지만, NL 조인의 후행 집합쪽을 보면, t1의 데이터를 Memoize 처리했고, Memoiz에 25,000 번의 반복 접근이 발생했습니다.

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ Leading((t2 t1)) NestLoop(t1 t2) */
SELECT  t1.itm_id
        ,COUNT(*) CNT
FROM    m_itm t1
        INNER JOIN t_itm_evl_big t2 ON (t2.itm_id = t1.itm_id)
WHERE   t1.itm_tp = 'CLOTHES'
AND     t1.unt_prc <= 1000
AND     t2.evl_pt = 5
GROUP BY t1.itm_id;

HashAggregate (actual time=30.695..30.697 rows=2 loops=1)
  Group Key: t1.itm_id
  Batches: 1  Memory Usage: 24kB
  Buffers: shared hit=1694
  ->  Nested Loop (actual time=0.028..30.145 rows=3500 loops=1)
        Buffers: shared hit=1694
        ->  Seq Scan on t_itm_evl_big t2 (actual time=0.010..22.777 rows=25000 loops=1)
              Filter: (evl_pt = '5'::numeric)
              Rows Removed by Filter: 147500
              Buffers: shared hit=1662
        ->  Memoize (actual time=0.000..0.000 rows=0 loops=25000)
              Cache Key: t2.itm_id
              Cache Mode: logical
              Hits: 24984  Misses: 16  Evictions: 0  Overflows: 0  Memory Usage: 2kB
              Buffers: shared hit=32
              ->  Index Scan using m_itm_pk on m_itm t1 (actual time=0.004..0.004 rows=0 loops=16)
                    Index Cond: ((itm_id)::text = (t2.itm_id)::text)
                    Filter: ((unt_prc <= '1000'::numeric) AND ((itm_tp)::text = 'CLOTHES'::text))
                    Rows Removed by Filter: 1
                    Buffers: shared hit=32
Planning Time: 0.186 ms
Execution Time: 30.736 ms

 

 

지금까지의 내용을 종합해, NL 조인 처리 방향에 따른 성능 차이를 아래와 같이 정리할 수 있습니다. 정리를 통해 현재 SQL에서는 어떤 방향으로 NL 조인이 적합한가를 판단할 수 있습니다. 

 

 

• t1 > t2
  • 후행(t2)에 접근횟수: 10번
  • t1 > t2 IO(Buffers) : 296
  • t1 > t2 Execution time : 10.792 ms
• t2 > t1
  • 후행(t1)에 접근횟수: 실제 테이블에는 16번 접근, 하지만 memoize(cache) 처리된 데이터를 25,000번 접근.
  • IO(Buffers): 1694
  • Execution time : 30.736 ms

 

 

물론, 지금까지 살펴본 내용에 인덱스 구성을 추가하거나 변경한다면 완전히 다른 결과가 나올 수 있습니다. 

여기서 중요한건, 이와 같은 순차적인 질문과 답으로 NL 조인의 방향에 따라 어떤 부분에서 성능 변화가 발생할 수 있는지 확인해보는 과정입니다.

 

StartUP Tuning 강의에서는 이와 같은 Quiz를 직접 풀어보시게 됩니다.

감사합니다.~!

 

P.S. 아래 강의들을 진행하고 있으니, 많은 관심 부탁드립니다.
  - StartUP Tuning For PostgreSQL: PostgreSQL을 활용한 SQL 튜닝 입문 교육
    https://cafe.naver.com/dbian/7181
  - StartUP Tuning For MySQL: MySQL을 활용한 SQL 튜닝 입문 교육
    https://cafe.naver.com/dbian/6958
  - 평생필요한 데이터 분석: 저자 직강!, 주식 데이터를 활용한 SQL 입문자 교육
    https://cafe.naver.com/dbian/7131