Search

어느 컬럼에 인덱스를 만들 것인가?

본 글은, 오프라인 강의인 StartUP Tuning For PostgreSQL을 책으로 펼치기 위해 정리 중인 글입니다. 아직은 써나가는 중이므로 문장이 부자연스러울 수 있습니다. 또한, 부분적인 내용만 일부 공유하고 있어, 전체적인 설명 흐름이 맞지 않는 부분이 존재할 수 있습니다.
본격적으로 SQL에 따라 필요한 인덱스 컬럼을 판단하고 인덱스를 만들어볼 차례다.
아래의 [SQL-1]과 실행계획을 살펴보자.
[SQL-1] EXPLAIN (ANALYZE,COSTS OFF) SELECT t1.* 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'); Seq Scan on tr_ord_big t1 (actual time=60.547..2009.541 rows=504 loops=1) Filter: (((ord_st)::text = 'PKUP'::text) AND ((shop_id)::text = 'S281'::text) AND (pkup_dtm >= to_date('20221101'::text, 'YYYYMMDD'::text)) AND (pkup_dtm < to_date('20221201'::text, 'YYYYMMDD'::text))) Rows Removed by Filter: 26291268 Planning Time: 0.086 ms Execution Time: 2010.096 ms
SQL
복사
[SQL-1]의 실행계획을 살펴보면, Seq Scan으로 조건에 필요한 데이터를 검색하고 있다. Seq Scan 과정에서 약 2초(2009.541 ms)가 걸렸다. 연습삼아, Seq Scan의 오퍼레이션을 해석해보면 다음과 같다.
•
Seq Scan on tr_ord_big t1: tr_ord_big 테이블 전체를 스캔
•
actual time=60.547..2009.541, rows=504, loops=1
◦
첫 레코드를 반환하기까지 60.547ms가 걸렸으며, 완료하는데 2009.541ms가 걸림(time)
◦
Seq Scan을 통해 504 건의 데이터를 찾아냄(rows)
◦
Seq Scan은 한 번 반복되었다.(loops)
•
Filter ord_st, shop_id, pkup_dtm
◦
테이블의 전체 레코드를 읽으면서, 각 레코드 별로, ord_st, shop_id, pkup_dtm 조건을 필터링한다.
•
Rows Removed by Filter: 26,291,268
◦
Seq Scan 과정에서 접근했지만, Filter 조건에 만족하지 않아 제외된 데이터 수
◦
Tip: 실행계획에서 Rows Removed by Filter가 높다면, 개선할 수 있는 포인트를 고민해 볼 필요가 있다.
•
Seq Scan에서 검색한 총 레코드 수
◦
actual의 rows(504) + Rows Removed by Filter(26,291,268)
위 내용을 가만히 생각해보면, 504건의 데이터를 찾아내기 위해 2천 6백만건 가까이 읽고 버린 셈이다. 이처럼 ‘읽고서 필요 없어 버리는 작업’이 바로 비효율이다. 이러한 비효율을 줄일 수 있다면, SQL 성능은 자연스럽게 좋아진다.
Seq Scan을 피하기 위해서는 [SQL-1]에 적절한 인덱스를 생성해야 한다. 인덱스는 WHERE 절에 사용된 조건 컬럼에 대해 구성한다. [SQL-1]의 WHERE 절에는 다음 세 개의 컬럼이 사용 중이다.
•
ord_st, shop_id, pkup_dtm
[SQL-1]의 성능을 위해서 위 세 개의 컬럼 중에 어떤 컬럼을 인덱스 처리해야 할지 고민해보자. 사실, 위 세 개의 컬럼을 조합한 하나의 복합 인덱스(Composite Index)를 만들면 [SQL-1]의 성능에 가장 유리할 수 있다. 하지만, 아직은 복합 인덱스를 고려할 단계는 아니다. 지금은 단 하나의 컬럼만 사용하는 단일 인덱스(Single Column Index)에 집중하자.

ord_st 인덱스

