사용자가 100만 명인 테이블에서 WHERE email = 'user@example.com'을 실행했더니 5초가 걸립니다. 인덱스를 걸었더니 즉시 반환됩니다. 왜 이렇게 빠른지 이해하지 못하면 인덱스를 아무 컬럼에나 걸거나, 반대로 필요한 곳에 안 걸어서 슬로우 쿼리가 반복됩니다.

Full Table Scan vs 인덱스 검색

인덱스가 없는 테이블에서 특정 이메일을 검색하면 DB는 첫 번째 행부터 마지막 행까지 모든 행을 순서대로 비교합니다. 1억 행이 있으면 최악의 경우 1억 번 비교가 필요합니다. 이것이 Full Table Scan이고 시간 복잡도는 O(n)입니다.

B-Tree 인덱스가 있으면 루트 노드에서 시작해 각 단계마다 탐색 범위가 절반씩 줄어듭니다. 1억 행에서도 약 27번(log₂(1억) ≈ 27)의 비교만으로 원하는 행을 찾을 수 있습니다. 시간 복잡도는 O(log n)입니다.

B-Tree 구조 상세

B-Tree 인덱스는 루트 노드, 브랜치 노드, 리프 노드의 세 계층으로 구성됩니다. users.email 컬럼에 인덱스를 만들면 아래와 같은 트리가 내부적으로 생성됩니다.

              [루트 노드]
              [M | T]
             /   |   \
            /    |    \
    [Branch]  [Branch]  [Branch]
    [D | H]   [N | R]   [V | Z]
    /  |  \    ...        ...
   /   |   \
[Leaf][Leaf][Leaf]
[A,B] [C,D] [E,F,G,H]
  ↓     ↓       ↓
실제 행 포인터 (heap page, offset)

각 노드의 역할은 다음과 같습니다. 루트 노드는 탐색의 시작점으로 전체 값 범위를 크게 나눕니다. 브랜치 노드는 중간 경로로 값 범위에 따라 탐색 방향을 결정합니다. 리프 노드는 실제 인덱스 키값과 해당 행의 물리적 위치(heap pointer)를 저장합니다. 리프 노드들은 서로 링크드 리스트로 연결되어 있어 BETWEEN, 부등호 같은 범위 검색에서 연속된 결과를 효율적으로 읽을 수 있습니다.

Cardinality — 인덱스 효과의 핵심 지표

Cardinality는 컬럼의 고유값 수를 의미합니다. 높을수록 인덱스 효과가 큽니다.

is_active=true인 행이 전체의 90%라면, 인덱스를 통해 90%를 읽는 것보다 그냥 테이블 전체를 읽는 Full Scan이 오히려 빠릅니다. DB 옵티마이저가 이를 감지하고 인덱스를 자동으로 무시하기도 합니다.

CREATE INDEX 문법

기본 인덱스, UNIQUE 인덱스, 복합 인덱스, 부분 인덱스를 용도에 맞게 선택합니다. 운영 중인 대용량 테이블에 인덱스를 추가할 때는 CONCURRENTLY 옵션으로 테이블 락 없이 생성합니다. 시간이 더 걸리지만 서비스를 중단하지 않아도 됩니다.

CREATE INDEX idx_users_email ON users (email);

CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

CREATE INDEX idx_posts_created_desc ON posts (created_at DESC);

CREATE INDEX CONCURRENTLY idx_large_table_col ON large_table (column_name);

실행 완료 또는 조회 결과가 표시됩니다.

인덱스 확인 방법

psql에서 \d users로 테이블 구조와 인덱스를 함께 확인합니다. pg_stat_user_indexes의 idx_scan이 0인 인덱스는 생성 이후 한 번도 사용되지 않은 것으로, 삭제 대상 후보입니다.

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'users';

SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'users'
ORDER BY idx_scan DESC;

SHOW INDEX FROM users;

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN ANALYZE 결과에서 Index Scan using idx_users_email이 보이면 인덱스를 사용 중이고, Seq Scan이 보이면 Full Table Scan 중입니다.

인덱스를 많이 걸수록 좋을 것 같아서 모든 컬럼에 인덱스를 걸었습니다. INSERT/UPDATE가 갑자기 느려졌습니다. 인덱스는 읽기를 빠르게 하지만 쓰기를 느리게 합니다. 어느 컬럼에 걸어야 하고 어느 컬럼에 걸면 안 되는지 — 이 판단 기준을 모르면 인덱스가 오히려 독이 됩니다.

인덱스를 만들어야 하는 컬럼

WHERE 조건에 자주 사용되는 컬럼, JOIN ON 조건 컬럼, ORDER BY 컬럼, Cardinality가 높은 컬럼이 인덱스 대상입니다. 특히 JOIN에서 외래키 컬럼(orders.user_id)에 인덱스가 없으면 조인 시 Full Scan이 발생합니다.

CREATE INDEX idx_orders_user_id ON orders (user_id);

CREATE UNIQUE INDEX idx_users_email ON users (email);

CREATE INDEX idx_posts_created_at ON posts (created_at DESC);

CREATE INDEX idx_products_sku ON products (sku);

Composite Index 컬럼 순서 설계

복합 인덱스의 컬럼 순서는 어떤 WHERE 조건으로 사용할 수 있는지를 결정합니다. 선두 컬럼부터 순서대로만 사용할 수 있습니다. 컬럼 순서 설계 원칙은 등호(=) 조건 컬럼을 앞에, Cardinality가 높은 컬럼을 앞에, 범위 조건(>, <, BETWEEN)은 뒤에, ORDER BY 컬럼은 마지막에 배치합니다.

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);

-- 인덱스 사용됨 (선두 컬럼 user_id 포함)
EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- 인덱스 미사용 (선두 컬럼 user_id 없음)
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

위 인덱스는 WHERE user_id = 1, WHERE user_id = 1 AND status = 'pending', WHERE user_id = 1 AND status = 'pending' ORDER BY created_at 조건에서 사용됩니다. 반면 WHERE status = 'pending'이나 WHERE created_at > '2024-01-01'처럼 선두 컬럼(user_id)이 없는 조건에서는 사용되지 않습니다.

인덱스를 만들지 말아야 할 상황

인덱스는 조회를 빠르게 하지만 쓰기(INSERT, UPDATE, DELETE)를 느리게 합니다. 데이터 변경마다 인덱스도 함께 갱신해야 하기 때문입니다.

소규모 테이블(1000행 미만)은 Full Scan이 인덱스 조회보다 빠를 수 있습니다. 인덱스를 통해 행 위치를 찾고 다시 힙에 접근하는 오버헤드가 더 크기 때문입니다.

Cardinality가 낮은 컬럼(gender, is_active)은 인덱스 효율이 없습니다. 초당 수만 건 INSERT가 발생하는 로그 테이블이나 사용자 활동마다 갱신되는 last_activity_at 컬럼은 인덱스가 쓰기 성능을 떨어뜨립니다.

인덱스 유지보수

시간이 지나면 인덱스가 단편화됩니다. REINDEX로 인덱스를 재구성할 수 있고, 운영 중에는 CONCURRENTLY 옵션을 사용합니다. 분기별로 idx_scan = 0인 미사용 인덱스를 점검하고 삭제합니다.

REINDEX INDEX CONCURRENTLY idx_users_email;

REINDEX TABLE users;

DROP INDEX idx_old_unused_index;

SELECT
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    indexrelname,
    idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pk_%'
ORDER BY pg_relation_size(indexrelid) DESC;