데이터베이스 기초 (5) - Indexes and EXPLAIN

PostgreSQL에서 generate_series로 10만 건 실험 데이터를 만들고 EXPLAIN ANALYZE로 실행 계획을 읽습니다. B-tree index의 동작 원리, 복합 index와 column 순서, covering index, index가 쓰이지 못하는 조건과 쓰기 비용까지 정리합니다.

Posted 2025/12/16

By 강호승

5 min read

데이터베이스 기초 시리즈의 5편입니다. 전체 목차는 0편에 있습니다.

실험 준비

2편에서 넣은 데이터는 posts 4건, comments 6건입니다. 이 크기에서는 index를 만들어도 속도 차이가 측정되지 않으므로, 이번 편은 실험용 table을 따로 만들어 10만 건을 넣습니다. 실습 환경은 PostgreSQL 편에서 만든 container와 psql을 그대로 씁니다.

  
CREATE TABLE posts_large (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL,
    title      TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

INSERT INTO posts_large (user_id, title, created_at)
SELECT (n % 100) + 1,
       'post ' || n,
       now() - (n || ' minutes')::interval
FROM generate_series(1, 100000) AS n;

ANALYZE posts_large;

generate_series(1, 100000)은 1부터 100000까지 연속된 값을 만들어 주는 PostgreSQL 함수입니다. 이 값을 n으로 받아 user_id는 1부터 100까지 순환시키고, created_at은 now()에서 n분씩 빼서 과거로 분산시켰습니다. 따라서 user_id 값 하나당 row가 정확히 1000건씩 있습니다. created_at 식에서 ||는 문자열을 이어 붙이는 연산이고, ::interval은 그렇게 만든 ‘N minutes’ 형태의 문자열을 시간 간격 type인 interval로 바꾸는 PostgreSQL의 형변환 문법입니다. 마지막 줄의 ANALYZE는 table의 값 분포 통계를 수집하는 명령입니다. 다음 섹션에서 볼 planner가 이 통계를 근거로 실행 방법을 고릅니다.

EXPLAIN ANALYZE 첫 읽기

쿼리 앞에 EXPLAIN ANALYZE를 붙이면 그 쿼리를 실제로 실행하고, 실행 계획과 실측 시간을 함께 출력합니다. planner는 쿼리를 어떤 방법으로 실행할지 결정하는 PostgreSQL 내부 구성 요소이고, EXPLAIN ANALYZE의 ANALYZE는 위에서 쓴 통계 수집 명령 ANALYZE와 이름만 같은 별개 키워드입니다. index가 아직 없는 user_id 조건 조회부터 봅니다.

  
EXPLAIN ANALYZE SELECT * FROM posts_large WHERE user_id = 42;

 Seq Scan on posts_large  (cost=0.00..2084.00 rows=1077 width=34) (actual time=0.010..4.402 rows=1000.00 loops=1)
   Filter: (user_id = 42)
   Rows Removed by Filter: 99000
   Buffers: shared hit=834
 Planning:
   Buffers: shared hit=69
 Planning Time: 0.214 ms
 Execution Time: 4.468 ms

Seq Scan은 table을 처음부터 끝까지 전부 읽는 방식입니다. Rows Removed by Filter: 99000은 10만 건을 다 읽은 뒤 99000건을 조건 불일치로 버렸다는 뜻입니다. 괄호 두 쌍은 의미가 다릅니다. cost=0.00..2084.00 rows=1077은 planner가 통계로 계산한 추정치이고, actual time=0.010..4.402 rows=1000.00은 실제 실행에서 잰 값입니다. cost의 단위는 ms가 아니라 planner 내부의 상대 비용이며, 두 숫자는 각각 첫 row를 반환하기까지의 비용과 전체 완료까지의 비용입니다. actual time만이 ms 단위 실측입니다. 추정 rows와 실제 rows가 크게 어긋나면 통계가 낡았다는 신호이고, 이 예에서는 추정 1077건과 실제 1000건으로 거의 일치합니다. Buffers는 접근한 page 수인데, 이 줄과 다음 출력에 나오는 Recheck Cond, Heap Blocks 같은 세부 줄은 이번 편에서 다루지 않습니다. 참고로 ANALYZE 없이 EXPLAIN만 쓰면 쿼리를 실행하지 않고 추정치만 보여 줍니다.

index를 만들면

PostgreSQL의 기본 index 자료구조는 B-tree입니다. B-tree는 column 값을 정렬된 순서로 유지하는 트리로, 루트 노드에서 시작해 값의 범위를 좁히며 하위 노드로 내려가기 때문에 table 전체를 읽지 않고 원하는 값의 저장 위치에 도달합니다. 데이터가 10만 건이든 1억 건이든 트리 깊이는 몇 단계에 그치므로, 조회 비용이 데이터 크기에 비례해서 늘지 않습니다.

  
CREATE INDEX idx_posts_large_user_id ON posts_large (user_id);

EXPLAIN ANALYZE SELECT * FROM posts_large WHERE user_id = 42;

 Bitmap Heap Scan on posts_large  (cost=12.64..904.70 rows=1077 width=34) (actual time=0.198..1.002 rows=1000.00 loops=1)
   Recheck Cond: (user_id = 42)
   Heap Blocks: exact=833
   Buffers: shared hit=836 read=3
   ->  Bitmap Index Scan on idx_posts_large_user_id  (cost=0.00..12.37 rows=1077 width=0) (actual time=0.093..0.093 rows=1000.00 loops=1)
         Index Cond: (user_id = 42)
         Index Searches: 1
         Buffers: shared hit=3 read=3
 Planning:
   Buffers: shared hit=51 read=1
 Planning Time: 0.299 ms
 Execution Time: 1.089 ms

같은 쿼리가 Seq Scan 대신 index를 쓰는 계획으로 바뀌었고, Execution Time이 4.468 ms에서 1.089 ms로 줄었습니다. 정확한 수치는 하드웨어와 캐시 상태에 따라 환경마다 다르므로, 절대값보다 계획이 바뀌었다는 사실을 확인합니다. 조건에 맞는 row가 소수면 Index Scan이 나오고, 이 예처럼 1000건 정도로 많으면 Bitmap Heap Scan이 나올 수 있는데 둘 다 index를 활용하는 계획입니다.

실행 계획 읽는 법

실행 계획은 노드의 트리입니다. 들여쓰기와 -> 표시가 자식 노드를 나타내고, 자식 노드의 출력이 부모 노드의 입력이 됩니다. 위 출력에서는 Bitmap Index Scan이 먼저 실행되어 조건에 맞는 저장 위치 목록을 만들고, 부모인 Bitmap Heap Scan이 그 목록에 해당하는 부분만 table에서 읽었습니다. 자주 보는 노드는 다음과 같습니다.

노드	동작
Seq Scan	table 전체를 순서대로 읽습니다
Index Scan	index에서 위치를 찾고 table에서 해당 row를 읽습니다
Index Only Scan	index만 읽고 table 접근을 생략합니다
Bitmap Heap Scan	index로 읽을 위치 목록을 먼저 만들고 table을 한 번에 읽습니다
Nested Loop	한쪽 결과의 row마다 다른 쪽을 반복 조회하는 JOIN 방식입니다
Hash Join	한쪽 데이터로 hash table을 만들어 다른 쪽과 대조하는 JOIN 방식입니다

Nested Loop와 Hash Join은 3편에서 배운 JOIN을 planner가 실행하는 내부 방식입니다. 지금 단계에서는 이름과 위 표의 한 줄 동작만 알아도 충분합니다. 느린 쿼리를 진단할 때는 계획에서 actual time이 큰 노드를 찾는 것이 시작점이기 때문입니다.

복합 index와 한계

column 두 개 이상으로 index 하나를 만들 수 있습니다.

  
CREATE INDEX idx_posts_large_user_created ON posts_large (user_id, created_at);

복합 index는 첫 번째 column으로 먼저 정렬하고, 같은 값 안에서 두 번째 column으로 정렬합니다. 그래서 왼쪽 column부터 조건에 있어야 효율적으로 쓰입니다. 이 index는 WHERE user_id = 42 조회와 WHERE user_id = 42 AND created_at > now() - interval '1 day' 조회에는 쓰이지만, user_id 조건 없이 created_at 조건만 있는 조회에서는 정렬 순서로 범위를 좁히지 못합니다. planner가 index 전체를 읽는 계획을 고를 수는 있지만, 왼쪽 column 조건이 있을 때처럼 필요한 부분만 읽는 효율은 나오지 않습니다. 그리고 앞 섹션에서 만든 idx_posts_large_user_id처럼 더 작은 단일 column index가 같이 있으면 planner는 그쪽을 고르므로, WHERE user_id = 42 조회의 실행 계획에는 복합 index가 나타나지 않습니다.

쿼리가 필요로 하는 column을 전부 index에 실어 두면, table 접근을 생략하는 Index Only Scan을 만들 수 있습니다. 이런 index를 covering index라고 합니다.

  
CREATE INDEX idx_posts_large_covering ON posts_large (user_id) INCLUDE (title);

VACUUM posts_large;

INCLUDE에 적은 column은 정렬 기준으로는 쓰이지 않고 값만 index에 함께 저장됩니다. SELECT user_id, title FROM posts_large WHERE user_id = 42처럼 조회 column이 전부 index 안에 있으면 Index Only Scan이 나올 수 있습니다. Index Only Scan은 table 접근을 생략해도 된다는 page 단위 표시를 VACUUM이 만들어 둔 뒤에 나오므로 VACUUM을 함께 실행했습니다. INSERT 직후에 VACUUM 없이 조회하면 row 가시성 확인 때문에 table을 읽는 계획이 나오거나, 출력에 Heap Fetches 항목이 함께 나옵니다.

index가 쓰이지 못하는 조건도 있습니다.

column에 함수를 씌운 조건: WHERE lower(title) = 'post 42'는 title에 만든 index를 쓰지 못합니다. CREATE INDEX ON posts_large (lower(title));처럼 표현식 자체로 index를 만들면 해결됩니다.
앞에 %가 오는 LIKE: LIKE '%질문'은 문자열의 앞부분이 정해지지 않아 정렬 순서를 활용할 수 없습니다. 앞부분이 고정된 LIKE '질문%'는 원리상 정렬 순서를 활용할 수 있지만, PostgreSQL에서는 database의 collation이 C일 때만 일반 index가 쓰입니다. 실습 container의 database는 collation이 C가 아니므로, CREATE INDEX ON posts_large (title text_pattern_ops);처럼 text_pattern_ops를 지정한 index를 만들어야 LIKE에 쓰입니다.
cardinality가 낮은 column: cardinality는 column이 가진 서로 다른 값의 개수입니다. 값 종류가 두세 개뿐인 column은 한 값이 table의 큰 비율을 차지하므로, index로 걸러도 결국 table 대부분을 읽게 되어 planner가 Seq Scan을 선택합니다.

index의 비용

index는 조회를 빠르게 하는 대신 쓰기 비용을 늘립니다. row를 INSERT하면 table뿐 아니라 그 table의 모든 index에도 항목이 추가되고, index에 포함된 column을 UPDATE하면 해당 index도 갱신됩니다. index가 다섯 개인 table은 쓰기 한 번에 자료구조 여섯 개를 갱신하는 셈이고, 디스크 공간도 추가로 차지합니다. 그래서 index는 실제 조회 패턴의 WHERE, JOIN, ORDER BY에 쓰이는 column에만 만드는 것이 원칙입니다. 만들어 두고 쓰이지 않는 index는 index 사용 횟수를 기록하는 내장 통계인 pg_stat_user_indexes에서 idx_scan 값으로 확인할 수 있습니다.

실험이 끝났으므로 table을 삭제해 정리합니다. table을 삭제하면 그 위에 만든 index도 함께 삭제됩니다.

  
DROP TABLE posts_large;

MySQL 차이

이번 편의 원리는 MySQL에도 그대로 적용되지만 도구와 저장 구조에 차이가 있습니다. MySQL의 기본 EXPLAIN은 트리가 아니라 type, key, rows 같은 column을 가진 표 형식으로 출력됩니다. 실측 시간까지 보여 주는 EXPLAIN ANALYZE는 8.0.18부터 지원되며, 이때는 PostgreSQL과 비슷한 트리 형식으로 출력됩니다. 저장 구조의 차이가 더 큽니다. InnoDB에서 PRIMARY KEY는 clustered index라서 table 데이터 자체가 PK 순서로 저장됩니다. 나머지 secondary index는 row의 물리 위치 대신 PK 값을 저장하므로, PK가 크면 모든 index가 함께 커집니다. 그래서 MySQL에서는 PK를 작고 증가하는 값으로 설계하는 것이 PostgreSQL에서보다 더 중요합니다.

다음 편

다음 편인 데이터베이스 기초 6편에서는 여러 접속이 같은 데이터를 동시에 수정할 때 생기는 문제와 이를 제어하는 transaction, isolation level, lock을 다룹니다.

Database, DB Basics