PostgreSQL 인덱스 설계 | 선택도, 복합 인덱스 순서, 커버링 인덱스, 쓰기 비용

boradora 2026. 7. 30. 10:32

작업일2026. 07

인덱스 B-Tree 선택도 복합 인덱스 커버링 인덱스 PostgreSQL

쿼리가 느릴 때 가장 먼저 나오는 답이 인덱스를 걸라는 것입니다. 그런데 인덱스를 만들면 조회는 빨라지지만 쓰기가 느려지고 저장 공간이 늘어납니다. 어느 컬럼에 걸어야 하고 어디까지 걸어도 되는지 정리해보겠습니다.

인덱스를 걸었는데 왜 더 느려졌을까요?
조건에 맞는 행이 전체의 20%를 넘으면 인덱스를 타도 순차 읽기와 속도가 비슷해집니다.

수백만 행짜리 테이블에서 조건 하나로 행을 찾는다고 해봅시다. 인덱스가 없으면 데이터베이스는 첫 행부터 마지막 행까지 전부 확인합니다. 행이 두 배가 되면 걸리는 시간도 두 배가 되는 O(N) 구조입니다.

인덱스가 있으면 탐색 횟수가 O(log N)으로 줄어듭니다. B-Tree는 노드 하나에 수백 개의 자식이 붙기 때문에, 행이 수백 배로 늘어나야 탐색 단계가 하나 늘어납니다. 그래서 100만 행이든 1억 행이든 몇 단계 안에 도달합니다.

실제로 재보면

5만 행짜리 직원 테이블에서 이메일로 한 명을 찾는 쿼리를 직접 측정해봤습니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees
 WHERE lower(email) = 'user1234@corp.com';

인덱스가 없을 때입니다.

Seq Scan on employees  (cost=0.00..1657.00 rows=250 width=28)
                       (actual time=13.944..13.946 rows=1 loops=1)
  Filter: (lower(email) = 'user1234@corp.com'::text)
  Rows Removed by Filter: 50000
  Buffers: shared hit=907

Execution Time: 13.968 ms

1건을 찾으려고 5만 건을 읽고 버렸습니다. Rows Removed by Filter: 50000이 그 숫자입니다. 이 한 줄이 인덱스가 필요하다는 신호입니다.

인덱스를 만들고 같은 쿼리를 실행합니다.

CREATE INDEX idx_employees_lower_email ON employees (lower(email));
ANALYZE employees;

Index Scan using idx_employees_lower_email on employees
    (cost=0.41..8.43 rows=1 width=28)
  Index Cond: (lower(email) = 'user1234@corp.com'::text)
  Buffers: shared read=3

Execution Time: 0.090 ms

인덱스 없음

Seq Scan · O(N)

5만 행을 읽고 49,999행을 버림

13.968 ms

인덱스 있음

Index Scan · O(log N)

3 페이지

0.090 ms

약 155배 차이입니다. 읽은 페이지 수는 907에서 3으로 줄었습니다.

여기서 인덱스를 email에 걸면 안 됩니다. 조건이 lower(email)이라 컬럼 값이 그대로 쓰이지 않기 때문에, email 인덱스는 타지 않습니다. 함수를 적용한 결과에 인덱스를 걸어야 위처럼 Index Cond로 잡힙니다. 이런 인덱스를 함수 기반 인덱스라고 합니다.

인덱스를 걸면 느려지는 것

여기까지만 보면 모든 컬럼에 인덱스를 걸면 될 것 같습니다. 그렇게 하지 않는 이유는 조회가 빨라지는 만큼 쓰기가 느려지고 저장 공간이 늘어나기 때문입니다.

빨라짐

조회

WHERE · JOIN · ORDER BY 가 빨라집니다

느려짐

쓰기

INSERT · UPDATE · DELETE 마다 인덱스를 재정렬합니다

늘어남

저장 공간

인덱스도 디스크를 차지합니다

인덱스가 열 개 걸린 테이블에 행 하나를 넣으면 테이블에 한 번, 인덱스에 열 번을 씁니다. 그래서 쓰기가 많은 테이블일수록 인덱스를 적게 유지해야 합니다.

이 비용은 실행계획에 안 나옵니다. EXPLAIN은 조회 쪽만 보여주기 때문에, 직접 INSERT 시간을 재봤습니다. 같은 테이블에 2만 행을 넣는 시간입니다.

인덱스 구성	INSERT 20,000행	인덱스 총 크기
기본키와 UNIQUE만	220 ms	8.77 MB
튜닝 인덱스 7개 추가	433 ms (1.97배)	22.43 MB

