5-4-3. CLUSTER

출간 준비 중인 < 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의 실행계획을 살펴보면 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-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|

pg_stats의 correlation은 각 컬럼과 실제 데이터의 정렬 순서의 일치성을 나타내는 수치다. -1부터 1까지의 값을 가지며, 1은 컬럼과 실제 데이터의 정렬 순서가 거의 일치하는 상태다. -1은 완전히 역순인 경우다. 0에 가까울수록 해당 컬럼과 테이블의 정렬 순서가 일치하지 않음을 의미한다.