어느 컬럼에 인덱스를 만드는 것이 좋다라고 결정하기 위해, 각각의 컬럼으로 인덱스를 만들면 어떻게 데이터를 찾아낼까? 그 과정 중에 비효율은 없을까? 란 방식으로 접근해보자.
먼저, ord_st 컬럼에 인덱스를 만들었다고 가정해보자. 해당 인덱스의 리프 블록은 다음과 같이 구성된다.
•
리프 블록에는 ord_st의 모든 값이 정렬되어 저장되어 있다.
•
리프 블록에는 ord_st의 값이 실제 속한 레코드를 찾아갈 수 있는 주소(ctid)가 같이 저장되어 있다.
[SQL-1]에는 ord_st 외에도, shop_id와 pkup_dtm이 WHERE 절에 사용되고 있다. ord_st 인덱스로 [SQL-1]을 처리한다고 가정했을 때, 각 조건을 처리하는 과정을 정리해보면 다음과 같다.
1.
ord_st 조건: ord_st 인덱스에서 처리
1-a. 수직적 탐색: ord_st가 ‘PKUP’이 포함된 리프 블록을 찾아간다.
1-b. 수평적 탐색: ord_st가 ‘PKUP’을 초과하는 값이 나올때까지 리프 블록을 차례대로 스캔한다.
1-c. 테이블 접근: 수평적 탐색을 하면서 찾은 ctid를 이용해 테이블의 레코드에 접근한다.
2.
shop_id 조건: 테이블에서 처리
2-a. ord_st 인덱스로 찾은 주소로 접근한 레코드에서, shop_id가 ‘S281’인지 확인
3.
pkup_dtm 조건: 테이블에서 처리
3-a. ord_st 인덱스로 찾은 주소로 접근한 레코드에서 pkup_dtm이 2022년 11월인지 확인
위 과정을 그림으로 그려보면 아래와 같다.
[SQL-1]은 세 개의 조건이 AND로 묶여 있기 때문에, 1부터 3까지의 과정을 모두 통과한 레코드만 결과에 포함된다. 어느 하나의 단계라도 통과하지 못한 레코드는 결과에 포함되지 못한다. 그리고, 1번 단계는 인덱스에서 2번과 3번 단계는 테이블에서 처리된다는 점을 이해하기 바란다.
위 처리 과정 중에 가장 중요한 성능 포인트는 1-c다. 1-c에서 찾아낸 ctid만큼 테이블의 레코드에 접근해야 한다. 테이블의 레코드에 접근해야 하는 이유는 shop_id와 pkup_dtm에 대한 조건을 확인하기 위해서다. 테이블의 레코드에 접근 횟수가 높을수록 SQL 성능은 느려진다. 1-c에서 몇 건의 레코드에 접근해야 하는지는, 아래 SQL로 확인할 수 있다. 인덱스에는 ord_st에 대한 값만 존재하므로, 아래와 같이 ord_st 조건만 처리한 결과 건수가 바로 접근해야 할 테이블의 레코드 수가 된다.
SELECT COUNT(*) cnt FROM tr_ord_big t1 WHERE t1.ord_st = 'PKUP'; -- 26,291,052 건
SQL
복사
위 SQL을 실행하면, 26,291,052 건(약 2천 6백만)이라는 값이 출력된다. 이는, [SQL-1]을 위해 ord_st 인덱스를 사용하면, 인덱스를 통해 약 2천 6백만건의 데이터를 찾을 수 있음을 의미하며, 해당 건수만큼 테이블의 레코드에 접근해야 함을 의미한다. ord_st 인덱스를 사용해 PKUP이 포함된 리프 블록을 찾아가는 수직적 탐색은 매우 빠르게 처리된다. 하지만, 그 후에 수평적 탐색을 하면서 테이블 접근을 2천 6백만번 정도 해야 한다면, 그 SQL은 절대 빠를 수 없다.
어쨋든 ord_st 인덱스를 이용해 [SQL-1]을 처리하면, 2천 6백만건 정도의 테이블 레코드에 접근해야 하며, 테이블 접근 후에는 2번(shop_id 조건)과 3번 단계(pkup_dtm 조건)를 처리해야 한다. 1번 단계를 거쳐 테이블에 접근해서 2번과 3번 조건을 통과한 데이터는 몇 건일까? 아래와 같이 [SQL-1]의 SELECT 절을 COUNT로 변경하면 알 수 있다. 504 건이 나온다.
SELECT 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'); -- 504 건
SQL
복사
1단계를 거쳐 26,291,052 건의 테이블의 레코드에 접근했다. 그리고 2번, 3번 단계를 거치고 최종 조회되는 데이터는 504건이다. 이 수치를 그림에 포함해보면 다음과 같다.
인덱스를 통해, 26,291,052 건의 레코드를 찾았으며, 그 만큼 테이블의 레코드에 접근했다. 그리고 최종 결과에는 504건만 출력된다. 결과에 불필요한 레코드를 몇 건 읽었는가? 아래와 같이 구할 수 있다.
•
26,291,052 - 504 = 26,290,548 (결과에 필요 없는 레코드 수)
[SQL-1]에 ord_st 인덱스를 사용한다면, 26,290,548 건의 비효율적인 접근이 있다라고 할 수 있다.

shop_id 인덱스