인덱스 일곱 개를 더 만들었더니 쓰기가 두 배 가까이 느려졌습니다. 그리고 테이블 본체가 약 7.5MB인데 인덱스 총량이 22.43MB로 본체보다 커졌습니다. 디스크 사용량의 대부분이 데이터가 아니라 인덱스입니다.

주문이 초당 수백 건씩 들어오는 테이블과, 하루에 한 번 적재하고 종일 조회만 하는 테이블은 인덱스 전략이 반대입니다.

구분	쓰기 중심 (OLTP)	읽기 중심 (OLAP)
예	주문, 결제, 로그 적재	대시보드, 리포트, 집계
인덱스 수	꼭 필요한 것만	조회 패턴마다 넉넉히
기준	쓰기 지연을 얼마나 감수할 수 있는가	조회가 얼마나 자주 반복되는가

조회에 한 번도 쓰이지 않는 인덱스는 쓰기만 느리게 만듭니다. 조회가 빨라지는 효과는 없는데 INSERT마다 갱신되고 디스크도 차지합니다. 뒤에서 이런 인덱스를 찾아내는 방법을 정리하겠습니다.

B-Tree의 구조

대부분의 인덱스는 B-Tree(Balanced Tree)입니다. 모든 리프 노드가 같은 깊이에 있어서 어떤 값을 찾든 탐색 단계 수가 같고, 그래서 O(log N)이 보장됩니다.

Root

▼

Branch

▼

Leaf

↔

Leaf

↔

Leaf

↔

Leaf

Root는 탐색의 시작점, Branch는 어느 쪽으로 내려갈지 안내하는 중간 노드, Leaf는 실제 키 값과 테이블 행의 물리 위치(ctid)를 가지고 있습니다. 리프끼리 이중 연결 리스트로 이어져 있어서 BETWEEN 같은 범위 조회가 빠릅니다.

여기서 실무에 바로 걸리는 것이 키 크기입니다. 키가 작을수록 노드 하나에 더 많은 항목이 들어가고, 그만큼 트리 깊이가 얕아져 탐색이 빨라집니다. 기본키로 UUID(16바이트)를 쓸지 BIGINT(8바이트)를 쓸지가 인덱스 크기에 그대로 반영됩니다.

컬럼 타입을 열어 두면 인덱스가 커집니다

같은 이유로 인덱스를 걸 컬럼의 길이를 정해 두는 편이 낫습니다. 아래처럼 식별자 컬럼을 TEXT로 열어 두는 설계가 자주 보입니다.

CREATE TABLE query_logs (
  id       SERIAL PRIMARY KEY,
  user_id  TEXT,          -- 길이 제한이 없습니다
  ...
);
CREATE INDEX idx_user ON query_logs (user_id);

PostgreSQL만 놓고 보면 TEXT와 VARCHAR(n)은 저장 방식이 같아서 타입만으로 공간을 더 쓰지는 않습니다. 문제는 길이를 막아 두지 않으면 긴 값이 그대로 인덱스 키로 들어간다는 점입니다. 키가 길어지면 노드에 담기는 항목이 줄고 트리가 깊어집니다. 값 하나가 인덱스 행 크기 한계(약 2,704바이트)를 넘으면 그 행은 아예 인덱스에 들어가지 못하고 에러가 납니다.

MySQL은 여기서 차이가 더 큽니다. TEXT 컬럼에는 인덱스를 통째로 걸 수 없어서 INDEX(user_id(20))처럼 앞 몇 글자만 잡는 prefix 인덱스를 써야 하고, 그만큼 선택도가 떨어집니다.

-- 값의 최대 길이를 예측해 자리수를 정해 둡니다
user_id  VARCHAR(64)  NOT NULL

실제로 들어올 값의 최대 길이를 먼저 확인하고, 여유를 조금 둔 자리수로 잡습니다. 사번이나 UUID 문자열처럼 형식이 정해진 값이라면 자리수도 같이 정해집니다.

Fill Factor는 인덱스 페이지를 몇 퍼센트까지 채울지 정하는 값으로 기본값이 90입니다. 10%를 비워 두는 이유는 나중에 값이 들어올 자리를 남겨 페이지 분할을 줄이기 위해서입니다. UPDATE가 잦은 테이블이라면 이 값을 낮춰 분할 횟수를 더 줄일 수 있습니다.

CREATE INDEX idx_orders_status ON orders (status)
  WITH (fillfactor = 70);

B-Tree 말고 다른 인덱스

