PostgreSQL 인덱스 최적화 실무 — 인덱스 걸었는데 왜 느린지 모르겠다면

목차

• "인덱스 걸면 빨라진다"는 반만 맞는 말이다
• EXPLAIN ANALYZE 출력 제대로 읽기
• WHERE 절에 함수를 씌우면 인덱스는 죽는다
• Seq Scan이 나쁜 건 아닌 경우
• 복합 인덱스에서 컬럼 순서가 성능을 가른다
• Partial Index와 Covering Index
  • Partial Index — 필요한 행만 인덱스에 담기
  • Covering Index — 힙 접근을 아예 없애기
• pg_stat_statements로 병목 쿼리 추적

"인덱스 걸면 빨라진다"는 반만 맞는 말이다

PostgreSQL 인덱스 최적화 실무에서 가장 흔한 착각이 하나 있다. "인덱스 만들었으니까 빠를 거야." 화요일 오후, 배포 직후 슬랙에 "주문 조회 API 응답 3초 초과" 알림이 떴을 때 나도 그렇게 생각했다. orders 테이블의 created_at 컬럼에 B-tree 인덱스를 걸어둔 상태였으니까.

M1 Mac에서 DataGrip 열고 운영 DB에 읽기 전용으로 붙었다. EXPLAIN ANALYZE를 찍어봤더니 Seq Scan. 1200만 건짜리 테이블을 통째로 훑고 있었다. 인덱스가 버젓이 존재하는데.

결론부터 말하면, 인덱스를 만들었다고 쿼리가 빨라지는 게 아니다. PostgreSQL 쿼리 플래너가 그 인덱스를 선택해야 빨라진다. 플래너는 pg_statistic의 통계 정보, 테이블 크기, 인덱스 선택도(selectivity)를 종합 판단해서 인덱스를 쓸지 말지 결정한다. 이 판단이 개발자 의도와 항상 일치하지는 않는다.

인덱스가 실제로 사용되고 있는지 확인하려면 pg_stat_user_indexes를 보면 된다.

-- 사용되지 않는 인덱스 찾기
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan이 0인 인덱스가 줄줄이 나온다면, 그건 성능을 돕는 게 아니라 디스크만 잡아먹는 짐짝이다. INSERT와 UPDATE가 발생할 때마다 인덱스 유지 비용만 발생시킨다. 만들어놓고 안 쓰이는 인덱스는 쓰기 성능의 적이다.

EXPLAIN ANALYZE 출력 제대로 읽기

느린 쿼리를 잡으려면 EXPLAIN ANALYZE를 읽을 줄 알아야 한다. EXPLAIN만 쓰는 경우가 많은데, 이건 플래너의 예상치일 뿐이다. 실제 실행 시간을 보려면 ANALYZE를 붙여야 한다. BUFFERS 옵션까지 넣으면 I/O 패턴도 파악된다.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 12345
  AND created_at >= '2026-03-01'
ORDER BY created_at DESC
LIMIT 20;

출력에서 핵심은 세 가지다.

actual time — 첫 번째 숫자가 첫 로우 반환까지 걸린 시간(ms), 두 번째가 전체 완료 시간이다. actual time=0.045..0.089면 정상이다. actual time=234.5..1892.3 같은 숫자가 보이면 해당 노드에 병목이 있다는 뜻이다.

rows와 Rows Removed by Filter — 반환 행이 15건인데 Rows Removed by Filter: 89000이 찍혀 있다면, 89000건을 읽고 버렸다는 뜻이다. 인덱스가 제대로 동작하고 있었다면 이 숫자가 이렇게 클 이유가 없다. 인덱스 조건이 쿼리와 맞지 않거나, 인덱스 자체를 타지 않고 있는 거다.

Buffers — shared hit=45는 PostgreSQL 공유 버퍼 캐시에서 읽은 블록 수, shared read=1200은 디스크에서 읽은 블록 수다. read가 hit 대비 크면 shared_buffers 설정을 점검하거나 인덱스 효율을 의심해봐야 한다. 잘 설계된 인덱스 쿼리는 대부분 hit에서 해결된다.

한 가지 주의할 게 있다. EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. SELECT야 문제없지만 UPDATE나 DELETE에 걸면 데이터가 진짜 바뀐다. 운영 DB에서는 반드시 트랜잭션으로 감싸야 한다.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'cancelled' WHERE id = 99999;
ROLLBACK;  -- 실제 변경 방지. 이거 빼먹으면 진짜 바뀐다

이걸 모르고 운영 DB에서 DELETE 쿼리에 EXPLAIN ANALYZE를 건 적이 있다. WHERE 조건이 좁아서 피해는 없었지만, 그 뒤로는 습관적으로 BEGIN부터 친다.

WHERE 절에 함수를 씌우면 인덱스는 죽는다

화요일에 1시간 반을 헤맨 원인이 바로 이거였다. created_at에 인덱스가 있는 상태에서 아래 쿼리를 쓰고 있었다.