이번에는 ord_st가 아닌 shop_id 컬럼에 인덱스를 만들었다고 가정해보자. 여러분이 괄호를 직접 채워보기 바란다.
shop_id 컬럼에 인덱스를 만들었다고 가정해보자. 해당 인덱스의 리프 블록은 다음과 같이 구성된다.
•
리프 블록에는 ( )의 모든 값이 정렬되어 저장되어 있다.
•
리프 블록에는 ( )의 값이 실제 속한 레코드를 찾아갈 수 있는 주소(ctid)가 같이 저장되어 있다.
[SQL-1]에는 shop_id 외에도, ord_st와 pkup_dtm이 WHERE 절에 사용되고 있다. shop_id 인덱스로 [SQL-1]을 처리한다고 가정했을 때, 각 조건을 처리하는 과정을 정리해보면 다음과 같다.
1.
shop_id 조건: ( ) 에서 처리
1-a. 수직적 탐색: shop_id가 ( )이 포함된 리프 블록을 찾아간다.
1-b. 수평적 탐색: shop_id가 ( )을 초과하는 값이 나올때까지 리프 블록을 차례대로 ( )한다.
1-c. 테이블 접근: ( ) 탐색을 하면서 찾은 ctid를 이용해 테이블의 ( )에 접근한다.
2.
ord_st 조건: ( )에서 처리
2-a. ( ) 인덱스로 찾은 주소로 접근한 레코드에서, ord_st가 ‘PKUP’인지 확인
3.
pkup_dtm 조건: ( )에서 처리
3-a. ( ) 인덱스로 찾은 주소로 접근한 레코드에서 pkup_dtm이 2022년 11월인지 확인
위 과정을 그림으로 그려보면 아래와 같다.
위 처리 과정 중에 가장 중요한 성능 포인트는 ( )다. ( )에서 찾아낸 ctid만큼 테이블의 ( )에 ( )해야 한다. 테이블의 레코드에 ( ) 횟수가 높을수록 SQL 성능은 느려진다. 아래 SQL로 shop_id 인덱스를 거쳐 ( )해야 할 테이블의 ( ) 수를 알 수 있다.
SELECT COUNT(*) cnt FROM tr_ord_big t1 WHERE t1.( ) = '( )'; -- 16,128 건
SQL
복사
위 SQL을 실행하면, 16,128건이 출력된다.
이는, [SQL-1]을 위해 ( ) 인덱스를 사용하면, 인덱스를 통해 ( ) 건의 데이터를 찾을 수 있음을 의미하며, 해당 건수만큼 테이블의 ( )에 ( )해야 함을 의미한다. 테이블의 레코드에 접근한 후에 ord_st와 pkup_dtm 조건을 확인하면 남겨지는 데이터는 504건이다.
1단계를 거쳐 16,128 건의 테이블의 레코드에 접근했다. 그리고 2번, 3번 단계를 거치고 최종 조회되는 데이터는 504건이다. 결과에 불필요한 레코드를 몇 건 읽었는가? 아래와 같이 구할 수 있다.
•
( ) - ( ) = ( ) (결과에 필요 없는 레코드 수)
[SQL-1]에 shop_id 인덱스를 사용한다면, ( ) 건의 비효율적인 접근이 있다라고 할 수 있다.

pkup_dtm

마지막으로 pkup_dtm 컬럼에 대한 인덱스를 고민해보자. shop_id 인덱스에 대한 빈칸을 모두 채웠다면 어렵지 않게 pkup_dtm 인덱스의 비효율을 평가할 수 있을 것이다.
pkup_dtm 인덱스를 사용하면, 인덱스 리프에서 찾을 수 있는 데이터 건수는 615,348 건이다. 아래 SQL로 확인할 수 있다.
SELECT COUNT(*) cnt FROM tr_ord_big t1 WHERE t1.( ) ( ) TO_DATE('20221101','YYYYMMDD') AND t1.( ) ( ) TO_DATE('20221201','YYYYMMDD'); -- 615,348
SQL
복사
[SQL-1]을 위해 pkup_dtm 인덱스를 사용하면, 인덱스를 통해 615,348 건의 데이터를 찾을 수 있다. [SQL-1]을 위해 필요한 레코드는 504건이다. 결과에 불필요한 레코드를 몇 건 읽었는지를 계산해보면 다음과 같다.
•
( ) - ( ) = ( ) (결과에 필요 없는 레코드 수)
[SQL-1]에 shop_id 인덱스를 사용한다면, ( ) 건의 비효율적인 접근이 있다라고 할 수 있다.

그래서 어느 컬럼?

지금까지 검토한 내용을 토대로 어느 컬럼에 인덱스를 만들어야 할지 스스로 결정해 보기 바란다.
준비한 내용은 여기까지입니다. 이번에는 약간 실험적인 방법으로 글을 작성해봤습니다. 어떤 방식으로 책을 만드는게 더 좋은지 고민이 많습니다. 고민하다 보면, 진도를 못나가는 문제가 있네요.
데이터 활용이 필요한 모든 분을 위한 SQL 실무서를 소개합니다.
•
SQL 입문자를 위한 실습환경 구성과 SELECT 기초부터
•
SQL 중급자를 위한 GROUP BY와 JOIN, 그리고 분석함수까지!
•
그림과 친절한 설명, 손끝으로 익히는 실습 SQL과 실무형 퀴즈를 더한 올인원 SQL 실무서