본 글은, 오프라인 강의인 StartUP Tuning For PostgreSQL을 책으로 펼치기 위해 정리 중인 글입니다. 아직은 써나가는 중이므로 문장이 부자연스러울 수 있습니다. 또한, 부분적인 내용만 일부 공유하고 있어, 전체적인 설명 흐름이 맞지 않는 부분이 존재할 수 있습니다.
PostgreSQL 실행계획에 표시되는 Index Scan은 단순히 “인덱스만 뒤졌다”는 뜻이 아니다. 인덱스를 따라 필요한 위치를 찾은 뒤, 그 주소를 이용해 실제 테이블에 접근해 처리한 내용까지 함께 보여 주는 오퍼레이션임을 기억하자.
아래의[ SQL-1]을 실행해 실행계획을 살펴보자. [SQL-1]을 위해서는 x02(shop_id) 인덱스가 존재해야 한다. 앞의 과정에서 이미 생성한 인덱스다.
[SQL-1]
EXPLAIN (ANALYZE,COSTS OFF)
/*+ IndexScan(t1) */
SELECT TO_CHAR(t1.ord_dtm,'YYYYMM')
,COUNT(*) cnt
FROM tr_ord_big t1
WHERE t1.ord_st = 'PKUP'
AND t1.shop_id = 'S281'
AND t1.pkup_dtm >= TO_DATE('20221101','YYYYMMDD')
AND t1.pkup_dtm < TO_DATE('20221201','YYYYMMDD')
GROUP BY TO_CHAR(t1.ord_dtm,'YYYYMM');
GroupAggregate (actual time=46.508..46.510 rows=1 loops=1)
Group Key: (to_char(ord_dtm, 'YYYYMM'))
-> Sort (actual time=46.322..46.365 rows=504 loops=1)
Sort Key: (to_char(ord_dtm, 'YYYYMM'))
Sort Method: quicksort Memory: 36kB
-> Index Scan using tr_ord_big_x02 on tr_ord_big t1 (actual time=15.934..46.205 rows=504 loops=1)
Index Cond: ((shop_id) = 'S281')
Filter: (((ord_st) = 'PKUP') AND (pkup_dtm >= '20221101' AND (pkup_dtm < '20221201')
Rows Removed by Filter: 15624
Planning Time: 0.909 ms
Execution Time: 47.624 ms
SQL
복사
실행계획을 살펴보면 x02 인덱스를 Index Scan 오퍼레이션으로 처리하고 있다. 그리고 Index Scan 오퍼레이션에는 Index Con와 Filter 정보가 포함되어 있다. Index Cond에서는 shop_id 조건을 처리하고 있으며, Filter에서는 ord_st와 pkup_dtm 조건이 처리되고 있다. Index Scan에서 사용한 x02 인덱스에는 shop_id 컬럼만 존재한다. 그러므로, shop_id 조건은 인덱스에서, ord_st와 pkup_dtm 은 테이블의 레코드 레벨에서 조건이 처리되었다고 판단할 수 있다. 간단하게 ‘Index Cond에 나온 조건은 인덱스에서, Filter에 나온 조건은 테이블에서 처리한다’라고 정의할 수 있다. 하지만 100% 그렇지는 않다. 간혹 인덱스에서 처리한 조건이 Filter에 나오기도 한다. 어쨋든, ‘일반적’으로 Filter에는 테이블에서 처리한 조건이 나오는 것은 사실이다.
PostgreSQL의 실행계획은 테이블 접근 과정을 별도의 오퍼레이션으로 보여주지 않는다. 이는, MySQL도 마찬가지다. 테이블 접근을 얼마나 했는지는 SQL 성능에 큰 부분을 차지한다. 그러므로 우리는 Index Scan 오퍼레이션에는 테이블 접근이 포함되어 있음을 인지하고, Index Scan 오퍼레이션을 만나면, 몇 건의 레코드에 접근했을지 찾아낼 수 있어야 한다.
테이블 접근을 얼마나 했는지가 왜 SQL 성능에 큰 부분을 차지하는가? 랜덤 I/O
실행계획의 Index Scan에 출력된 레코드 관련 수치는 다음 두 가지가 있다.
•
Actual의 Rows: 504
•
Rows Removed by Filter: 15,624
위의 두 수치는, Index Scan에 포함된 수치이지만, 인덱스가 아닌, 테이블 내에서 처리된 수치다. 위의 두 수치를 그림으로 나타내면 다음과 같다. Actual의 Rows와 Rows Removed by Filter 모두 테이블에서 처리된 수치다. 그렇다면, 인덱스에서 찾은 데이터 수는 몇 건일까?
실행계획에 표시되지 않았지만, 인덱스에서 찾은 데이터 수는, Actual의 Rows(504)와 Rows Removed by Filter(15,624)를 더하면 된다. 다시 말해, 16,128이 인덱스에서 찾아낸 데이터 건수가 되며, 그만큼 테이블의 레코드에 접근이 발생해다. PostgreSQL의 Index Scan은 테이블 접근 관련 수치도 담고 있음을 기억하기 바란다.
참고로, ORACLE의 실행계획을 살펴보자. 우리가 실습 중인 데이터베이스와 동일하게 ORACLE에 구성하고, [SQL-1]을 실행하면 다음과 같다.
CREATE INDEX STARTDBORA.TR_ORD_BIG_X02 ON STARTDBORA.TR_ORD_BIG(SHOP_ID);
SELECT TO_CHAR(T1.ORD_DTM,'YYYYMM')
,COUNT(*) CNT
FROM STARTDBORA.TR_ORD_BIG T1
WHERE T1.ORD_ST = 'PKUP'
AND T1.SHOP_ID = 'S281'
AND T1.PKUP_DTM >= TO_DATE('20221101','YYYYMMDD')
AND T1.PKUP_DTM < TO_DATE('20221201','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DTM,'YYYYMM');
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 14631 | |
| 1 | HASH GROUP BY | | 1 | 411 | 1 |00:00:00.04 | 14631 | 1422K|
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TR_ORD_BIG | 1 | 411 | 504 |00:00:00.07 | 14631 | |
|* 3 | INDEX RANGE SCAN | TR_ORD_BIG_X02 | 1 | 88227 | 16128 |00:00:00.01 | 39 | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T1"."ORD_ST"='PKUP' AND "T1"."PKUP_DTM">=TO_DATE(' 2022-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"T1"."PKUP_DTM"<TO_DATE(' 2022-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
3 - access("T1"."SHOP_ID"='S281')
SQL
복사
ORACLE의 실행계획은 인덱스를 활용한 오퍼레이션(Id=3, INDEX RANGE SCAN)과 테이블 접근에 대한 오퍼레이션(Id=2, TABLE ACCESS BY INDEX ROWID BATCHED)을 별도로 보여주고 있다. 이에 맞게 수치도 PostgreSQL과는 다르게 표현해주고 있다. ORACLE은 인덱스에서 찾은 데이터 건수(INDEX RANGE SCAN의 A-Rows, 16,128)와 테이블의 레코드에서 걸러져 결과로 출력된 건수(TABLE ACCESS BY INDEX ROWID BATCHED의 A-Rows, 504)를 직접적으로 보여준다. 이 두 수치의 차이를 구하면 PostgreSQL의 Rows Removed by Filter와 같은 수치가 된다.
준비한 내용은 여기까지입니다.
데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.
•
SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터
•
SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!
•
그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서
책 정보 링크>>> 
SQL STARTER For MySQLSQL STARTER For MySQL
SQL STARTER For MySQLSQL STARTER For MySQL