오늘은 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 |
--------------------------------------------------------------------------------------------------------------
이번에는, 현업의 요구사항으로 ORDER BY 기준을 아래와 같이 변경되었다고 가정해 봅니다. ORDER BY에 SHOP_ID와 MBR_ID가 추가되고, DESC와 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| | | |
------------------------------------------------------------------------------------------------------------------------------
이미, 인덱스 구조가 머리에 있으신 분은 인덱스를 왜 사용하지 않았는지 이해가 될 것입니다. 아래와 같이 힌트를 적용해 인덱스를 강제로 사용하게 하는 것을 고려해 볼 수 있습니다. 하지만, 성능은 전혀 좋아지지 않습니다. 힌트로 인해 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 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------