- Published on
PostgreSQL 느린 쿼리, EXPLAIN ANALYZE로 인덱스 튜닝
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
운영에서 PostgreSQL 쿼리가 느려졌을 때 가장 흔한 실수는, 증상만 보고 인덱스를 “일단 하나 더” 만드는 것입니다. 인덱스는 비용이 있습니다. 쓰기 성능 저하, VACUUM 부담 증가, 플래너 선택 혼란(잘못된 인덱스 선택) 같은 부작용이 생깁니다. 그래서 튜닝의 출발점은 항상 EXPLAIN ANALYZE 입니다. 실제로 어떤 노드에서 시간이 소비되는지, 왜 인덱스가 안 타는지, 어떤 조건이 병목인지 확인한 뒤에 인덱스를 설계해야 합니다.
이 글에서는 EXPLAIN ANALYZE 결과를 읽는 핵심 포인트와, 자주 만나는 느린 쿼리 패턴별 인덱스 튜닝 전략을 “실행 계획 근거”와 함께 정리합니다.
또한 튜닝 과정에서 테이블이 비대해져 추정치가 흔들리는 문제는 VACUUM과도 강하게 연결됩니다. 관련해서는 PostgreSQL VACUUM 안됨? dead tuple·autovacuum 튜닝 글도 같이 보면 원인-해결이 더 빨리 연결됩니다.
1) EXPLAIN 과 EXPLAIN ANALYZE 차이
EXPLAIN: 플래너가 “이렇게 실행할 것”이라고 예측한 계획만 보여줍니다(실제 실행 없음)EXPLAIN ANALYZE: 실제로 쿼리를 실행하고, 각 노드의 실제 시간/행 수/루프 횟수까지 보여줍니다
느린 쿼리는 예측이 아니라 “실제”를 봐야 합니다. 그래서 운영 튜닝은 보통 EXPLAIN (ANALYZE, BUFFERS) 조합을 씁니다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;
ANALYZE: 실제 실행 시간과 실제 행 수BUFFERS: shared buffer 히트/읽기(디스크 I/O 단서)
주의: EXPLAIN ANALYZE 는 쿼리를 실제로 실행합니다. UPDATE/DELETE 같은 DML은 롤백 가능한 트랜잭션에서 실행하거나, EXPLAIN 만 먼저 보세요.
2) 실행 계획에서 먼저 봐야 할 6가지
2.1 actual time 이 큰 노드가 범인
실행 계획은 트리 구조입니다. 최상단 노드 시간이 크다고 해서 그 노드만 문제인 게 아니라, 하위 노드에서 시간이 누적된 결과일 수 있습니다. 보통은 “가장 아래쪽에서 시간이 큰 노드”를 먼저 찾습니다.
2.2 rows 추정치와 실제치 차이
rows=... (추정) vs actual rows=... (실제) 차이가 크면 플래너가 잘못된 선택을 할 확률이 높습니다.
- 통계가 낡았거나(
ANALYZE부족) - 데이터 분포가 치우쳤거나
- 조건이 상관관계가 강한데 독립으로 가정했거나
이 경우 인덱스를 추가하기 전에 통계부터 점검해야 합니다.
ANALYZE orders;
2.3 Seq Scan 이 무조건 나쁜 건 아니다
테이블이 작거나, 조건이 대부분의 행을 읽는 형태라면 Seq Scan 이 더 빠를 수 있습니다. 중요한 건 “왜 느린가”이며, 느린 원인이 I/O인지 CPU인지 정렬인지 조인인지 확인해야 합니다.
2.4 Sort 와 Hash 는 메모리/디스크 스필 단서
정렬이나 해시가 느리면 work_mem 부족으로 디스크 스필이 발생했을 수 있습니다. BUFFERS 와 함께 보면 감이 옵니다.
2.5 loops 가 큰 Nested Loop는 위험 신호
Nested Loop 자체가 나쁜 게 아니라, 바깥쪽 행 수가 큰데 안쪽을 반복해서 많이 긁으면 폭발합니다. 이때는 인덱스가 없거나(안쪽 조건), 조인 순서가 비효율적이거나, 조인 조건이 함수로 감싸져 인덱스를 못 타는 경우가 많습니다.
2.6 Index Scan 이 있어도 느릴 수 있다
인덱스를 탔다고 끝이 아닙니다.
- 랜덤 I/O로 테이블 힙을 너무 많이 방문하는
Index Scan - 필요한 컬럼이 많아
Index Only Scan이 안 되는 경우 Bitmap Heap Scan이 반복적으로 많은 페이지를 읽는 경우
이때는 “커버링 인덱스” 또는 조건/정렬을 반영한 복합 인덱스 설계가 필요합니다.
3) 패턴 1: WHERE + ORDER BY + LIMIT 가 느리다
가장 흔한 화면 조회 패턴입니다.
SELECT id, user_id, created_at, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 50;
이 쿼리가 느리면 흔히 Sort 가 뜨거나, user_id 인덱스만 타고 결국 많은 행을 읽고 정렬합니다.
해결: 조건과 정렬을 함께 만족하는 복합 인덱스
CREATE INDEX CONCURRENTLY idx_orders_user_created_desc
ON orders (user_id, created_at DESC);
user_id로 범위를 줄이고created_at DESC순서대로 이미 정렬된 인덱스를 읽어LIMIT 50만큼만 빠르게 가져옵니다
EXPLAIN ANALYZE 에서 기대하는 변화는 다음입니다.
Sort노드가 사라지거나Index Scan using idx_orders_user_created_desc가 상단에 등장- 읽는 행 수가
LIMIT근처로 줄어듦
추가 팁: 조회 컬럼이 고정이고 테이블이 큰 경우, PostgreSQL 11+ 에서는 INCLUDE 로 커버링을 만들 수 있습니다.
CREATE INDEX CONCURRENTLY idx_orders_user_created_desc_cover
ON orders (user_id, created_at DESC)
INCLUDE (total_amount);
이렇게 하면 Index Only Scan 가능성이 올라가고, 힙 방문이 줄어 I/O가 크게 감소합니다.
4) 패턴 2: 인덱스가 있는데도 Seq Scan 이 뜬다
인덱스가 있는데 플래너가 안 쓰는 이유는 대개 아래 중 하나입니다.
- 조건이 인덱스 컬럼에 “그대로” 걸리지 않음
- 선택도가 낮아(너무 많은 행 매칭) 인덱스가 손해
- 통계가 부정확
4.1 함수로 감싼 조건
-- 나쁜 예: 컬럼에 함수를 적용
SELECT *
FROM users
WHERE lower(email) = lower('A@EXAMPLE.COM');
이 경우 일반적인 email 인덱스는 못 탑니다.
해결 1: 표현식 인덱스
CREATE INDEX CONCURRENTLY idx_users_lower_email
ON users (lower(email));
해결 2: 데이터 정규화(저장 시 소문자화)도 실전에서 많이 씁니다.
4.2 타입 캐스팅/암묵적 변환
-- 예: user_id가 bigint인데 파라미터가 text로 들어오는 경우
SELECT *
FROM orders
WHERE user_id = '42';
드라이버/ORM에서 파라미터 타입이 틀리면 캐스팅이 발생하고 플래너 선택이 꼬일 수 있습니다. 애플리케이션에서 바인딩 타입을 맞추세요.
4.3 LIKE 패턴이 앞에 와일드카드
SELECT *
FROM products
WHERE name LIKE '%iphone%';
B-tree 인덱스로는 어렵습니다. 이때는 pg_trgm 기반 GIN 인덱스가 정석입니다.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
ON products USING gin (name gin_trgm_ops);
5) 패턴 3: 조인이 느리다 (Nested Loop 폭발)
예를 들어 최근 7일 주문과 주문 아이템을 조인하는 쿼리입니다.
SELECT o.id, o.created_at, i.product_id, i.qty
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= now() - interval '7 days';
여기서 orders 는 기간 조건으로 많이 걸리고, order_items 가 큰 테이블인데 i.order_id 인덱스가 없으면 Nested Loop에서 안쪽 테이블을 반복 풀스캔할 수 있습니다.
해결: 조인 키 인덱스는 기본
CREATE INDEX CONCURRENTLY idx_order_items_order_id
ON order_items (order_id);
추가로, orders.created_at 조건이 핵심이면 아래도 고려합니다.
CREATE INDEX CONCURRENTLY idx_orders_created_at
ON orders (created_at);
EXPLAIN ANALYZE 에서 확인할 포인트는 다음입니다.
Nested Loop의 안쪽이Index Scan으로 바뀌는지loops가 큰데도 노드 시간이 줄었는지
조인 튜닝은 애플리케이션 레벨에서도 자주 터집니다. ORM에서 N+1이 발생하면 DB 인덱스만으로는 한계가 있으니, 관련해서는 Spring Boot JPA N+1 폭탄 - 배치·페치조인 튜닝 도 함께 점검해보는 게 좋습니다.
6) 패턴 4: IN (...) 이 커질수록 느려진다
SELECT *
FROM users
WHERE id IN ( ...많은 id... );
IN 리스트가 수천 개로 커지면 파싱/플래닝 비용도 커지고, 실행도 비효율적일 수 있습니다.
해결: 임시 테이블 또는 unnest 조인
WITH ids AS (
SELECT unnest($1::bigint[]) AS id
)
SELECT u.*
FROM users u
JOIN ids ON ids.id = u.id;
여기서 users.id 는 보통 PK 인덱스가 있으므로 조인이 빠르게 끝납니다.
7) 인덱스 설계 체크리스트 (실전 기준)
7.1 복합 인덱스 컬럼 순서
B-tree 복합 인덱스는 “왼쪽부터” 조건이 잘 걸릴수록 유리합니다.
- 동등 조건(
=) 컬럼을 앞에 - 범위 조건(
>=,<)이나 정렬 컬럼을 뒤에
예:
WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC- 인덱스는
(user_id, created_at DESC)가 자연스럽습니다
7.2 부분 인덱스(Partial Index)
활성 데이터만 자주 조회한다면 부분 인덱스가 강력합니다.
CREATE INDEX CONCURRENTLY idx_orders_active_user_created
ON orders (user_id, created_at DESC)
WHERE status = 'ACTIVE';
인덱스 크기가 줄어 캐시 효율이 좋아지고, 쓰기 비용도 감소합니다.
7.3 인덱스는 읽기만이 아니라 “유지비”가 든다
인덱스가 늘면 INSERT/UPDATE/DELETE 비용이 증가하고, dead tuple이 늘어 VACUUM 부담이 커질 수 있습니다. 인덱스 튜닝을 했다면, autovacuum이 따라오는지도 함께 보세요. 이 주제는 PostgreSQL VACUUM 안됨? dead tuple·autovacuum 튜닝 과 연결됩니다.
8) EXPLAIN (ANALYZE, BUFFERS) 로 튜닝 전후 검증하는 법
튜닝은 “체감”이 아니라 “수치”로 끝내야 합니다.
8.1 같은 조건으로 여러 번 실행
첫 실행은 캐시 미스 영향이 큽니다. 최소 3회 실행해 편차를 보세요.
8.2 BUFFERS 로 I/O 병목 확인
shared hit이 높고 빠르면 캐시 친화적shared read가 높고 느리면 디스크 I/O 병목 가능
8.3 결과 행 수가 같아야 비교가 의미 있다
튜닝 후 쿼리 의미가 바뀌지 않았는지(LIMIT/조인 조건/필터) 확인하세요.
9) 운영에서 자주 쓰는 보조 도구
9.1 pg_stat_statements 로 느린 쿼리 후보 찾기
가장 많이 호출되거나, 총 시간이 큰 쿼리를 먼저 잡아야 합니다.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
9.2 인덱스 생성은 CONCURRENTLY
운영에서 큰 테이블에 인덱스를 만들 때는 잠금 영향을 줄이기 위해 CONCURRENTLY 를 고려합니다.
CREATE INDEX CONCURRENTLY idx_orders_user_created_desc
ON orders (user_id, created_at DESC);
단, CONCURRENTLY 는 더 오래 걸릴 수 있고, 트랜잭션 블록 안에서 실행할 수 없습니다.
10) 정리: 느린 쿼리는 “계획을 읽고” 인덱스를 설계한다
EXPLAIN ANALYZE로 실제 병목 노드를 찾고rows추정치와 실제치 차이를 확인해 통계/데이터 분포 문제를 의심하고- 패턴별로 복합 인덱스, 표현식 인덱스, 부분 인덱스, GIN 트라이그램 같은 “맞는” 인덱스를 적용한 뒤
BUFFERS포함 실행 계획으로 전후를 검증하세요
마지막으로, DB 튜닝은 애플리케이션 구조와도 함께 봐야 효과가 큽니다. 조인이 느려서가 아니라 N+1로 쿼리 폭격을 하고 있을 수도 있고, 반대로 DB는 빠른데 애플리케이션 스레드/커넥션 설정 때문에 느릴 수도 있습니다. 이런 교차 지점은 Spring Boot 3 가상스레드 적용 후 TPS 급락 원인 같은 글과 함께 보면 전체 병목을 더 정확히 분리할 수 있습니다.