Search

PostgreSQL의 Index Full Scan

본 글은, 오프라인 강의인 StartUP Tuning For PostgreSQL을 책으로 펼치기 위해 정리 중인 글입니다. 아직은 써나가는 중이므로 문장이 부자연스러울 수 있습니다. 또한, 부분적인 내용만 일부 공유하고 있어, 전체적인 설명 흐름이 맞지 않는 부분이 존재할 수 있습니다.

사실 난, Index Full Scan이야.

지금까지 다룬 인덱스 기반 탐색은 루트 블록부터 값을 차례로 비교하며 내려가, 목표 데이터가 있는 리프 블록에 도달하는 과정이다. 필자는, 이와 같은 탐색 방법을 ‘효율적인’ 인덱스 탐색이라고 한다. 한편, 인덱스 리프 블록 전체를 순차적으로 읽는 ‘비효율적안’ 인덱스 탐색도 존재한다.
아래 그림의 왼쪽은 효율적인 인덱스 탐색을, 오른쪽은 비효율적인 인덱스 탐색을 보여주고 있다.
위 그림의 오른쪽과 같은 비효율적인 인덱스 탐색을 통상 Index Full Scan이라고 부른다. 안타깝게도, PostgreSQL에서는 실행계획에 Index Full Scan이라는 오퍼레이션을 별도 보여주지 않는다. 비효율적인 인덱스 탐색도 Index Scan 또는 Index Only Scan으로 출력해준다. 그러므로 실행계획에서 해당 오퍼레이션에 사용한 인덱스와 Index Cond 정보를 참고해 Index Full Scan인지 판단해야 한다.
아래 SQL은 2023년 3월 1일 하루치의 주문의 건수를 세고 있다. SQL과 함께 실행계획을 살펴보자.
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF) SELECT COUNT(*) FROM tr_ord_big t1 WHERE t1.ord_dtm >= TO_DATE('20230301','YYYYMMDD') AND t1.ord_dtm < TO_DATE('20230302','YYYYMMDD'); Aggregate (actual time=2.285..2.286 rows=1 loops=1) Buffers: shared hit=7846 -> Index Only Scan using tr_ord_big_x01 on tr_ord_big t1 (actual time=0.027..1.603 rows=22248 loops=1) Index Cond: ((ord_dtm >= to_date('20230301'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20230302'::text, 'YYYYMMDD'::text))) Heap Fetches: 0 Buffers: shared hit=7846 Planning Time: 0.078 ms Execution Time: 2.304 ms
SQL
복사
위 SQL의 실행계획을 살펴보면, x01 인덱스에 대한 Index Only Scan 오퍼레이션이 작동한 것을 알 수 있다. 해당 오퍼레이션의 부가 정보인 Index Cond를 보면 WHERE 조건에 해당하는 날짜 조건이 사용되고 있다. 해당 오퍼레이션은 아래와 같이 상세하게 정리해 볼 수 있다.
•
사용한 인덱스: tr_ord_big_x01 on tr_ord_big t1
◦
x01인덱스는 ord_dtm으로 구성된 인덱스다.
•
actual 수치: time=0.027..1.603 rows=22248 loops=1
•
오퍼레이션: Index Only Scan
◦
1단계 수직적 탐색: 루트에서 리프 블록의 [검색 범위의 시작 위치]까지 찾아간다.
▪
[검색 범위의 시작 위치]는 두 조건 값 중에 작은 값인 2023년 3월 1일이 된다.
▪
루트에서부터 2023년 3월 1일이 포함된 리프를 찾아간다.
◦
2단계 수평적 탐색: 리프 블록을 [검색 범위의 종료 위치]까지 차례대로 읽어나간다.
▪
[검색 범위의 종료 위치]는 두 조건 값 중에 큰 값인 2023년 3월 2일이 된다.
▪
리프 블록을 검색하다가 2023년 3월 2일 이상의 값이 나오면 스캔을 종료한다.
◦
3단계 테이블 접근: Index Only Scan이므로 테이블 접근이 발생하지 않는다.
▪
SQL에서 ord_dtm 컬럼외에 사용하지 않았으므로 테이블 접근이 필요 없다.
•
Buffers 수치: Shared hit = 7846
◦
테이블 접근이 없으므로 인덱스 블록 7,846개를 메모리(Shared)에서 읽었다.
위 SQL은 성능에 특별한 이슈가 없다. 필자에게 위와 같은 SQL에 대한 성능 문의가 들어오고, 위와 같은 실행계획이 나온다면, 일반적으로 ‘정상’이란 판단을 한다.
위 SQL의 단점은 무엇인가? 개발 측면에서 바라보면, 2023년 3월 1일 하루의 데이터를 조회하기 위해 조건을 두 개나 사용하고 있다는 점이다. 얼마나 불편한가? 개발자들은 이런 패턴을 제법 귀찮아한다. 그리고, 자신만의 창의성을 발휘해 아래와 같이 SQL을 작성한다. WHERE 절에 조건절을 하나만 사용해도 되다니, 얼마나 좋은가!?
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF) SELECT COUNT(*) FROM tr_ord_big t1 WHERE TO_CHAR(t1.ord_dtm,'YYYYMMDD') = '20230301'; Aggregate (actual time=5251.477..5251.479 rows=1 loops=1) Buffers: shared hit=12992222 read=35474 -> Index Only Scan using tr_ord_big_x01 on tr_ord_big t1 (actual time=2482.514..5250.574 rows=22248 loops=1) Filter: (to_char(ord_dtm, 'YYYYMMDD'::text) = '20230301'::text) Rows Removed by Filter: 26269524 Heap Fetches: 0 Buffers: shared hit=12992222 read=35474 Planning Time: 0.066 ms Execution Time: 5251.747 ms
SQL
복사
위 SQL의 실행계획을 살펴보자. 이전과 동일한 Index Only Scan을 사용하고 있다. 하지만, 성능은 어마무시한 차이가 난다. 이전 SQL은 총 실행시간이 2.304 ms에 불과했다. 변경된 위의 SQL은 총 실행시간이 무려 5251.747 ms다.
위 SQL의 Index Only Scan 오퍼레이션에는 index Cond에 대한 정보가 없다. PostgreSQL에서 이처럼, Index Cond 정보가 생략되었다면, 인덱스를 ‘효율적’으로 사용하지 못했다는 의미다. 인덱스 리프 블록 전체를 스캔했다는 의미다. 인덱스 리프 블록 전체를 스캔하면서 처리한 조건은 실행계획의 Filter 부분에 표시되어 있다. 그리고 Index Only Scan 오퍼레이션에 Rows Removed by Filter가 추가되어 있다. 이는, 인덱스를 탐색해 접근한 데이터 중에 조건에 맞지 않아 버려진 데이터를 의미한다. 무려 26,269,524 건의 데이터가 검색 조건과 맞지 않아 버려졌다. 얼마나 비효율적인가? 끝으로, Buffers 수치를 살펴보자. 디스크에서 읽은 블록(read) 수치가 35,474에 달한다. 이 수치는, tr_ord_big_x01 인덱스 블록수와 거의 동일하다. 아래 SQL로 tr_ord_big_x01 인덱스를 구성한 블록수를 확인할 수 있다.
-- 바이트 기준 블록수 SELECT pg_relation_size('startdbpg.tr_ord_big_x01') AS bytes, pg_relation_size('startdbpg.tr_ord_big_x01') / (current_setting('block_size')::int) AS blocks; bytes |blocks| ---------+------+ 291667968| 35604| -- 통계 기반(ANALYZE 이후 값으로 근사치) SELECT relpages -- = 블록 수 FROM pg_class WHERE oid = 'startdbpg.tr_ord_big_x01'::regclass; relpages| --------+ 35604|
SQL
복사
기본적으로 인덱스는 컬럼에 저장된 원래의 값을 사용해 만들어진다. 그러므로, WHERE 절에서 인덱스 컬럼을 변형하면, 인덱스를 ‘효율적’으로 사용할 수 없다. 인덱스를 효율적으로 사용할 수 없다는 뜻은 수직적 탐색을 할 수 없음을 뜻한다. 수직적 탐색이 생략된 채로 인덱스를 사용하는 방법은 인덱스 리프 전체를 차례대로 스캔하는 것이다. 이를 보통은 Index Full Scan이라고 한다. 하지만 PostgreSQL은 이러한 오퍼레이션을 실행계획에 별도로 표시해 주지 않는다. 이 챕터를 통해 우리가 기억해야 할 것은 두 가지다.
•
WHERE 절에서 인덱스 컬럼을 가공하지 않는다.
•
PostgreSQL의 실행계회에서, Index Scan과 Index Only Scan 오퍼레이션이 나오면,
◦
Index Cond가 적절한가를 꼭 살펴보자. 주
준비한 내용은 여기까지입니다.
데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.
•
SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터
•
SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!
•
그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서