TimewareTimeware
블로그 목록으로
블로그

데이터베이스 인덱스 전략: 느린 쿼리를 10배 빠르게 만드는 실전 가이드

EXPLAIN ANALYZE 읽는 법부터 복합 인덱스 설계, 부분 인덱스, 함수 인덱스까지. 실제 프로덕션 쿼리를 8초에서 0.8초로 줄인 인덱스 최적화 실전 사례.

2026년 3월 5일•Timeware Engineeringpostgresqldatabaseperformancebackendoptimization
데이터베이스 인덱스 전략: 느린 쿼리를 10배 빠르게 만드는 실전 가이드

요약

EXPLAIN ANALYZE 읽는 법부터 복합 인덱스 설계, 부분 인덱스, 함수 인덱스까지. 실제 프로덕션 쿼리를 8초에서 0.8초로 줄인 인덱스 최적화 실전 사례.

진단 시작관련 서비스유사 사례

데이터베이스 인덱스 전략: 느린 쿼리를 10배 빠르게 만드는 실전 가이드

Executive Summary - Topic: PostgreSQL 인덱스 설계와 쿼리 최적화 실전 패턴 - Target: 백엔드 개발자, DBA, 성능 최적화 담당자 - TL;DR 1: 잘못된 인덱스는 없는 것보다 나쁠 수 있다 — 쓰기 성능과 용량을 희생한다 - TL;DR 2: EXPLAIN ANALYZE의 Seq Scan vs Index Scan, cost, rows 3가지만 읽어도 문제의 80%를 파악한다 - TL;DR 3: 복합 인덱스 컬럼 순서는 선택도(cardinality) 높은 것부터, WHERE 절 패턴에 맞게

작년 커머스 클라이언트에서 긴급 호출을 받았습니다. 주문 조회 API가 평균 8초 응답 시간을 기록하며 사용자 이탈이 급증한다는 것이었습니다. EXPLAIN ANALYZE 한 번으로 문제를 찾는 데 5분, 해결하는 데 30분이 걸렸습니다.

그 경험을 바탕으로 인덱스 최적화의 핵심을 정리합니다.

기초: EXPLAIN ANALYZE 읽는 법

모든 최적화는 여기서 시작합니다.

sql
1EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
2SELECT o.id, o.status, u.name, u.email
3FROM orders o
4JOIN users u ON o.user_id = u.id
5WHERE o.status = 'pending'
6  AND o.created_at > NOW() - INTERVAL '30 days'
7ORDER BY o.created_at DESC
8LIMIT 50;

출력 읽는 법:

code
1Limit  (cost=0.00..45823.12 rows=50 width=89) (actual time=8234.123..8234.567 rows=50 loops=1)
2  ->  Sort  (cost=0.00..45823.12 rows=1234567 width=89) (actual time=8234.120..8234.123 rows=50 loops=1)
3        Sort Key: o.created_at DESC
4        Sort Method: top-N heapsort  Memory: 32kB
5        ->  Hash Join  (cost=...) (actual time=... rows=... loops=1)
6              ->  Seq Scan on orders  (cost=0.00..43212.34 rows=1234567 width=...)  ← 문제!
7                    Filter: ((status = 'pending') AND (created_at > ...))
8                    Rows Removed by Filter: 987654

핵심 지표:

  • Seq Scan → 인덱스 미사용 (테이블 전체 스캔)
  • cost=0.00..43212.34 → 오른쪽 숫자가 실제 비용 (높을수록 느림)
  • Rows Removed by Filter: 987654 → 인덱스가 있으면 버려지지 않을 행 수
  • actual time=8234 → 밀리초 단위 실제 실행 시간

실전 케이스 1: 복합 인덱스 순서 실수

문제 쿼리:

sql
1SELECT * FROM orders
2WHERE user_id = 12345
3  AND status = 'completed'
4  AND created_at > '2026-01-01';

잘못된 인덱스:

sql
1-- status의 선택도가 낮아서 (pending/completed/cancelled 3개)
2-- 첫 번째 컬럼으로 두면 효과가 거의 없음
3CREATE INDEX idx_wrong ON orders (status, user_id, created_at);

올바른 인덱스:

sql
1-- user_id: 선택도 높음 (수백만 명 중 한 명)
2-- status: 선택도 낮음 (3개 값)
3-- created_at: 범위 조회이므로 마지막
4CREATE INDEX idx_orders_user_status_created
5  ON orders (user_id, status, created_at DESC);