B-Tree가 기본값이지만 데이터 형태에 따라 더 맞는 구조가 따로 있습니다. JSON 컬럼이나 좌표 데이터에 B-Tree를 걸면 인덱스를 만들어도 타지 않습니다.

인덱스	구조	맞는 용도	지원
B-Tree	균형 트리	`=`, `BETWEEN`, `LIKE 'abc%'`, `ORDER BY`	모든 DBMS 기본
Hash	해시 테이블	등호 비교만. 범위 조회 불가	PostgreSQL, MySQL(제한적)
GIN	역색인	JSONB, 배열, 전문검색	PostgreSQL
GiST	일반화 검색 트리	지리정보(PostGIS), 범위 타입, 벡터 유사도	PostgreSQL 확장
BRIN	블록 범위의 최소·최대	시간순으로 쌓이는 대용량 로그	PostgreSQL
Bitmap	비트 배열	고유값이 적은 컬럼 여러 개를 조합할 때	Oracle (DW용)
Clustered	데이터를 물리적으로 정렬	범위 스캔, 기본키 기반 접근	SQL Server, MySQL InnoDB

BRIN은 인덱스 크기가 B-Tree의 수백분의 일 수준입니다. 대신 물리적으로 정렬돼 쌓인 데이터에서만 효과가 나기 때문에, 시간순으로 적재되는 로그 테이블이 대표적인 대상입니다.

궁금!역인덱스는 무엇을 뒤집은 것일까?

대응 방향이 반대입니다. B-Tree는 행 하나에 키 하나를 대응시킵니다. tags = '{db, sql, index}' 라는 배열이 들어 있으면, B-Tree는 그 배열 전체를 키 하나로 봅니다. 그래서 배열이 정확히 {db, sql, index} 인 행은 찾을 수 있지만, "sql 이 들어 있는 행"은 찾을 수 없습니다.

역인덱스(Inverted Index)는 키 하나에 그 키를 포함한 행 목록을 대응시킵니다. 값을 쪼개서 조각마다 항목을 만들고, 각 항목에 행 위치를 모아 둡니다.

db → [3번 행, 7번 행, 12번 행]
sql → [3번 행, 21번 행]
index → [3번 행, 9번 행]

이제 sql 로 검색하면 3번과 21번 행만 읽으면 됩니다. 조건이 두 개면 각 목록의 교집합이나 합집합을 구합니다. "역"이라는 이름은 행에서 값을 찾는 방향을 값에서 행을 찾는 방향으로 뒤집었다는 뜻입니다.

궁금!GIN 인덱스는 어떻게 동작할까?

GIN은 Generalized Inverted Index의 줄임말입니다. 앞의 역인덱스를 특정 데이터 타입에 묶지 않고 일반화한 것이라 이름에 Generalized가 붙었습니다. 값을 어떻게 쪼갤지만 타입별로 정해주면 같은 구조를 그대로 씁니다.

쪼개는 방식이 타입마다 다릅니다.
배열은 원소 하나하나가 항목이 되고, JSONB는 키와 값이 항목이 됩니다. 전문검색은 단어 단위로 쪼개고, pg_trgm 은 세 글자씩 쪼갭니다. 'user123' 이라면 use, ser, er1, r12, 123 같은 조각이 항목이 됩니다. 그래서 LIKE '%ser%' 처럼 가운데를 검색해도 ser 항목의 행 목록을 꺼내 후보를 좁힐 수 있습니다.

내부는 두 겹입니다. 항목들은 B-Tree로 정렬해 관리하고, 각 항목에 붙은 행 목록은 개수가 적으면 배열로, 많아지면 별도의 B-Tree로 보관합니다. 같은 값이 수만 행에 나타나도 목록 탐색이 느려지지 않게 하려는 구조입니다.

대가는 쓰기 비용입니다. 값 하나를 넣을 때 항목이 여러 개 생기므로 인덱스 갱신량이 그만큼 많아집니다. 'user123' 한 건에 조각 여러 개가 들어갑니다. 그래서 GIN에는 fastupdate 옵션이 있어 새 항목을 대기 목록에 모아 두고 나중에 합칩니다. 다만 합쳐지기 전까지는 조회할 때 그 대기 목록도 훑어야 해서 조회가 일시적으로 느려질 수 있습니다.

마지막으로 GIN은 후보를 좁혀 주는 역할까지만 합니다. ser 조각을 가진 행이라고 해서 실제로 '%ser%' 에 맞는다는 보장은 없기 때문에, 행을 읽은 뒤 조건을 한 번 더 확인합니다. 실행계획에 Recheck Cond 가 나오는 이유가 여기 있습니다.