-- 이렇게 쓰면 인덱스를 안 탄다
SELECT * FROM orders
WHERE DATE(created_at) = '2026-03-15';

DATE() 함수를 씌우는 순간, PostgreSQL은 created_at 인덱스를 쓸 수 없다. 인덱스에는 created_at 원본 타임스탬프 값이 저장되어 있는데, DATE(created_at) 결과값은 인덱스 어디에도 없기 때문이다. 결국 모든 행에 DATE()를 적용해서 비교해야 하니까 Seq Scan으로 빠진다. 1200만 건 테이블에서 Seq Scan이면 3초가 약과다.

해결은 범위 조건으로 바꾸는 거다.

-- 범위 조건으로 변환하면 인덱스를 탄다
SELECT * FROM orders
WHERE created_at >= '2026-03-15'
  AND created_at < '2026-03-16';

이걸로 바꾸니 3.2초 → 0.8ms. 체감상 4000배 차이. 이건 PostgreSQL만의 문제가 아니다. MySQL, Oracle 전부 동일하게 적용되는 원칙이다 — 인덱스 컬럼에 연산을 가하면 인덱스를 못 쓴다. LOWER(email) = 'test@test.com'도 같은 이유로 email 인덱스를 무시한다.

꼭 함수를 써야 하는 상황이라면 expression index가 있다.

-- expression index 생성
CREATE INDEX idx_orders_date_created
ON orders (DATE(created_at));

-- 이제 DATE()를 써도 인덱스를 탄다
SELECT * FROM orders
WHERE DATE(created_at) = '2026-03-15';

다만 expression index는 표현식이 정확히 일치할 때만 사용된다. DATE(created_at)으로 만들었으면 created_at::date에는 안 먹힌다. 문법은 다르지만 결과가 같다고? PostgreSQL 플래너 입장에서는 별개의 표현식이다. 이걸 모르면 "인덱스 만들었는데 왜 안 타지?"를 또 반복하게 된다.

Seq Scan이 나쁜 건 아닌 경우

이것도 흔한 오해다. 테이블에 500건밖에 없으면 Seq Scan이 Index Scan보다 빠를 수 있다. 인덱스를 타려면 B-tree를 탈고 내려간 뒤 힙 테이블로 다시 점프해야 하는데, 데이터가 적으면 그냥 처음부터 쭉 읽는 게 낫다. 플래너가 Seq Scan을 골랐다고 무조건 잘못된 건 아니라는 거다.

의심해야 할 건 통계 정보가 오래된 경우다. 대량 INSERT나 DELETE 직후에 autovacuum이 아직 안 돌았으면 플래너가 잘못된 판단을 내릴 수 있다. 이럴 때는 수동으로 통계를 갱신하면 된다.

-- 특정 테이블의 통계 갱신
ANALYZE orders;

배포 후 대량 데이터 마이그레이션을 했다면 ANALYZE를 한 번 돌려주는 게 좋다. 간단한데 빠뜨리기 쉽다.

복합 인덱스에서 컬럼 순서가 성능을 가른다

"WHERE 절에 쓰는 순서대로 복합 인덱스를 만들면 된다"는 말이 돌아다니는데, 정확하지 않다. PostgreSQL은 WHERE 절 컬럼 순서와 인덱스 컬럼 순서가 달라도 인덱스를 쓸 수 있다. 진짜 기준은 **선택도(selectivity)**다.

선택도가 높은 컬럼 — 고유한 값이 많은 컬럼 — 을 인덱스 앞에 놓아야 한다. 예를 들어 status는 ‘pending’, ‘completed’, ‘cancelled’ 3가지뿐이고, user_id는 수십만 개라면:

-- user_id를 앞에 놓는다 (선택도가 높은 컬럼 먼저)
CREATE INDEX idx_orders_user_status
ON orders (user_id, status);

-- 이 순서는 비효율적이다
CREATE INDEX idx_orders_status_user
ON orders (status, user_id);

첫 번째는 user_id로 먼저 좁히니까 탐색 범위가 확 줄어든다. 두 번째는 status = ‘completed’로 시작해봤자 전체의 60%가 해당될 수 있어서 좁히는 효과가 미미하다 (개인적으로 이건 EXPLAIN ANALYZE로 양쪽 다 찍어보는 게 가장 확실하다).

복합 인덱스에서 빠뜨리면 안 되는 개념이 leftmost prefix 규칙이다. (user_id, status, created_at) 인덱스가 있을 때:

왼쪽부터 연속으로 조건이 있어야 인덱스를 제대로 쓸 수 있다. 가운데를 건너뛰면 뒤쪽 컬럼은 인덱스 탐색이 아니라 필터로만 작동한다. 그래서 복합 인덱스를 설계할 때는 가장 자주 쓰이는 쿼리 패턴 3~4개를 먼저 뽑아놓고, 그 패턴들을 최대한 커버할 수 있는 컬럼 순서를 정하는 게 순서다.

Partial Index와 Covering Index

Partial Index — 필요한 행만 인덱스에 담기

-- status가 pending인 행만 인덱스에 포함
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