복합 인덱스 컬럼 순서 원칙:

  1. = 조건 컬럼 먼저 (등치 비교)
  2. 선택도 높은 것 먼저 (user_id가 status보다 선택도 높음)
  3. 범위 조건 (>, <, BETWEEN) 마지막
  4. ORDER BY 컬럼을 인덱스에 포함하면 Sort 제거 가능

실전 케이스 2: 부분 인덱스로 90% 절약

문제:orders 테이블에 1억 건이 있는데, 실제 조회는 status = 'pending' (전체의 3%)에 집중됩니다.

일반 인덱스 (비효율):

sql
1-- 1억 건 전체에 인덱스 생성 → 큰 용량, 쓰기 오버헤드
2CREATE INDEX idx_orders_status ON orders (status);

부분 인덱스 (효율적):

sql
1-- 300만 건만 인덱싱 → 작은 용량, 빠른 조회
2CREATE INDEX idx_orders_pending
3  ON orders (created_at DESC)
4  WHERE status = 'pending';
5 
6-- 실제 크기 비교
7-- 일반 인덱스: 2.1GB
8-- 부분 인덱스: 180MB (91% 절감)

부분 인덱스가 사용되려면 쿼리의 WHERE 절이 인덱스 조건을 포함해야 합니다:

sql
1-- 이 쿼리에서 idx_orders_pending 사용됨
2SELECT * FROM orders
3WHERE status = 'pending'
4  AND created_at > NOW() - INTERVAL '7 days';
5 
6-- 이 쿼리에서는 사용 안 됨 (status 조건 없음)
7SELECT * FROM orders
8WHERE created_at > NOW() - INTERVAL '7 days';

실전 케이스 3: 함수 인덱스

문제 쿼리:

sql
1-- 이메일 대소문자 구분 없이 검색
2SELECT * FROM users
3WHERE LOWER(email) = LOWER('[email protected]');

일반 인덱스로는 해결 안 됨:

sql
1CREATE INDEX idx_users_email ON users (email);
2-- LOWER()가 적용되면 인덱스 사용 안 됨!

함수 인덱스로 해결:

sql
1CREATE INDEX idx_users_email_lower
2  ON users (LOWER(email));
3 
4-- 또는 PostgreSQL의 citext 타입 사용
5CREATE EXTENSION IF NOT EXISTS citext;
6ALTER TABLE users ALTER COLUMN email TYPE citext;
7CREATE INDEX idx_users_email ON users (email);

자주 쓰는 함수 인덱스 패턴:

sql
1-- JSON 필드 인덱스
2CREATE INDEX idx_orders_metadata_type
3  ON orders ((metadata->>'type'));
4 
5-- 날짜 추출 인덱스
6CREATE INDEX idx_orders_year_month
7  ON orders (EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));
8 
9-- 배열 포함 인덱스 (GIN)
10CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

실전 케이스 4: 커버링 인덱스

문제: 인덱스를 탔는데도 느린 경우 — "Heap Fetch" 때문입니다.

code
1Index Scan on idx_orders_user  (cost=...) (actual time=1234.567)
2  Index Cond: (user_id = 12345)
3  →  Heap Fetches: 45678  ← 이게 문제!

인덱스에서 행 위치를 찾은 후, 실제 테이블(Heap)로 가서 나머지 컬럼을 가져오는 작업입니다.

커버링 인덱스로 Heap Fetch 제거:

sql
1-- SELECT에서 필요한 컬럼을 인덱스에 모두 포함
2CREATE INDEX idx_orders_covering
3  ON orders (user_id, status)
4  INCLUDE (id, total_amount, created_at);  -- INCLUDE 절
5 
6-- 이제 이 쿼리는 Heap을 전혀 안 건드림 (Index Only Scan)
7SELECT id, status, total_amount, created_at
8FROM orders
9WHERE user_id = 12345 AND status = 'completed';

실전 케이스 5: 8초 → 0.8초 실제 사례

문제 쿼리 (8.2초):