어떤 컬럼에 걸 것인가

인덱스를 걸 컬럼을 고르는 기준이 선택도(Selectivity)입니다. 조건 하나로 전체의 몇 분의 몇까지 좁혀지는가를 재는 값입니다.

선택도 = 고유값 수 ÷ 전체 행 수

-- 전체 5만 행일 때
gender  : 고유값 2개      →      2 ÷ 50,000 ≈ 0.00004   거의 0
email   : 고유값 50,000개 → 50,000 ÷ 50,000 = 1         1에 가까움

이 용어는 문헌마다 뒤집혀 쓰입니다. 위 공식으로는 1에 가까울수록 좋은 인덱스입니다. 반대로 "조건을 만족하는 행의 비율"을 선택도라 부르는 글에서는 0에 가까울수록 좋다고 씁니다. 부르는 이름보다 조건 하나로 몇 행이 남는지를 보면 헷갈리지 않습니다.

고유값 2개 · 인덱스 효과 낮음

gender

값이 M과 F 두 개뿐이라 조건 하나로 절반밖에 걸러내지 못합니다. 절반을 읽을 바에는 전체를 순차로 읽는 편이 빠를 수 있어서, 옵티마이저가 인덱스를 건너뛰기도 합니다.

고유값이 행 수만큼 · 인덱스 효과 큼

email

값이 거의 전부 다르기 때문에 조건 하나로 행 하나까지 좁혀집니다.

조건이 전체의 20%를 넘게 남기면 인덱스를 타도 이득이 거의 없습니다. 이메일 도메인이 gmail인 직원을 찾는 쿼리로 재봤더니, 선택도가 25%인 구간에서 Bitmap Index Scan(2.73ms)과 Seq Scan(2.83ms)이 거의 같았습니다. 읽어야 할 행이 그만큼 많으면 인덱스를 거쳐 흩어진 위치를 찾아가는 비용이 순차 읽기와 비슷해집니다.

선택도 외에 실무에서 함께 보는 기준이 네 가지 더 있습니다.

WHERE에 자주 등장하는 컬럼을 먼저 봅니다. 쿼리 로그를 뒤져 실제로 어떤 조건이 반복되는지 확인합니다.
외래키 컬럼에는 반드시 인덱스를 겁니다. JOIN 성능뿐 아니라 부모 행을 지우거나 고칠 때 하는 참조 검사에도 쓰입니다. 없으면 자식 테이블을 전부 훑습니다.
쓰기 중심인지 읽기 중심인지에 따라 개수를 조절합니다.
쓰이지 않는 인덱스는 지웁니다. 남겨 둘 이유가 없습니다.

복합 인덱스의 컬럼 순서

컬럼 두 개 이상을 묶은 인덱스를 복합 인덱스라고 합니다. 여기서는 어떤 컬럼을 앞에 두느냐에 따라 성능이 갈립니다. 규칙은 하나입니다. 등호 조건을 앞에, 범위 조건을 뒤에 둡니다.

SELECT * FROM orders
 WHERE status = 'A'
   AND created_at > '2024-01-01';

INDEX (status, created_at)

status = 'A' 로 범위를 먼저 좁힌 뒤, 그 안에서 날짜 범위를 훑습니다. 읽는 양이 두 단계에 걸쳐 줄어듭니다.

INDEX (created_at, status)

날짜 범위로 넓게 잡아 놓고 그 안에서 status 를 하나씩 걸러냅니다. 먼저 읽어야 하는 양이 훨씬 많습니다.

범위 조건을 앞에 두면 그 뒤 컬럼은 정렬 순서를 잃어서 인덱스로 좁히지 못하고 필터로만 쓰입니다. 등호를 앞에 두는 이유가 여기 있습니다.

커버링 인덱스

인덱스를 타고 행을 찾아도 작업이 한 번 더 남습니다. 인덱스에는 키 값과 행의 위치만 있기 때문에, 나머지 컬럼을 읽으려면 테이블(Heap)에 접근해야 합니다. 이 추가 접근을 Heap Fetch라고 합니다.

커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼을 인덱스 안에 넣어 테이블 접근을 없앤 인덱스입니다. PostgreSQL과 SQL Server에서는 INCLUDE 절로 만듭니다.

-- 일반 인덱스 : 인덱스를 탄 뒤 테이블을 한 번 더 읽습니다
CREATE INDEX idx_email ON users (email);
SELECT id, name FROM users WHERE email = 'a@b.com';
-- Index Scan using idx_email  +  Heap Fetch

