PostgreSQL의 강력한 기능 Presort

오늘은 PostgreSQL의 Presort 기능을 간단히 살펴봅니다. Presort 는 페이징 처리의 성능에 있어 아주 강력한 기능으로 언젠가 오라클도 지원해줬으면 하는 기능입니다.

•

인덱스와 페이징 개념이 있어야 읽을만한 내용입니다.

아래 예제에서 사용한 테이블과 데이터는 StartUP Tuning 강의에서 제공하는 데이터 셋입니다.

오라클의 페이징

오라클을 먼저 살펴보겠습니다. 아래 SQL은 ORD_DTM 으로 내림차순해서 페이징에 필요한 30건만 추출하는 SQL입니다. SQL의 메인 테이블인 TR_ORD_BIG에는 ORD_DTM으로 구성된 TR_ORD_BIG_X01 인덱스가 존재합니다. 실행계획을 확인해보면, X01 인덱스를 DESC로 읽어 페이징에 필요한 30 건의 데이터만 정확히 읽어내고 있습니다. 결과적으로, 조인을 포함한 IO 수치가 132로 매우 효율적으로 풀리는 것을 알 수 있습니다. 인덱스를 활용해 정확히 필요한 데이터만 읽어내는 DB-INDEX 페이징이 작동한 것입니다. (DB-INDEX 페이징은 SQL BOOSTER에서 페이징 방식을 분류한 용어입니다.)

SELECT  T1.ORD_DTM ,T1.ORD_NO 
        ,T1.SHOP_ID ,T2.SHOP_NM ,T2.SHOP_SIZE
        ,T1.MBR_ID ,T3.NICK_NM ,T3.MBR_GD
        ,T1.ORD_AMT
FROM    STARTDBORA.TR_ORD_BIG T1
        LEFT OUTER JOIN STARTDBORA.MS_SHOP_BIG T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
        LEFT OUTER JOIN STARTDBORA.MS_MBR_BIG T3
            ON (T3.MBR_ID = T1.MBR_ID)
WHERE   T1.ORD_DTM >= '20240901'
AND     T1.ORD_DTM <  '20241201'
ORDER BY T1.ORD_DTM DESC
FETCH NEXT 30 ROWS ONLY;

SQL_ID  1sr32711hzdsg, child number 0
Plan hash value: 2454156093
 
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |      1 |        |     30 |00:00:00.01 |     132 |
|*  1 |  VIEW                             |                |      1 |     30 |     30 |00:00:00.01 |     132 |
|*  2 |   WINDOW NOSORT STOPKEY           |                |      1 |     31 |     30 |00:00:00.01 |     132 |
|*  3 |    FILTER                         |                |      1 |        |     30 |00:00:00.01 |     132 |
|   4 |     NESTED LOOPS OUTER            |                |      1 |     31 |     30 |00:00:00.01 |     132 |
|   5 |      NESTED LOOPS OUTER           |                |      1 |     31 |     30 |00:00:00.01 |      69 |
|   6 |       TABLE ACCESS BY INDEX ROWID | TR_ORD_BIG     |      1 |   1116K|     30 |00:00:00.01 |      34 |
|*  7 |        INDEX RANGE SCAN DESCENDING| TR_ORD_BIG_X01 |      1 |     31 |     30 |00:00:00.01 |       4 |
|   8 |       TABLE ACCESS BY INDEX ROWID | MS_SHOP_BIG    |     30 |      1 |     30 |00:00:00.01 |      35 |
|*  9 |        INDEX UNIQUE SCAN          | MS_SHOP_BIG_PK |     30 |      1 |     30 |00:00:00.01 |       5 |
|  10 |      TABLE ACCESS BY INDEX ROWID  | MS_MBR_BIG     |     30 |      1 |     30 |00:00:00.01 |      63 |
|* 11 |       INDEX UNIQUE SCAN           | MS_MBR_BIG_PK  |     30 |      1 |     30 |00:00:00.01 |      33 |
--------------------------------------------------------------------------------------------------------------
SQL
복사

이번에는, 현업의 요구사항으로 ORDER BY 기준을 아래와 같이 변경되었다고 가정해 봅니다. ORDER BY에 SHOP_ID와 MBR_ID가 추가되고, DESC와 ASC가 혼합되어 사용되고 있습니다.

•

ORDER BY T1.ORD_DTM DESC ,T1.SHOP_ID ASC ,T1.MBR_ID ASC

위와 같이 ORDER BY 가 사용되면, 기존에 사용했던 X01(ORD_DTM) 인덱스를 사용해서는 DB-INDEX 페이징이 작동하지 않게 됩니다. 아래와 같이 ORDER BY를 변경하고 SQL을 실행해봅니다. 실행계획을 보면, IO가 251,000로 매우 큰 수치가 나옵니다. TR_ORD_BIG을 접근하는 과정에서 X01 인덱스가 아닌 FULL SCAN을 사용하고 있습니다.

SELECT  T1.ORD_DTM ,T1.ORD_NO 
        ,T1.SHOP_ID ,T2.SHOP_NM ,T2.SHOP_SIZE
        ,T1.MBR_ID ,T3.NICK_NM ,T3.MBR_GD
        ,T1.ORD_AMT