sql
1SELECT
2  o.id,
3  o.order_number,
4  o.total_amount,
5  o.status,
6  o.created_at,
7  u.name AS customer_name,
8  u.email AS customer_email,
9  COUNT(oi.id) AS item_count
10FROM orders o
11JOIN users u ON o.user_id = u.id
12JOIN order_items oi ON oi.order_id = o.id
13WHERE o.status IN ('pending', 'processing')
14  AND o.created_at BETWEEN '2026-01-01' AND '2026-03-01'
15  AND u.country = 'KR'
16GROUP BY o.id, o.order_number, o.total_amount, o.status, o.created_at,
17         u.name, u.email
18ORDER BY o.created_at DESC
19LIMIT 20;

EXPLAIN 분석 결과:

  • orders: Seq Scan (전체 230만 건 스캔)
  • users: Seq Scan (전체 150만 건 스캔)
  • order_items: Seq Scan (전체 890만 건 스캔)
  • Sort: 메모리 초과로 디스크 정렬

적용한 인덱스:

sql
1-- orders: status + created_at 범위 조회 최적화
2CREATE INDEX idx_orders_status_created
3  ON orders (status, created_at DESC)
4  WHERE status IN ('pending', 'processing');
5 
6-- users: country 필터 + join 조건
7CREATE INDEX idx_users_country_id
8  ON users (country, id)
9  WHERE country = 'KR';
10 
11-- order_items: join 조건 커버링
12CREATE INDEX idx_order_items_order_id
13  ON order_items (order_id)
14  INCLUDE (id);

결과 (0.82초, 90% 개선):

code
1이전: 8.234s (Seq Scan × 3, Disk Sort)
2이후: 0.823s (Index Scan × 3, Memory Sort)

인덱스 유지 관리

사용 안 되는 인덱스 찾기:

sql
1SELECT
2  schemaname,
3  tablename,
4  indexname,
5  idx_scan,
6  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
7FROM pg_stat_user_indexes
8WHERE idx_scan = 0
9  AND schemaname = 'public'
10ORDER BY pg_relation_size(indexrelid) DESC;

중복 인덱스 찾기:

sql
1SELECT
2  indrelid::regclass AS table_name,
3  array_agg(indexrelid::regclass) AS duplicate_indexes
4FROM pg_index
5GROUP BY indrelid, indkey
6HAVING COUNT(*) > 1;

인덱스 bloat 확인 (VACUUM 필요성):

sql
1SELECT
2  indexrelid::regclass AS index_name,
3  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
4  round(100 * pg_stat_get_dead_tuples(indexrelid) /
5        NULLIF(pg_stat_get_live_tuples(indexrelid), 0), 1) AS bloat_ratio
6FROM pg_stat_user_indexes
7WHERE schemaname = 'public'
8ORDER BY pg_relation_size(indexrelid) DESC
9LIMIT 20;

인덱스 체크리스트

상황권장 인덱스 유형
등치 + 범위 조건복합 인덱스 (등치 먼저)
일부 행만 조회 (e.g., status='active')부분 인덱스
LOWER(), EXTRACT() 등 함수 사용함수 인덱스
SELECT 컬럼이 적고 고정적커버링 인덱스 (INCLUDE)
배열, JSONB, 전문 검색GIN 인덱스
지리 데이터GiST 인덱스
동시 쓰기 중 인덱스 생성 필요CREATE INDEX CONCURRENTLY

FAQ

Q. 인덱스를 많이 만들면 읽기 성능이 무조건 좋아지나요? A. 아닙니다. 인덱스는 INSERT, UPDATE, DELETE 성능을 저하시킵니다. 또한 쿼리 플래너가 잘못된 인덱스를 선택할 수도 있습니다. 실제로 사용되는 쿼리 패턴에 맞게 설계하고, 정기적으로 pg_stat_user_indexes로 사용률을 모니터링하세요.

Q. EXPLAIN과 EXPLAIN ANALYZE의 차이는 무엇인가요? A. EXPLAIN은 실행 계획만 보여줍니다 (추정치). EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 실제 통계를 보여줍니다 (정확하지만 실제 실행됨). 프로덕션에서 분석할 때는 BEGIN/ROLLBACK으로 감싸거나, 읽기 전용 쿼리에만 사용하세요.

Q. 인덱스를 추가했는데 쿼리 플래너가 사용하지 않아요. A. 통계가 최신이 아닐 수 있습니다. ANALYZE table_name을 실행해 통계를 업데이트하세요. 또는 인덱스 선택도가 낮아 플래너가 Seq Scan을 더 효율적으로 판단할 수 있습니다. enable_seqscan = off로 임시 강제해 인덱스 성능을 비교해보세요.