PostgreSQL 쿼리 실행 계획 분석과 인덱스 최적화 실무 적용 가이드

쿼리가 왜 이렇게 느린지 알 수 없어서 막막했던 경험이 있는가. 인덱스를 만들었는데도 PostgreSQL 쿼리 실행 계획 최적화 방법을 모르면 EXPLAIN 결과가 눈에 들어오지 않는다. 숫자가 가득한 출력 화면을 보면서 '이게 느린 건지 빠른 건지'조차 판단하기 어렵다.

실무에서 자주 마주치는 문제는 크게 두 가지다. 첫째, 인덱스는 분명히 있는데 플래너가 Seq Scan을 선택한다. 둘째, 어제까지 빠르던 쿼리가 오늘 갑자기 느려졌다. 두 상황 모두 PostgreSQL 쿼리 플래너(Query Planner)가 실행 계획을 결정하는 방식을 이해하면 실마리가 보인다.

이 글에서는 플래너 동작 원리부터 EXPLAIN ANALYZE 출력 완전 해석, 통계 정보(pg_statistic) 갱신 전략, 플래너 힌트(pg_hint_plan) 활용법, 실전 케이스 스터디까지 단계별로 다룬다. 인덱스 구조 자체(복합 인덱스 순서, 부분 인덱스, 커버링 인덱스 설계)에 대한 심화 내용은 PostgreSQL 대용량 데이터 인덱스 최적화 및 성능 튜닝 가이드를 함께 참고하면 두 관점을 연결해서 이해할 수 있다.

PostgreSQL 쿼리 플래너가 실행 계획을 결정하는 원리

PostgreSQL 플래너는 비용 기반 최적화기(Cost-Based Optimizer, CBO)다. 주어진 SQL 문을 실행할 수 있는 모든 경우의 수를 탐색한 뒤, 각 경로마다 예상 비용(cost)을 계산하고 가장 낮은 비용의 경로를 실행 계획으로 선택한다.

비용 단위는 추상적인 숫자로, seq_page_cost(기본값 1.0)를 기준으로 상대적인 값이 매겨진다. random_page_cost는 기본값 4.0으로 랜덤 I/O가 순차 I/O보다 4배 비싸다는 가정을 반영한다. SSD 환경이라면 이 비율이 실제와 맞지 않아 플래너가 Index Scan 대신 Seq Scan을 선택하는 이유가 되기도 한다.

-- SSD 환경에서는 random_page_cost를 낮춰 Index Scan 선호도를 높인다
SET random_page_cost = 1.1;

-- 또는 특정 테이블스페이스에만 적용
ALTER TABLESPACE ssd_ts SET (random_page_cost = 1.1);

플래너가 참조하는 주요 입력값은 세 가지다. 첫 번째는 pg_class.reltuples(테이블 총 행 수 추정치), 두 번째는 pg_statistic에 저장된 컬럼별 통계(분포, 고유 값 수, MCV 등), 세 번째는 GUC 파라미터(work_mem, enable_seqscan 등)다. 이 세 가지 중 하나라도 현실과 어긋나면 플래너는 최선이 아닌 계획을 선택한다.

플래너 동작 단계를 요약하면 다음과 같다.

SQL 파싱 → 논리적 쿼리 트리 생성 → 플래너가 경로 탐색 → 비용 추정 → 최소 비용 경로 선택 → 실행기(Executor)로 전달

조인이 포함된 쿼리에서는 경우의 수가 기하급수적으로 늘어나기 때문에 join_collapse_limit(기본값 8)를 초과하는 조인부터는 모든 경로를 탐색하지 않고 유전 알고리즘(Genetic Query Optimizer, GEQO)으로 전환한다. 조인 테이블이 많고 계획이 이상하다면 이 점도 의심해볼 만하다.

EXPLAIN ANALYZE 출력 완전 해석 — 비용, 노드, 버퍼 읽기

