PostgreSQL: 쿼리 최적화 및 인덱스 작업 이해

PostgreSQL 인덱스 최적화: 인덱스가 무시되는 이유와 해결책

인덱스를 생성했다고 해서 PostgreSQL이 항상 이를 활용하는 것은 아닙니다. 개발자들은 인덱스가 존재함에도 불구하고 쿼리가 느리게 실행되어 전체 테이블 스캔(Seq Scan)으로 이어지는 상황을 자주 마주합니다. 데이터베이스 성능 최적화를 위해서는 PostgreSQL의 쿼리 플래너 메커니즘을 이해하고, EXPLAIN ANALYZE 지표를 해석하며, 실행 전략 선택에 영향을 미치는 요인들을 아는 것이 중요합니다. 이 글에서는 대규모 데이터셋을 사용한 실제 사례를 통해 이러한 측면들을 자세히 살펴보겠습니다.

준비: 실험을 위한 4백만 개의 행

인덱스가 어떻게 작동하는지 보여주기 위해, 기본 키나 인덱스가 없는 t_test라는 테스트 테이블을 생성하고 4백만 개의 레코드를 채워 넣겠습니다. 이를 통해 인덱싱 전후의 성능 차이를 명확하게 확인할 수 있을 것입니다.

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

인덱스 없이 쿼리를 실행할 때, 예를 들어 EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;를 실행하면, 4백만 개의 모든 행을 순회하는 Seq Scan이 발생하며 약 126밀리초가 소요되는 것을 볼 수 있습니다. 이는 인덱스가 해결하고자 하는 문제의 전형적인 예시입니다.

EXPLAIN과 cost 지표 이해하기

EXPLAIN ANALYZE는 PostgreSQL에서 쿼리 실행 계획을 분석하는 주요 도구입니다. 이는 플래너가 쿼리를 어떻게 실행할 것인지에 대한 자세한 정보(예: 선택된 연산자(Seq Scan, Index Scan 등), 실행 시간)와 함께, 핵심적으로 cost 지표를 제공합니다.

이전 예시에서 cost=0.00..71622.00 출력을 살펴보겠습니다. 이 숫자는 실제 밀리초 단위의 시간이 아니라, PostgreSQL이 여러 실행 계획을 비교하는 데 사용하는 상대적인 계수입니다. 이를 '앵무새'와 같은 추상적인 비용 단위로 생각할 수 있습니다. 더 깔끔한 실험을 위해 병렬 처리를 비활성화하는 것이 좋습니다:

SET max_parallel_workers_per_gather TO 0;

비용은 디스크 블록 수와 각 행을 처리하는 비용 등 여러 구성 요소에서 파생됩니다:

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 8KB blocks
SHOW cpu_tuple_cost;    -- 0.01 (cost of processing a row)
SHOW cpu_operator_cost; -- 0.0025 (cost of an operator/function)

우리의 Seq Scan에 대한 대략적인 비용 공식은 다음과 같습니다:

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

결과는 71622에 가까울 것입니다. cost가 시스템 하드웨어의 특정 사항을 고려하지 않는다는 점을 이해하는 것이 중요합니다. 따라서 두 가지 다른 쿼리의 cost를 비교하여 실제 실행 시간을 추정하는 것은 정확하지 않습니다. 하지만 단일 쿼리 내에서는 계획에서 가장 '비용이 많이 드는' 부분을 식별하는 데 유용합니다.

기본 인덱스 사용: BTree와 그 장점

PostgreSQL에서 가장 일반적인 인덱스 유형은 BTree입니다. BTree는 효율적인 검색, 정렬 및 높은 동시성을 제공합니다. id 컬럼에 BTree 인덱스를 생성해 보겠습니다:

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

인덱스를 생성한 후, 쿼리의 cost는 크게 감소하고(71,622에서 8.45로), 데이터 검색 시간은 밀리초 단위로 단축됩니다. BTree 인덱스는 정렬 작업 및 최소/최대 값 찾기에도 효과적입니다:

• 정렬: PostgreSQL은 원하는 방향으로 인덱스를 순회하여(DESC의 경우 Index Scan Backward) LIMIT에 도달하면 멈추는 방식으로 ORDER BY를 수행하기 위해 인덱스를 사용할 수 있습니다.

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

• 최소/최대: min(id) 또는 max(id)를 결정하기 위해 플래너는 Index Only Scan을 사용하여 인덱스에서 첫 번째 또는 마지막 항목을 읽습니다.

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

