인덱스 설계와 실행계획 읽기

인덱스는 테이블의 목차다

책에서 특정 단어를 찾을 때 처음부터 끝까지 읽지 않고 색인을 먼저 찾듯이, PostgreSQL도 인덱스를 통해 전체 테이블을 스캔하지 않고 원하는 행을 빠르게 찾는다. 하지만 인덱스는 공짜가 아니다. 쓰기 속도와 디스크 공간을 소비하며, 잘못 설계하면 쿼리 플래너가 인덱스를 무시하기도 한다. 언제, 어떤 인덱스를 왜 만드는지 이해하는 것이 DBA의 핵심 역량이다.

PostgreSQL 인덱스 유형

PostgreSQL은 목적에 따라 여러 인덱스 알고리즘을 제공한다.

B-tree — 범용 표준

B-tree는 CREATE INDEX 기본값이다. 균형 이진 트리 구조로 정렬된 데이터에 효율적이다. 전체 값 90 % 이상의 인덱스가 B-tree로 충분하다.

-- 단순 B-tree 인덱스
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 복합 인덱스: 선두 컬럼 순서가 중요하다
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

복합 인덱스에서는 선두 컬럼에 등호 조건이 있을 때 뒷 컬럼도 인덱스를 탄다. WHERE status = 'active' AND created_at > '2026-01-01'은 위 인덱스를 쓰지만, WHERE created_at > '2026-01-01' 만 있으면 인덱스를 쓰지 못한다.

GIN — 배열과 jsonb의 친구

GIN(Generalized Inverted Index)은 하나의 행이 여러 검색 키를 갖는 경우에 쓴다. jsonb 컬럼에서 특정 키를 검색하거나, 배열 포함 여부를 확인할 때 효율적이다.

-- jsonb 컬럼 전체에 GIN 인덱스
CREATE INDEX idx_products_attrs ON products USING gin(attributes);

-- 특정 jsonb 경로만 (더 작은 인덱스)
CREATE INDEX idx_products_tags ON products USING gin((attributes->'tags'));

BRIN — 시계열 대형 테이블

BRIN(Block Range INdex)은 테이블의 물리적 블록 범위마다 MinMax 값을 저장한다. INSERT가 항상 끝에 추가되는 로그나 이벤트 테이블처럼 물리 순서 = 데이터 순서인 경우에 B-tree 대비 수십 배 작은 인덱스로 비슷한 효과를 낸다.

CREATE INDEX idx_events_ts ON events USING brin(created_at) WITH (pages_per_range = 128);

부분 인덱스(Partial Index)

WHERE 조건을 붙여 인덱스 대상을 한정할 수 있다. 전체 행 중 일부만 자주 조회될 때 인덱스 크기를 줄이고 쓰기 오버헤드를 낮춘다.

-- 처리 안 된 주문만 인덱싱
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

표현식 인덱스(Expression Index)

컬럼 값이 아닌 표현식 결과를 인덱싱한다. 쿼리에서 LOWER(email) 같은 함수를 쓰면서도 인덱스 스캔이 가능해진다.

CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- 이제 WHERE LOWER(email) = '[email protected]' 이 인덱스를 쓴다

인덱스 설계 다이어그램

EXPLAIN / EXPLAIN ANALYZE 읽기

인덱스를 만들었다고 끝이 아니다. 쿼리 플래너가 실제로 인덱스를 쓰는지 확인해야 한다. EXPLAIN은 예상 실행 계획을, EXPLAIN ANALYZE는 실제 실행 후 결과를 보여준다.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending';

출력 읽는 법

Bitmap Heap Scan on orders  (cost=12.45..310.20 rows=87 width=112)
                             (actual time=0.312..1.204 rows=91 loops=1)
  Recheck Cond: (user_id = 42)
  Filter: ((status)::text = 'pending')
  ->  Bitmap Index Scan on idx_orders_user_id  (cost=0..12.42 rows=261 width=0)
                                               (actual time=0.280..0.280 rows=261 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.183 ms
Execution Time: 1.352 ms

rows 예상값과 actual rows 차이가 크면 통계(ANALYZE)가 오래됐거나 히스토그램이 부족한 것이다.

주요 스캔 노드

인덱스를 안 타는 흔한 이유

1. 함수 감싸기: WHERE UPPER(name) = 'ALICE' — 표현식 인덱스 없으면 Seq Scan.
2. 타입 불일치: 컬럼은 integer인데 WHERE id = '42' (문자열 리터럴) — 암묵적 캐스팅 시 인덱스 우회.
3. 통계 불일치: rows 예상이 너무 크면 플래너가 Seq Scan을 선택. ANALYZE 실행.
4. 선택도가 낮음: WHERE status IN ('active', 'inactive') — 전체 행의 80 %를 반환하면 순차 스캔이 더 빠를 수 있다.

인덱스 크기와 사용 현황 확인

-- 인덱스 크기
SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 사용 여부 (한 번도 안 쓰인 인덱스)
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

한 번도 쓰이지 않는 인덱스는 쓰기 오버헤드만 발생시킨다. 충분한 모니터링 기간 후 주기적으로 정리한다.

커버링 인덱스(INCLUDE)

PostgreSQL 11부터 INCLUDE 절로 인덱스에 추가 컬럼을 실어 Index Only Scan을 활성화할 수 있다. 인덱스 키는 아니지만 쿼리의 반환 컬럼을 인덱스에 포함시킨다.

-- user_id 검색 후 email을 힙 접근 없이 반환
CREATE INDEX idx_users_id_inc_email ON users(id) INCLUDE (email);

DBA 체크리스트

• EXPLAIN ANALYZE로 예상 rows와 실제 rows 차이를 확인하고, 차이가 크면 ANALYZE를 실행한다.
• 복합 인덱스는 등호 조건 컬럼을 선두에 배치한다.
• pg_stat_user_indexes로 idx_scan = 0인 불필요 인덱스를 주기적으로 정리한다.
• 대형 테이블에 CREATE INDEX CONCURRENTLY를 써서 운영 중 락을 피한다.
• jsonb 컬럼에 자주 접근하는 키만 있다면 gin_path_ops 연산자 클래스로 인덱스를 줄인다.

References

• https://www.postgresql.org/docs/current/indexes-types.html
• https://www.postgresql.org/docs/current/using-explain.html
• https://www.postgresql.org/docs/current/sql-explain.html
• https://www.cybertec-postgresql.com/en/how-to-interpret-postgresql-explain-analyze-output/
• https://leapcell.io/blog/navigating-postgresql-index-choices-b-tree-hash-gin-and-gist-explained
• https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan
• https://neon.com/postgresql/postgresql-indexes/postgresql-index-types