이 코드부터 보자. 전체 1200만 건 중 status = ‘pending’인 건 보통 3~5%다. 이것만 인덱스에 넣으면 인덱스 크기가 전체의 1/20 수준으로 줄어든다.

크기가 줄면 shared_buffers 캐시에 인덱스 전체가 올라갈 확률이 높아진다. INSERT/UPDATE 시 인덱스 유지 비용도 준다 — pending이 아닌 행은 이 인덱스에 영향을 주지 않으니까. 주문 대시보드 API에 이걸 적용했더니, DataGrip에서 동일 쿼리 10회 반복 실행 기준으로 12ms에서 7ms 정도로 떨어졌다. 정밀 벤치마크는 아니지만 체감은 확실했다.

주의할 점은 쿼리의 WHERE 절이 인덱스의 WHERE 조건을 포함해야 한다는 거다. WHERE status = 'pending' AND created_at > '...'이면 인덱스를 타지만, WHERE created_at > '...'만 쓰면 플래너가 이 partial index를 무시한다.

Covering Index — 힙 접근을 아예 없애기

PostgreSQL 11에서 INCLUDE 절이 추가됐다 (출처: PostgreSQL 11 Release Notes, 2018-10-18). 일반 인덱스 스캔은 인덱스에서 행 위치를 찾은 뒤 힙 테이블에서 실제 데이터를 읽는 2단계를 거친다. SELECT에 필요한 컬럼이 인덱스 안에 전부 들어있으면 힙 접근을 건너뛸 수 있다. 이게 Index Only Scan이다.

-- user_id로 검색하면서 email, name만 가져올 때
CREATE INDEX idx_users_covering
ON users (user_id) INCLUDE (email, name);

-- 이 쿼리는 Index Only Scan으로 처리된다
SELECT email, name FROM users WHERE user_id = 12345;

INCLUDE 컬럼은 B-tree 리프 노드에만 저장되고, 인덱스 탐색 키로는 사용되지 않는다. WHERE 절에 INCLUDE 컬럼을 넣어도 인덱스 탐색에는 기여하지 않으니 이 점을 혼동하면 안 된다.

자주 호출되는 단순 조회 API — 유저 프로필 조회, 주문 목록 같은 — 에 covering index를 걸면 힙 fetch가 사라지면서 응답 시간이 안정적으로 내려간다. 다만 INCLUDE 컬럼을 많이 넣을수록 인덱스 크기가 커지니까, SELECT에서 실제로 쓰는 컬럼만 골라야 한다. 여기에 대한 명확한 기준은 아직 못 잡았다. 쿼리 패턴마다 다른 것 같다.

pg_stat_statements로 병목 쿼리 추적

-- postgresql.conf 설정 후 재시작 필요
-- shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 총 실행 시간 기준 상위 10개 쿼리 조회
SELECT
  substring(query, 1, 80) AS short_query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

느린 쿼리를 일일이 찾아다니는 건 현실적이지 않다. pg_stat_statements를 쓰면 DB에서 실행된 모든 쿼리의 실행 통계를 누적 수집할 수 있다. 위 쿼리를 돌리면 시스템 전체에서 가장 많은 시간을 잡아먹는 쿼리가 바로 보인다.

여기서 흔히 하는 실수가 mean_exec_time 기준으로 정렬하는 거다. 평균 2ms짜리 쿼리라도 하루에 500만 번 호출되면 총 실행 시간이 10,000초를 넘는다. 한 번에 3초 걸리는 쿼리보다 시스템 전체에 미치는 영향은 이쪽이 훨씬 크다. total_exec_time 기준으로 정렬해야 진짜 병목이 보인다.

PostgreSQL 17에서 pg_stat_statements에 toplevel 컬럼이 추가되어, 함수나 프로시저 내부에서 실행된 쿼리와 직접 실행된 쿼리를 구분할 수 있게 됐다 (출처: PostgreSQL 17 Release Notes, 2024-09-26). 저장 프로시저 안에 숨어 있는 느린 쿼리를 찾아낼 때 유용하다.

주간 단위로 pg_stat_statements_reset()을 호출해서 통계를 초기화하고, 그 주의 상위 슬로우 쿼리를 팀 슬랙 채널에 공유하는 루틴을 만들어두면 "갑자기 느려졌어요" 류의 이슈를 조기에 잡을 수 있다. 이 루틴을 도입한 이후로 성능 관련 이슈 리포트가 체감상 절반 이하로 줄었다. 인덱스 하나 잘 거는 것보다 이런 모니터링 루틴을 꾸준히 돌리는 게 PostgreSQL 인덱스 최적화 실무에서는 장기적으로 효과가 더 큰 것 같다.

관련 글

• 개발자 번아웃 회복 루틴 — 의지력이 아닌 시스템으로 관리하는 법 – 번아웃은 의지력 부족이 아니라 시스템 부재의 문제다. 커밋 추이, 집중 시간, PR 리뷰 속도 같은 정량 지표로 번아웃을 조기에 감지하고,…