FROM    STARTDBORA.TR_ORD_BIG T1
        LEFT OUTER JOIN STARTDBORA.MS_SHOP_BIG T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
        LEFT OUTER JOIN STARTDBORA.MS_MBR_BIG T3
            ON (T3.MBR_ID = T1.MBR_ID)
WHERE   T1.ORD_DTM >= '20240901'
AND     T1.ORD_DTM <  '20241201'
ORDER BY T1.ORD_DTM DESC ,T1.SHOP_ID ASC ,T1.MBR_ID ASC -- > ORDER BY 변경
FETCH NEXT 30 ROWS ONLY;

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |      1 |        |     30 |00:00:04.14 |     251K|       |       |          |
|*  1 |  VIEW                     |             |      1 |     30 |     30 |00:00:04.14 |     251K|       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK |             |      1 |   1116K|     30 |00:00:04.14 |     251K| 36864 | 36864 |32768  (0)|
|*  3 |    FILTER                 |             |      1 |        |   1841K|00:00:01.91 |     251K|       |       |          |
|*  4 |     HASH JOIN RIGHT OUTER |             |      1 |   1116K|   1841K|00:00:01.89 |     251K|  1316K|  1316K| 1581K (0)|
|   5 |      TABLE ACCESS FULL    | MS_SHOP_BIG |      1 |    300 |    300 |00:00:00.01 |       6 |       |       |          |
|*  6 |      HASH JOIN RIGHT OUTER|             |      1 |   1116K|   1841K|00:00:01.60 |     251K|  8457K|  3610K| 8939K (0)|
|   7 |       TABLE ACCESS FULL   | MS_MBR_BIG  |      1 |  99990 |  99990 |00:00:00.01 |     947 |       |       |          |
|*  8 |       TABLE ACCESS FULL   | TR_ORD_BIG  |      1 |   1116K|   1841K|00:00:01.25 |     250K|       |       |          |
------------------------------------------------------------------------------------------------------------------------------
SQL
복사

이미, 인덱스 구조가 머리에 있으신 분은 인덱스를 왜 사용하지 않았는지 이해가 될 것입니다. 아래와 같이 힌트를 적용해 인덱스를 강제로 사용하게 하는 것을 고려해 볼 수 있습니다. 하지만, 성능은 전혀 좋아지지 않습니다. 힌트로 인해 X01 인덱스를 사용했지만, IO 측면에서는 1,591,000 으로 훨씬 나빠졌습니다.

SELECT  /*+ INDEX(T1(ORD_DTM)) */
        T1.ORD_DTM ,T1.ORD_NO 
        ,T1.SHOP_ID ,T2.SHOP_NM ,T2.SHOP_SIZE
        ,T1.MBR_ID ,T3.NICK_NM ,T3.MBR_GD
        ,T1.ORD_AMT
FROM    STARTDBORA.TR_ORD_BIG T1
        LEFT OUTER JOIN STARTDBORA.MS_SHOP_BIG T2
            ON (T2.SHOP_ID = T1.SHOP_ID)
        LEFT OUTER JOIN STARTDBORA.MS_MBR_BIG T3
            ON (T3.MBR_ID = T1.MBR_ID)
WHERE   T1.ORD_DTM >= '20240901'
AND     T1.ORD_DTM <  '20241201'
ORDER BY T1.ORD_DTM DESC ,T1.SHOP_ID ASC ,T1.MBR_ID ASC
FETCH NEXT 30 ROWS ONLY;

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |     30 |00:00:08.39 |    1591K|   4881 |       |       |          |
|*  1 |  VIEW                                    |                |      1 |     30 |     30 |00:00:08.39 |    1591K|   4881 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK                |                |      1 |   1116K|     30 |00:00:08.39 |    1591K|   4881 |    97M|  3371K|   86M (0)|
|*  3 |    FILTER                                |                |      1 |        |   1841K|00:00:08.56 |    1591K|   4881 |       |       |          |
|*  4 |     HASH JOIN RIGHT OUTER                |                |      1 |   1116K|   1841K|00:00:08.06 |    1591K|   4881 |  1316K|  1316K| 1527K (0)|
|   5 |      TABLE ACCESS FULL                   | MS_SHOP_BIG    |      1 |    300 |    300 |00:00:00.01 |       6 |      0 |       |       |          |
|*  6 |      HASH JOIN RIGHT OUTER               |                |      1 |   1116K|   1841K|00:00:07.03 |    1591K|   4881 |  8457K|  3610K| 8931K (0)|
|   7 |       TABLE ACCESS FULL                  | MS_MBR_BIG     |      1 |  99990 |  99990 |00:00:00.01 |     947 |      0 |       |       |          |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| TR_ORD_BIG     |      1 |   1116K|   1841K|00:00:05.10 |    1590K|   4881 |       |       |          |
|*  9 |        INDEX RANGE SCAN                  | TR_ORD_BIG_X01 |      1 |   1116K|   1841K|00:00:01.74 |    4874 |   4881 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------
 