여러 조건 처리: 비트맵 스캔

PostgreSQL은 비트맵 스캔(Bitmap Scan)을 사용하여 단일 인덱스에 대한 여러 OR 조건이 있는 쿼리를 효율적으로 처리할 수 있습니다.

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

이 시나리오에서 PostgreSQL은 각 조건에 대해 Bitmap Index Scan을 수행한 다음, BitmapOr을 사용하여 결과를 비트맵으로 결합하고, 그 후에야 메인 테이블(Bitmap Heap Scan)에 접근하여 전체 행을 검색합니다. 이는 여러 번의 테이블 스캔을 방지하고 데이터 접근을 최적화합니다.

플래너가 인덱스를 무시하는 이유: 선택성과 통계

인덱스가 사용되지 않는 가장 흔한 이유 중 하나는 쿼리 조건의 낮은 선택성(selectivity)입니다. 선택성은 주어진 조건과 일치하는 테이블 내 행의 비율을 의미합니다. 만약 조건이 테이블의 상당 부분을 차지한다면, 플래너는 Index Scan보다 Seq Scan이 더 효율적이라고 판단할 수 있습니다.

예시를 들어보겠습니다. name 필드에 인덱스를 생성하겠습니다:

CREATE INDEX idx_name ON t_test (name);

존재하지 않는 이름(EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';)을 검색하면 인덱스가 사용되고, PostgreSQL은 항상 최소 한 개의 행을 예상하므로 rows는 1이 됩니다. 하지만 쿼리가 테이블의 상당 부분을 차지하는 경우, 예를 들어 'hans' OR 'paul'(이는 테스트 테이블의 100%를 구성함)과 같이 검색하면:

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

이 경우 PostgreSQL은 Seq Scan을 수행할 것입니다. 이유는 간단합니다. 전체 인덱스를 스캔한 다음 4백만 개의 각 행에 대해 테이블에 접근하는 것이 전체 테이블을 순차적으로 읽는 것보다 더 많은 비용이 들기 때문입니다. 플래너는 데이터 분포 통계를 기반으로 결정을 내립니다. 만약 이 통계가 오래되었다면(예: 대량의 변경 후), 결정이 최적이 아닐 수 있습니다.

물리적 데이터 레이아웃의 영향: 상관관계와 CLUSTER

인덱스의 효율성은 디스크에 저장된 데이터의 물리적 정렬 방식에 크게 좌우됩니다. 인덱스를 통해 접근하는 데이터가 넓게 분산되어 있다면, 데이터 검색 속도가 현저히 느려질 수 있습니다. 우리 테이블의 복사본을 만들되, 행 순서를 무작위로 섞어보겠습니다:

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

원본(정렬된) 테이블과 무작위화된 테이블에서 처음 10,000개 레코드를 검색하는 쿼리의 성능을 비교해 봅시다:

• 원본 테이블 (정렬된 데이터):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

여기서는 낮은 버퍼 접근 횟수(예: Buffers: shared hit=3 read=82)를 관찰할 수 있으며, 이는 순차적인 데이터 읽기를 나타냅니다.

• 무작위화된 테이블 (분산된 데이터):

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

이 경우 버퍼 접근 횟수가 상당히 높아질 것입니다(예: Buffers: shared hit=801 read=7210). 이는 데이터가 분산되어 있어 PostgreSQL이 많은 무작위 디스크 읽기를 수행하게 만들고, 이로 인해 실행 시간이 크게 증가하기 때문입니다. 플래너는 심지어 Bitmap Heap Scan으로 전환할 수도 있습니다.

상관관계

PostgreSQL은 pg_stats에서 제공되는 상관관계(correlation) 지표를 사용하여 데이터 정렬 정도를 추적합니다:

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

• correlation ~ 1: 데이터가 물리적으로 정렬되어 있어 디스크 블록의 순차적 읽기가 가능합니다.
• correlation ~ 0: 데이터가 무작위로 분산되어 있어 각 행에 대해 많은 개별 디스크 접근이 발생합니다.

CLUSTER

CLUSTER 명령은 지정된 인덱스에 따라 테이블의 데이터를 물리적으로 정렬할 수 있도록 합니다:

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

CLUSTER 실행 후에는 데이터 검색이 다시 빨라집니다. 하지만 CLUSTER에는 다음과 같은 중요한 단점이 있습니다:

• 테이블 잠금: 이 작업은 실행되는 동안 SELECT 문을 포함하여 전체 테이블을 잠깁니다.
• 제한 사항: 단일 인덱스에서만 작동합니다.
• 자동 유지보수 없음: 데이터 순서가 자동으로 유지되지 않습니다. 새로운 삽입 또는 업데이트 후 데이터는 다시 정렬되지 않은 상태가 될 수 있습니다.

Index Only Scan과 INCLUDE를 통한 최적화

쿼리가 인덱스 내에 완전히 포함된 컬럼만 접근할 때, PostgreSQL은 인덱스 온리 스캔(Index Only Scan)을 수행할 수 있습니다. 이는 메인 테이블(힙)에 접근하는 것을 피하여 쿼리 실행 속도를 크게 향상시킵니다.

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

여기서 id는 이미 idx_id 인덱스에 있으므로 Index Only Scan이 가능합니다. 하지만 idx_id에 없는 name을 포함하여 모든 컬럼을 쿼리하는 경우:

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL은 name 컬럼을 위해 테이블에 접근해야 하므로 일반적인 Index Scan을 수행할 것입니다. SELECT *에 대해서도 Index Only Scan을 가능하게 하려면 INCLUDE 절을 사용하여 커버링 인덱스(covering index)를 사용할 수 있습니다:

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

이제 name이 인덱스에 포함되어 다시 Index Only Scan이 수행되며, 디스크 접근을 최소화합니다.

고급 인덱싱 기법: 함수 기반 인덱스와 부분 인덱스

표준 BTree 인덱스 외에도 PostgreSQL은 더 전문화된 솔루션을 제공합니다:

• 함수 기반 인덱스(Functional Indexes): 함수 결과에 인덱스를 생성할 수 있도록 합니다. 유일한 요구 사항은 해당 함수가 결정론적(deterministic)이어야 한다는 것입니다(항상 동일한 입력에 대해 동일한 결과를 반환해야 함).

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

일반적인 사용 사례는 대소문자 구분 없는 검색을 위해 lower(email)에 인덱스를 생성하는 것입니다.

• 부분 인덱스(Partial Indexes): 특정 WHERE 조건을 만족하는 테이블 행의 부분 집합만 커버하는 인덱스입니다.

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

이러한 인덱스는 크기가 훨씬 작고 업데이트 빈도가 낮아, 대부분의 데이터가 검색 쿼리에 거의 관여하지 않을 때 유용합니다.

기타 인덱스 유형: GiST와 pg_trgm

PostgreSQL은 특정 작업에 최적화된 다양한 인덱스 유형을 지원합니다. BTree 외에도 GIN, GiST, SP-GiST, BRIN, Bloom 등이 있습니다. 예를 들어, GiST 인덱스는 지리 공간 데이터, 전문 검색 및 기타 복잡한 데이터 유형에 자주 사용됩니다.

pg_trgm 확장 기능은 문자열을 트라이그램(trigram)으로 분해하고 그 사이의 거리를 계산하여 퍼지 문자열 매칭(fuzzy string matching)을 가능하게 합니다. 이는 오타 허용 검색이나 부분 문자열 일치에 유용합니다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- a number between 0 and 1
SELECT show_trgm('abcdef');

핵심 요약

• EXPLAIN ANALYZE는 최고의 친구: 쿼리 계획을 이해하고 병목 현상을 식별하는 데 사용하세요. cost는 단일 계획의 부분을 비교하는 데 유용한 상대적인 지표입니다.
• 선택성이 선택을 좌우합니다: 인덱스는 선택성이 높은 쿼리(적은 행)에 효과적입니다. 선택성이 낮으면(많은 행), PostgreSQL은 Seq Scan을 선호할 수 있습니다.
• 물리적 데이터 레이아웃이 중요합니다: 논리적 데이터 순서와 물리적 데이터 순서 간의 높은 상관관계는 Index Scan 성능을 향상시킵니다. CLUSTER가 도움이 될 수 있지만, 중요한 단점이 있습니다.
• Index Only Scan과 INCLUDE: 필요한 모든 컬럼이 이미 인덱스에 존재할 때 메인 테이블 접근을 최소화하기 위해 이 메커니즘들을 사용하세요.
• 고급 인덱스: 함수 기반 인덱스와 부분 인덱스는 특정 시나리오에 더 전문화되고 효율적인 구조를 생성할 수 있도록 합니다.

— Editorial Team