-- 커버링 인덱스 : 반환할 컬럼까지 인덱스에 담습니다
CREATE INDEX idx_email_cover ON users (email) INCLUDE (id, name);
SELECT id, name FROM users WHERE email = 'a@b.com';
-- Index Only Scan using idx_email_cover

INCLUDE에 넣은 컬럼은 탐색에는 쓰이지 않고 반환에만 쓰입니다. 그래서 트리 깊이를 늘리지 않으면서 테이블 접근만 없앨 수 있습니다. 적용됐는지 확인하려면 실행계획에서 Heap Fetches 값을 봅니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name FROM users WHERE email = 'a@b.com';

Index Only Scan using idx_email_cover on users
  Heap Fetches: 0        ← 테이블 접근 0번

Heap Fetches가 0이 아니면 Index Only Scan이 반쪽만 동작한 것입니다. PostgreSQL은 해당 행이 모든 트랜잭션에 보여도 되는지를 Visibility Map으로 판단하는데, 이 정보가 낡아 있으면 결국 테이블을 확인합니다. VACUUM이 오래 실행되지 않았는지부터 확인하면 됩니다.

안 쓰는 인덱스 찾아내기

인덱스는 만들 때보다 지울 때 판단이 어렵습니다. PostgreSQL은 인덱스가 몇 번 쓰였는지를 pg_stat_user_indexes에 기록하기 때문에, 감이 아니라 횟수로 판단할 수 있습니다.

-- 사용 횟수가 적은 인덱스부터 (제거 후보)
SELECT indexrelname AS index_name,
       relname      AS table_name,
       idx_scan     AS scan_count,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
  FROM pg_stat_user_indexes
 ORDER BY idx_scan ASC
 LIMIT 20;

-- 한 번도 안 쓰인 인덱스 (기본키 제외)
SELECT indexrelname, relname,
       pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
   AND indexrelname NOT LIKE 'pk_%'
 ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan이 0이면 통계를 모으기 시작한 뒤로 한 번도 쓰이지 않았다는 뜻입니다. 크기가 큰 것부터 제거를 검토하면 쓰기 비용과 디스크를 함께 줄일 수 있습니다.

인덱스를 오래 쓰면 삭제와 갱신이 쌓여 실제 데이터보다 부풀어 오릅니다. 이때는 재구성을 하는데, CONCURRENTLY를 붙이면 재구성 중에도 읽기와 쓰기가 계속 됩니다. 다만 완전히 잠금이 없는 것은 아니고, 마지막에 인덱스를 교체하는 짧은 순간에는 배타적 잠금이 필요합니다. PostgreSQL 12부터 쓸 수 있습니다.

REINDEX INDEX CONCURRENTLY idx_orders_cust_date;
REINDEX TABLE CONCURRENTLY orders;

MySQL에서는 information_schema.STATISTICS의 CARDINALITY로 각 인덱스의 고유값 추정치를 확인합니다. 값이 낮으면 선택도가 낮다는 뜻입니다.

SELECT TABLE_NAME, INDEX_NAME, CARDINALITY
  FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA = 'mydb'
 ORDER BY CARDINALITY ASC;

정리하면

인덱스를 걸기 전에 확인할 것을 순서대로 정리해보겠습니다.

이 컬럼의 선택도가 얼마인가. 고유값이 두세 개뿐이라면 인덱스를 만들어도 옵티마이저가 쓰지 않을 수 있습니다.
이 테이블은 쓰기가 많은가 읽기가 많은가. 쓰기가 많으면 인덱스 하나가 늘어날 때마다 INSERT가 느려집니다.
복합 인덱스라면 등호 조건이 앞에 있는가. 순서만 바꿔도 읽는 양이 달라집니다.
외래키에 인덱스가 있는가. 없으면 JOIN과 참조 검사에서 전체 스캔이 일어납니다.
지금 있는 인덱스 중 안 쓰이는 것은 없는가. idx_scan = 0이면 조회에는 안 쓰이면서 쓰기만 느리게 만드는 상태입니다.

한 줄 정리: 인덱스를 걸면 조회가 빨라지고 쓰기가 느려집니다. 몇 개까지 걸어도 되는지는 그 테이블에 INSERT가 얼마나 들어오는지로 정해집니다.

참고

PostgreSQL: Indexes
PostgreSQL: Index-Only Scans and Covering Indexes
PostgreSQL: The Statistics Collector
MySQL: Optimization and Indexes