출간 준비 중인 < SQL TUNER for PostgreSQL · 기본원리편 >의 미리보기입니다.
인덱스 키 값의 정렬 순서와 실제 테이블(힙)에 저장된 데이터의 정렬 순서가 얼마나 일치하는지에 따라 인덱스를 사용한 조회 성능이 달라진다. 소량의 데이터를 조회할 때는 성능 차이가 크지 않지만, 많은 데이터를 조회할수록 성능 차이가 커진다. CLUSTER 명령어를 이용해 저장된 데이터의 실제 정렬 순서를 변경할 수 있으며 이를 통해 특정 인덱스의 효율을 높일 수 있다.
아래 SQL은 ord_dtm 조건을 활용해 2024년 12월의 데이터를 조회하고 있다.
-- [SQL-5-4-3-a]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
SELECT t1.*
FROM startdbpg.tr_ord_big T1
WHERE t1.ord_dtm >= '2024-12-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
Index Scan using tr_ord_big_x01 on tr_ord_big t1 (actual time=0.016..56.340 rows=737568 loops=1)
Index Cond: ((ord_dtm >= '2024-12-01'::date) AND (ord_dtm < '2025-01-01'::date))
Buffers: shared hit=10719
Planning Time: 0.076 ms
Execution Time: 75.994 ms
SQL
복사
위 SQL의 실행계획을 살펴보면 x01(ord_dtm) 인덱스를 사용해 약 73만 건의 레코드를 검색하고 있다.
SELECT 절에서 ‘*’를 사용해 모든 컬럼을 출력하고 있기 때문에 인덱스에서 찾은 만큼 테이블 접근이 발생한다. 약 73만 건의 레코드를 x01 인덱스로 찾아내면서 10,719번의 메모리 I/O(Buffers: shared hit)가 발생했다. 실행 시간은 75.994ms다.
이번에는 tr_ord_big에서 shop_id가 ‘S100’인 데이터를 조회해 보자. 다음과 같다. IndexScan 방식으로 처리되도록 일부러 힌트를 추가했다. 힌트를 제거하면 Bitmap Scan으로 처리될 가능성이 높다.
-- [SQL-5-4-3-b]
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ IndexScan(t1) */
SELECT t1.*
FROM startdbpg.tr_ord_big T1
WHERE t1.shop_id = 'S010';
Index Scan using tr_ord_big_x02 on tr_ord_big t1 (actual time=0.083..409.508 rows=646596 loops=1)
Index Cond: ((shop_id)::text = 'S010'::text)
Buffers: shared read=165562
Planning Time: 0.140 ms
Execution Time: 435.844 ms
SQL
복사
[SQL-5-4-3-b]는 x02(shop_id) 인덱스를 이용해 약 64만 건의 레코드를 출력한다. [SQL-5-4-3-a]보다 더 적은 양의 데이터를 조회했지만, 디스크 I/O(Buffers: shared read)가 무려 165,562다. 메모리 I/O보다 느린 디스크 I/O가 [SQL-5-4-3-a]보다 16배나 많이 발생했다. 실행 시간도 435.844ms로 [SQL-5-4-3-a]의 5배를 넘었다. 조회하는 데이터 건수가 더 적음에도 불구하고 성능은 오히려 더 나빠졌다.
이러한 성능 차이는 인덱스 컬럼의 정렬 순서와 테이블의 실제 데이터 저장 순서가 얼마나 일치하는지에 따라 발생한다.
tr_ord_big 테이블에는 시간 순서대로 주문 데이터가 입력되었을 것이다. 그러므로 ord_dtm(주문일시)의 정렬 순서와 테이블의 실제 저장 순서가 거의 일치할 가능성이 높다. 힙 구조는 데이터의 정렬 순서를 보장하지 않지만, 일반적으로는 입력된 순서대로 저장되는 경향이 있다. (물론 데이터가 빈번하게 수정되거나 삭제된다면 입력 순서와 힙 구조내 데이터 정렬 순서는 차이가 커질 수 있다.)
반면 tr_ord_big 테이블의 주문이 shop_id(매장ID) 순서대로 저장되었을 가능성은 거의 없다. 그러므로 shop_id의 정렬 순서와 테이블의 실제 저장 순서는 일치하지 않는다.
PostgreSQL은 컬럼과 테이블 간의 정렬 순서의 일치도를 통계로 관리한다. pg_stats 뷰의 correlation(상관계수) 항목을 통해 확인할 수 있다. 다음 SQL을 실행해 보자.
-- [SQL-5-4-3-c]
SELECT t1.attname -- 컬럼명
,t1.correlation -- 테이블의 실제 데이터 정렬과 해당 컬럼의 정렬에 대한 상관계수
FROM pg_stats t1
WHERE t1.tablename = 'tr_ord_big'
AND t1.attname in ('ord_dtm','shop_id')
ORDER BY t1.correlation DESC;
attname|correlation|
-------+-----------+
ord_dtm| 1.0|
shop_id| 0.23215733|
SQL
복사
pg_stats의 correlation은 각 컬럼과 실제 데이터의 정렬 순서의 일치성을 나타내는 수치다. -1부터 1까지의 값을 가지며, 1은 컬럼과 실제 데이터의 정렬 순서가 거의 일치하는 상태다. -1은 완전히 역순인 경우다. 0에 가까울수록 해당 컬럼과 테이블의 정렬 순서가 일치하지 않음을 의미한다.
위 결과를 보면 ord_dtm의 correlation은 1이다. ord_dtm의 값과 실제 데이터의 정렬 순서가 거의 일치함을 의미한다. 반면에 shop_id는 0.23이다. 이는 테이블의 데이터 저장 순서와 shop_id의 정렬 순서가 크게 다르다는 것을 의미한다.
아래의 [그림-5-4-3-a]는 상관계수에 따른 성능 차이를 보여준다.
위 그림은 ord_dtm 인덱스(좌측)와 shop_id 인덱스(우측)로 각각 6건의 레코드를 검색하는 상황을 보여준다. ord_dtm으로 찾은 6건의 레코드는 데이터 블록 2번과 3번에 모두 존재하기 때문에 단 2개의 데이터 블록만 접근하면 된다. 반면에 shop_id로 찾은 6건의 레코드는 5개의 서로 다른 데이터 블록에 흩어져 있어서 상대적으로 더 많은 블록 접근이 필요하다.
correlation이 1에 가까우면 인덱스 리프에서 연속된 레코드들이 테이블에서도 같은 블록이나 인접한 블록에 모여 있다. 따라서 인덱스 순서대로 테이블을 읽을 때 같은 블록에서 여러 레코드를 연속으로 가져올 수 있다. 반면 correlation이 낮으면 레코드들이 여러 블록에 흩어져 있어 레코드마다 다른 블록에 접근해야 한다. 결과적으로 데이터 블록의 접근 횟수가 늘어나 성능이 저하된다. 이처럼 인덱스 순서대로 데이터가 얼마나 잘 모여 있는가를 클러스터링(Clustering)이라 부른다.
PostgreSQL에서는 CLUSTER 명령어를 통해 테이블에 저장된 실제 레코드의 정렬 순서를 조정할 수 있다. 다음과 같이 tr_ord_big을 이용해 tr_ord_big_shop이라는 테이블로 새로 만들고, 해당 테이블에 x01(ord_dtm) 인덱스와 x02(shop_id) 인덱스를 만들도록 하자. (아래 명시된 시간은 필자의 노트북 환경 기준이다. 각자 환경에 따라 다를 것이다.)
-- [SQL-5-4-3-d]
DROP TABLE IF EXISTS startdbpg.tr_ord_big_shop;
CREATE TABLE startdbpg.tr_ord_big_shop as
SELECT * FROM startdbpg.tr_ord_big; -- 48초
CREATE INDEX tr_ord_big_shop_x01 ON startdbpg.tr_ord_big_shop(ord_dtm); -- 8초
CREATE INDEX tr_ord_big_shop_x02 ON startdbpg.tr_ord_big_shop(shop_id); -- 21초
SQL
복사
위 SQL을 완료했다면 tr_ord_big_shop을 shop_id 컬럼 기준으로 CLUSTER 처리할 차례다. x02(shop_id) 인덱스를 활용하면 된다. 아래와 같이 CLUSTER 후 베큠과 통계 수집까지 수행하도록 하자.
-- [SQL-5-4-3-e]
CLUSTER startdbpg.tr_ord_big_shop USING tr_ord_big_shop_x02; -- 2분
VACUUM startdbpg.tr_ord_big_shop;
ANALYZE startdbpg.tr_ord_big_shop;
SQL
복사
아래 SQL로 새로 만든 tr_ord_big_shop 테이블의 ord_dtm 컬럼과 shop_id 컬럼의 correlation을 확인해 보자. 원래 테이블인 tr_ord_big과 반대로 ord_dtm은 0.23으로 줄어들고 shop_id는 1로 높아졌다.
-- [SQL-5-4-3-f]
SELECT t1.attname -- 컬럼명
,t1.correlation -- 테이블의 실제 데이터 정렬과 해당 컬럼의 정렬에 대한 상관계수
FROM pg_stats t1
WHERE t1.tablename = 'tr_ord_big_shop'
AND t1.attname in ('ord_dtm','shop_id')
ORDER BY t1.correlation DESC;
attname|correlation|
-------+-----------+
shop_id| 1.0|
ord_dtm| 0.23091064|
SQL
복사
이제 앞에서 실행했던 [SQL-5-4-3-a](ord_dtm으로 약 73만 건 조회)와 [SQL-5-4-3-b](shop_id로 약 64만 건 조회)를 FROM 절의 테이블을 tr_ord_big_shop으로 변경해 다시 실행해 보자. 이때, [SQL-5-4-3-a]를 재실행할 때는 IndexScan 힌트를 추가하고 [SQL-5-4-3-b]를 재실행할 때는 IndexScan 힌트를 제거한다. 다음과 같다.
-- [SQL-5-4-3-g]: [SQL-5-4-3-a]와 동일(대상 테이블 변경, 힌트 추가), x01(ord_dtm)로 73만 건 조회
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
/*+ IndexScan(t1) */ -- > IndexScan 힌트 추가
SELECT t1.*
FROM startdbpg.tr_ord_big_shop t1 -- > 대상 테이블 변경
WHERE t1.ord_dtm >= '2024-12-01'::date
AND t1.ord_dtm < '2025-01-01'::date;
Index Scan using tr_ord_big_shop_x01 on tr_ord_big_shop t1 (actual time=0.111..172.277 rows=737568 …
Index Cond: ((ord_dtm >= '2024-12-01'::date) AND (ord_dtm < '2025-01-01'::date))
Buffers: shared hit=654509
Planning Time: 0.314 ms
Execution Time: 198.989 ms
SQL
복사
-- [SQL-5-4-3-h]: [SQL-5-4-3-b]와 동일(대상 테이블 변경, 힌트 제거), x02(shop_id)로 약 64만 건 조회
EXPLAIN (ANALYZE,BUFFERS,COSTS OFF)
-- > IndexScan 힌트 제거
SELECT t1.*
FROM startdbpg.tr_ord_big_shop t1 -- > 대상 테이블 변경
WHERE t1.shop_id = 'S010';
Index Scan using tr_ord_big_shop_x02 on tr_ord_big_shop t1 (actual time=0.031..64.015 rows=646596 …
Index Cond: ((shop_id)::text = 'S010'::text)
Buffers: shared hit=9170
Planning Time: 0.125 ms
Execution Time: 87.190 ms
SQL
복사
원래의 tr_ord_big 테이블과 shop_id로 CLUSTER 처리된 tr_ord_big_shop 테이블의 테스트 결과를 정리하면 다음 표와 같다. 컬럼과 테이블 간의 correlation 수치에 따라 성능이 뒤바뀌는 것을 알 수 있다.
5-4-3-1. 실무와 CLUSTER
CLUSTER는 특정 인덱스 순서에 맞춰 테이블 전체를 재정렬한다. 이러한 CLUSTER 작업은 SQL에 따라 성능에 도움이 될 수도 있으며, 오히려 성능을 저하시킬 수도 있다. 앞에서 살펴본 것처럼 shop_id로 CLUSTER 처리된 테이블은 shop_id 조건 처리에는 좋은 성능을 내지만, ord_dtm 조건 처리에는 좋지 못한 성능을 낸다. 결국 CLUSTER를 적용하기 위해서는 해당 테이블의 실제 데이터 정렬 순서가 전체적으로 어떤 영향을 미칠지 평가할 필요가 있다. 하지만 실무에서는 이러한 평가가 매우 어렵다.
또한 CLUSTER 작업은 테이블 전체에 배타적 락을 걸기 때문에 운영 중인 시스템에서는 사용하기 어렵다. 작업하는 동안 해당 테이블에 대한 모든 읽기/쓰기가 차단되기 때문이다. 특히 대용량 테이블의 경우 CLUSTER 작업에 상당한 시간이 소요될 수 있어 더욱 신중해야 한다.
결과적으로 실무에서는 운영 중인 시스템에 CLUSTER 작업을 거의 시도하지 않는다. 다만 구시스템에서 데이터를 이행했으며 아직 오픈 전인 시스템이라면 CLUSTER 작업을 고려해볼 수 있다. 실제 데이터 입력 순서나 자주 조회하는 범위 조건에 맞게 데이터를 정렬하면 오픈 후 조회 성능 향상을 기대할 수 있다. 이는 이행 프로젝트에서 활용할 수 있는 중요한 성능 팁이다.