SQL
복사

PostgreSQL의 페이징

지금까지는 오라클의 페이징 처리에 대해 살펴봤습니다. 같은 상황으로 PostgreSQL에서 동일한 SQL을 실행해봅니다. 마찬가지로 tr_ord_big에는 x01(ord_dtm) 인덱스만 존재합니다. ORDER BY 절에는 x01 인덱스로 커버할 수 없는 (ord_dtm DESC, shoip_id ASC, mbr_id ASC)를 사용합니다.

EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT  t1.ord_dtm ,t1.ord_no 
        ,t1.shop_id ,t2.shop_nm ,t2.shop_size
        ,t1.mbr_id ,t3.nick_nm ,t3.mbr_gd
        ,t1.ord_amt
FROM    tr_ord_big t1
        LEFT OUTER JOIN ms_shop_big t2
            ON (t2.shop_id = t1.shop_id)
        LEFT OUTER JOIN ms_mbr_big t3
            ON (t3.mbr_id = t1.mbr_id)
WHERE   t1.ord_dtm >= '20240901'
AND     t1.ord_dtm <  '20241201'
ORDER BY t1.ord_dtm DESC ,t1.shop_id ASC ,t1.mbr_id ASC
LIMIT 30;

Limit (actual time=0.639..0.646 rows=30 loops=1)
  Buffers: shared hit=129
  ->  Incremental Sort (actual time=0.637..0.641 rows=30 loops=1)
        Sort Key: t1.ord_dtm DESC, t1.shop_id, t1.mbr_id
        Presorted Key: t1.ord_dtm
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
        Buffers: shared hit=129
        ->  Nested Loop Left Join (actual time=0.063..0.553 rows=31 loops=1)
              Buffers: shared hit=129
              ->  Nested Loop Left Join (actual time=0.047..0.137 rows=31 loops=1)
                    Buffers: shared hit=36
                    ->  Index Scan Backward using tr_ord_big_x01 on tr_ord_big t1 (actual time=0.028..0.076 rows=31 loops=1)
                          Index Cond: ((ord_dtm >= '2024-09-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-12-01 00:00:00'::timestamp without time zone))
                          Buffers: shared hit=34
                    ->  Memoize (actual time=0.001..0.001 rows=1 loops=31)
                          Cache Key: t1.shop_id
                          Cache Mode: logical
                          Hits: 30  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                          Buffers: shared hit=2
                          ->  Index Scan using ms_shop_big_pk on ms_shop_big t2 (actual time=0.009..0.009 rows=1 loops=1)
                                Index Cond: ((shop_id)::text = (t1.shop_id)::text)
                                Buffers: shared hit=2
              ->  Memoize (actual time=0.009..0.009 rows=1 loops=31)
                    Cache Key: t1.mbr_id
                    Cache Mode: logical
                    Hits: 0  Misses: 31  Evictions: 0  Overflows: 0  Memory Usage: 4kB
                    Buffers: shared hit=93
                    ->  Index Scan using ms_mbr_big_pk on ms_mbr_big t3 (actual time=0.008..0.008 rows=1 loops=31)
                          Index Cond: ((mbr_id)::text = (t1.mbr_id)::text)
                          Buffers: shared hit=93
Planning:
  Buffers: shared hit=10
Planning Time: 0.558 ms
Execution Time: 0.995 ms
SQL
복사

PG의 실행계획을 확인해보면, 전체 IO가 129가 나오고 있습니다. ORDER BY에 적절하지 않지 x01(ord_dtm) 인덱스만 있는데도, 마치 DB-INDEX 페이징을 한 것처럼 효율적인 IO 수치가 출력되었습니다.

PG의 실행계획을 잘 보면, 다음과 같이 Presorted라는 알고리즘이 존재합니다. 정확한 알고리즘은 알 수 없지만, ord_dtm으로 pre sort를 진행해, (ord_dtm DESC, shop_id ASC, mbr_id ASC)를 만족할수 있는 30건만 뽑아내는 내부 동작이 있는 것으로 보입니다.

  ->  Incremental Sort (actual time=0.637..0.641 rows=30 loops=1)
        Sort Key: t1.ord_dtm DESC, t1.shop_id, t1.mbr_id
        Presorted Key: t1.ord_dtm
        Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB
        Buffers: shared hit=129
SQL
복사

위와 같은 Presort 기능은 제가 다뤄본 DBMS 중에서는 PG에서만 발견했습니다. 페이징을 위해 아주 강력한 기능이라고 생각됩니다. 언젠가 오라클도 이러한 기능을 넣어주지 않을까 기대해 봅니다. 페이징을 위해 ORDER BY에 맞게, 인덱스를 픽스하게 구성하고 인라인 뷰를 2중, 3중으로 쓰는 일이 줄어들길 바래봅니다.

준비한 내용은 여기까지입니다. 25년 상반기 StartUP Tuning For PostgreSQL 오프라인 주말 강의를 모집중입니다. 많은 관심 부탁드립니다.!

•

https://cafe.naver.com/dbian/8709