EXPLAIN ANALYZE는 PostgreSQL 성능 분석의 시작점이다. 실제 실행 결과와 플래너 예측값을 동시에 보여주기 때문에 '예측 대비 실측 차이'가 얼마나 큰지 파악할 수 있다.

-- BUFFERS 옵션을 추가하면 캐시 히트/미스 정보도 확인 가능
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name, SUM(oi.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= '2026-01-01'
GROUP BY o.id, c.name;

실제 출력 예시는 다음과 같다.

HashAggregate  (cost=4821.30..4923.50 rows=10220 width=48)
               (actual time=312.45..318.72 rows=8941 loops=1)
  Buffers: shared hit=2103 read=1847
  ->  Hash Join  (cost=312.00..4512.80 rows=30850 width=40)
                 (actual time=18.32..289.61 rows=30412 loops=1)
        Hash Cond: (o.customer_id = c.id)
        Buffers: shared hit=1982 read=1847
        ->  Index Scan using idx_orders_created_at on orders o
              (cost=0.43..3891.20 rows=30850 width=16)
              (actual time=0.08..201.34 rows=30412 loops=1)
              Index Cond: (created_at >= '2026-01-01')
              Buffers: shared hit=412 read=1847
        ->  Hash  (cost=185.00..185.00 rows=9800 width=36)
                  (actual time=17.91..17.91 rows=9800 loops=1)
              Buffers: shared hit=1570
              ->  Seq Scan on customers c
                    (cost=0.00..185.00 rows=9800 width=36)
                    (actual time=0.02..9.14 rows=9800 loops=1)
                    Buffers: shared hit=1570
Planning Time: 2.13 ms
Execution Time: 320.18 ms

각 항목이 의미하는 바를 정리하면 다음과 같다.

항목	설명	주의 기준
cost=X..Y	X: 첫 번째 행 반환까지 비용, Y: 전체 완료 비용 (추정)	자식 노드 대비 비용 급증 확인
rows=N (cost 옆)	플래너 예측 행 수	actual rows와 10배 이상 차이 시 통계 갱신 필요
actual time=A..B	A: 첫 행까지 실측 ms, B: 전체 실측 ms	—
actual rows=N	실제 반환된 행 수	예측 rows와 비교하여 통계 정확도 판단
loops=N	해당 노드가 반복 실행된 횟수	actual time은 1회 기준이므로 loops 곱하면 총 소요 시간
shared hit=N	공유 버퍼 캐시에서 읽은 블록 수	read가 많으면 디스크 I/O 발생
shared read=N	디스크에서 읽은 블록 수	이 값이 크면 I/O 병목 의심

가장 먼저 살펴봐야 할 점은 rows 예측치와 actual rows 실측치 간의 격차다. 위 예시에서 HashAggregate가 10,220행을 예측했지만 실제 8,941행이 반환된 것은 허용 가능한 수준이다. 반면 예측 100행에 실측 50,000행이라면 통계가 심각하게 낡은 것이다.

loops 값을 간과하는 실수도 잦다. 중첩 루프 조인에서 자식 노드의 actual time은 루프 1회 기준이므로, 총 소요 시간은 actual time * loops로 계산해야 한다. DML 쿼리(UPDATE, DELETE)를 분석할 때는 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; 패턴으로 데이터 변경 없이 실행 계획을 확인할 수 있다.

통계 정보(pg_statistic) 갱신 전략으로 플래너 판단력 높이기

플래너 예측이 실측과 크게 어긋나는 가장 흔한 원인은 낡은 통계 정보다. PostgreSQL은 autovacuum 프로세스가 자동으로 ANALYZE를 수행하지만, 데이터 변화 속도가 빠른 테이블에서는 자동 트리거 조건을 충족하기 전에 통계가 구식이 되는 경우가 많다.

pg_stats 뷰(사용자 친화 인터페이스)를 직접 조회하면 현재 통계 상태를 확인할 수 있다.

-- 컬럼별 통계 정보 확인 (pg_stats는 pg_statistic의 사용자 친화 뷰)
SELECT
    tablename,
    attname           AS column_name,
    n_distinct,       -- 고유 값 추정 수 (음수이면 비율)
    correlation,      -- 물리적 순서와 논리적 순서의 상관도 (1에 가까울수록 Index Scan 유리)
    most_common_vals, -- 가장 빈번한 값 목록 (MCV)
    most_common_freqs -- MCV별 출현 빈도
FROM pg_stats
WHERE tablename = 'orders'
  AND attname = 'status';

통계 갱신 전략은 테이블 크기와 변화 빈도에 따라 다르게 설계해야 한다.

-- 특정 테이블만 즉시 통계 갱신
ANALYZE orders;

-- 특정 컬럼의 통계 샘플 크기를 늘려 정밀도 향상
-- 기본값은 default_statistics_target (기본 100, 최대 10000)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders (status);

-- 테이블 전체 데드 튜플 정리 + 통계 갱신 동시 수행
VACUUM ANALYZE orders;

실무에서 경험한 패턴 중 하나는, 배치 작업으로 수백만 건을 INSERT한 직후 쿼리 성능이 갑자기 떨어지는 케이스다. autovacuum이 아직 실행되지 않아 통계가 이전 상태 그대로인 경우가 많다. 이때는 배치 완료 후 ANALYZE 명령을 명시적으로 실행하는 것을 운영 절차에 포함시키는 것이 효과적이다.

-- autovacuum 통계 갱신 이력 확인 (테이블별)
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

last_autoanalyze가 수일 전이거나 NULL이면 통계 갱신이 제때 이뤄지지 않고 있다는 신호다. PostgreSQL 공식 문서에서는 default_statistics_target 기본값 100이 대부분의 상황에서 적절하며, 고분산 컬럼에 대해서만 선택적으로 높이는 방식을 권장한다.

플래너가 잘못된 계획을 선택할 때 — pg_hint_plan 활용법

통계를 갱신하고 인덱스를 재검토해도 플래너가 계속 비효율적인 계획을 선택하는 경우가 있다. Oracle의 /*+ INDEX(...) */ 힌트와 같은 기능을 PostgreSQL에서 제공하는 확장이 pg_hint_plan이다.

설치는 확장 모듈로 진행한다.

-- pg_hint_plan 확장 활성화 (superuser 권한 필요)
CREATE EXTENSION pg_hint_plan;

-- 세션 단위로 로드
LOAD 'pg_hint_plan';

힌트는 SQL 문 안에 주석 형태로 삽입한다. 플래너는 힌트를 인식해 지정된 방식으로 계획을 수립한다.

-- Index Scan 강제 지정
/*+ IndexScan(orders idx_orders_created_at) */
SELECT id, customer_id
FROM orders
WHERE created_at >= '2026-01-01';

-- 특정 조인 방식 강제 (Hash Join 대신 Nested Loop)
/*+ NestLoop(orders order_items) IndexScan(orders idx_orders_id) */
SELECT o.id, oi.product_id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 12345;

-- 플래너 예측 행 수를 덮어써서 조인 순서 교정
/*+ Rows(orders #100) Leading(orders customers order_items) */
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'PENDING';

주요 힌트 유형을 정리하면 다음과 같다.

힌트	적용 대상	설명
SeqScan(table)	스캔 방식	순차 스캔 강제
IndexScan(table index)	스캔 방식	특정 인덱스 스캔 강제
IndexOnlyScan(table index)	스캔 방식	인덱스 온리 스캔 강제
NestLoop(t1 t2)	조인 방식	Nested Loop Join 강제
HashJoin(t1 t2)	조인 방식	Hash Join 강제
MergeJoin(t1 t2)	조인 방식	Merge Join 강제
Rows(table #N)	행 수 보정	플래너 예측 행 수를 N으로 덮어씀
Leading(t1 t2 t3)	조인 순서	조인 처리 순서 강제

주의. pg_hint_plan은 어디까지나 임시 해결책이다. 힌트를 쿼리에 삽입하면 통계가 갱신되거나 데이터 분포가 변해도 힌트가 그대로 적용되어 오히려 역효과가 날 수 있다. 근본 원인(낡은 통계, 잘못된 인덱스 설계)을 해결하는 것이 우선이며, 힌트는 급한 불을 끄는 용도로만 사용해야 한다.

실전 EXPLAIN 분석 케이스 스터디 3가지

케이스 1 — 인덱스가 있는데 Seq Scan이 발생하는 경우

-- 문제 쿼리
SELECT * FROM logs WHERE level = 'ERROR';

-- EXPLAIN 결과 (인덱스가 있음에도 Seq Scan 선택)
-- Seq Scan on logs  (cost=0.00..18420.00 rows=12 width=256)
--   Filter: ((level)::text = 'ERROR'::text)

플래너가 level 컬럼에 인덱스가 있음에도 Seq Scan을 선택한 이유는 통계상 ERROR 값의 비율이 매우 낮다고 알고 있었는데, 실제로는 대량 INSERT 이후 ERROR 비율이 역전된 상황이었다. ANALYZE logs;를 실행하자마자 플래너가 Index Scan으로 전환됐다. 이 케이스에서 핵심은 통계 갱신 하나로 문제가 해결됐다는 점이다.

케이스 2 — Nested Loop 조인에서 loops 수가 폭증하는 경우

-- EXPLAIN ANALYZE 결과 일부
Nested Loop  (cost=0.43..589.34 rows=1 width=64)
             (actual time=0.08..9834.21 rows=1 loops=1)
  ->  Seq Scan on events  (actual time=0.05..9800.12 rows=92341 loops=1)
  ->  Index Scan on users  (actual time=0.00..0.00 rows=1 loops=92341)

events 테이블의 user_id 컬럼에 인덱스가 없어 Seq Scan이 발생하고, 그 결과 92,341번의 루프가 돌면서 users 인덱스를 92,341회 조회했다. 총 실행 시간은 9.8초였다. events(user_id) 인덱스를 추가하자 전체 실행 시간이 0.04초로 줄었다. loops 값을 보지 않았다면 어디서 시간이 소비되는지 파악하지 못했을 것이다.

케이스 3 — work_mem 부족으로 디스크 정렬이 발생하는 경우

-- EXPLAIN ANALYZE 결과 일부
Sort  (cost=15234.82..15484.82 rows=100000 width=128)
      (actual time=4821.34..5102.71 rows=100000 loops=1)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 18432kB

Sort Method: external merge Disk는 work_mem이 부족해 정렬이 디스크로 넘쳐났다는 신호다. 메모리 정렬(quicksort)에 비해 수십 배 느려질 수 있다. 세션 단위로 work_mem을 늘리거나, 해당 쿼리에서 페이지네이션으로 처리 범위를 줄이면 해결된다.

-- 세션 단위로 work_mem 조정 (전역 변경은 모든 세션 메모리 사용에 영향)
SET work_mem = '64MB';

-- 이후 동일 쿼리 재실행 시 Sort Method: quicksort (메모리 정렬)로 변경되는지 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ORDER BY created_at DESC;

자주 묻는 질문 (FAQ)

Q. EXPLAIN과 EXPLAIN ANALYZE의 차이는 무엇인가요?

EXPLAIN은 쿼리를 실행하지 않고 플래너가 예상하는 계획과 비용만 출력합니다. EXPLAIN ANALYZE는 쿼리를 실제로 실행하여 각 노드의 실측 소요 시간과 처리 행 수를 함께 보여줍니다. DML 쿼리(UPDATE, DELETE)를 분석할 때는 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; 패턴으로 실행 후 롤백하면 데이터 변경 없이 분석할 수 있습니다.

Q. Seq Scan이 발생한다고 무조건 문제인가요?

반드시 그렇지는 않습니다. 반환되는 행이 전체 테이블의 5~10% 이상일 때는 Seq Scan이 Index Scan보다 효율적입니다. 인덱스를 사용하면 랜덤 I/O가 발생하는데, 많은 행을 읽어야 한다면 순차 읽기가 더 빠릅니다. 문제가 되는 Seq Scan은 적은 행을 반환해야 하는 쿼리에서 플래너가 통계를 잘못 읽어 Index Scan을 건너뛸 때입니다.

Q. ANALYZE와 VACUUM ANALYZE의 차이는 무엇인가요?

ANALYZE만 실행하면 pg_statistic 통계 갱신만 수행합니다. VACUUM ANALYZE는 데드 튜플 제거(VACUUM)와 통계 갱신(ANALYZE)을 함께 수행합니다. 데드 튜플이 많은 테이블은 통계가 갱신되더라도 실제 행 수 추정이 왜곡될 수 있으므로, 일반적으로 VACUUM ANALYZE를 함께 사용하는 것이 권장됩니다.

Q. pg_hint_plan은 언제 사용해야 하나요?

통계 갱신이나 인덱스 재설계로도 플래너가 잘못된 계획을 선택하는 문제를 해결하지 못할 때 임시 해결책으로 사용합니다. 특히 조인 순서가 잘못 선택되거나 고도로 편향된 데이터 분포 때문에 플래너가 지속적으로 나쁜 계획을 선택하는 경우에 유용합니다. 단, 근본 원인을 해결하지 않은 채 장기 의존하면 데이터 변화에 따라 힌트가 역효과를 낼 수 있어 유지보수가 어려워집니다.

Q. default_statistics_target 값을 올리면 항상 플래너 정확도가 높아지나요?

반드시 그렇지는 않습니다. 샘플 크기가 커지면 ANALYZE 실행 시간이 길어지고 pg_statistic 메모리 사용량도 증가합니다. 고분산 컬럼(UUID, 타임스탬프, 긴 문자열)에만 선택적으로 높은 값을 지정하는 것이 효과적이며, 단순한 boolean이나 소수의 고정 값을 갖는 컬럼은 기본값으로 충분합니다.

실행 계획 분석을 습관으로 만드는 첫걸음

PostgreSQL 쿼리 실행 계획 최적화 방법은 한 번 익혀두면 성능 문제를 만날 때마다 체계적으로 접근할 수 있는 무기가 된다. 오늘 당장 할 수 있는 첫 번째 액션은 간단하다. 현재 운영 중인 데이터베이스에서 가장 자주 실행되는 쿼리 하나를 골라 EXPLAIN (ANALYZE, BUFFERS) 결과를 직접 읽어보는 것이다.

rows 예측치와 actual rows 실측치가 10배 이상 차이 나는 노드가 있다면 바로 그 테이블에 ANALYZE를 실행해보자. 통계 갱신 하나로 실행 계획이 바뀌는 경험을 하면, 이후 성능 분석 접근 방식이 완전히 달라진다. loops 값과 Sort Method: external merge Disk 문구도 눈에 익혀두면 실전에서 바로 병목 지점을 집어낼 수 있다.

쿼리 실행 계획 분석에 익숙해진 뒤에는 인덱스 구조 설계(복합 인덱스 컬럼 순서, 부분 인덱스, 커버링 인덱스)까지 연결해서 보면 최적화 효과가 배가된다. 관련 내용은 PostgreSQL 대용량 데이터 인덱스 최적화 및 성능 튜닝 가이드에서 이어서 확인할 수 있다.

• 관련 글
• PostgreSQL 대용량 데이터 인덱스 최적화 및 성능 튜닝
• PostgreSQL 데드락 분석과 트러블슈팅