EXPLAIN ANALYZE 읽는 법, B-tree·GIN·GiST 인덱스 전략, 성능 병목 5가지, pg_stat_statements 활용법을 실전 쿼리와 함께 정리한다.
한 줄 요약: PostgreSQL 쿼리가 느리다면 EXPLAIN ANALYZE부터 시작해라. 이 글에서는 실행 계획 읽는 법, 인덱스 설계 전략, 자주 발생하는 성능 병목과 해결법을 실전 쿼리와 함께 정리한다.
서비스가 커질수록 쿼리 성능 문제는 반드시 만나게 된다. 처음에는 수십 밀리초였던 응답이 데이터가 쌓이면서 수 초로 늘어나고, 특정 시간대에만 타임아웃이 발생하거나, 배포 직후 DB CPU가 치솟는 상황이 생긴다. 문제는 "느리다"는 사실만 알고 원인을 특정하지 못하는 경우가 대부분이라는 점이다.
이 글은 PostgreSQL 성능 최적화를 진단 → 인덱스 설계 → 쿼리 리팩토링 → 모니터링 순서로 실전 위주로 정리한다.
이 글이 필요한 사람
EXPLAIN 결과를 봐도 어디서부터 손봐야 할지 모르는 백엔드 개발자
인덱스를 만들었는데 쿼리가 여전히 Seq Scan을 타는 상황
N+1 문제, 대용량 페이지네이션, JOIN 최적화가 필요한 실무자
pg_stat_statements로 슬로우 쿼리를 추적하고 싶은 DBA/개발자
기준일: 2026년 3월 | PostgreSQL 16 기준
EXPLAIN ANALYZE 읽는 법
PostgreSQL 성능 최적화의 첫 단계는 실행 계획(Execution Plan)을 읽는 것이다. EXPLAIN은 옵티마이저가 선택한 계획을 보여주고, EXPLAIN ANALYZE는 실제로 쿼리를 실행하면서 각 노드의 실측값을 함께 출력한다.
실행 계획 출력에서 반드시 확인해야 할 항목은 다음과 같다.
항목
의미
체크 포인트
Seq Scan
테이블 전체를 순차 읽기
대용량 테이블에서 등장하면 인덱스 후보
Index Scan
인덱스 탐색 후 Heap 접근
선택성이 낮으면 Seq Scan보다 느릴 수 있음
Index Only Scan
Heap 접근 없이 인덱스만으로 응답
커버링 인덱스 효과. 가장 빠름
actual time
노드 실행 시간 (ms)
첫 번째 row / 전체 완료 시간
rows
추정 vs 실제 행 수
추정치가 크게 틀리면 ANALYZE 필요
loops
노드 반복 횟수
Nested Loop에서 loops가 많으면 N+1 신호
cost는 옵티마이저의 추정값이고 단위는 상대적 비용이다. 절대 시간이 아니므로 실제 성능 판단은 actual time을 기준으로 해야 한다. 추정 rows와 actual rows의 차이가 10배 이상이라면 ANALYZE로 통계를 갱신하거나 더 촘촘한 통계 목표값(ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500)을 설정할 필요가 있다.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)를 함께 사용하면 캐시 히트율(shared hit vs shared read)도 확인할 수 있어 I/O 병목 진단에 유용하다.
EXPLAIN ANALYZE 기본 사용법
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.total DESC
LIMIT 100;
인덱스 전략 — B-tree, GIN, GiST 선택 기준
PostgreSQL은 다양한 인덱스 유형을 지원한다. 워크로드 유형에 맞지 않는 인덱스는 오히려 쓰기 성능만 저하시키고 쿼리 성능에는 도움이 되지 않는다. 아래 비교표를 기준으로 선택하면 된다.
인덱스 유형
주요 사용 사례
예시 컬럼/연산
주의사항
B-tree
등호·범위 조건, 정렬
=, <, >, BETWEEN, ORDER BY
기본값. 대부분의 경우 B-tree로 시작
GIN
배열, JSONB, 전문 검색
@>, <@, @@, tsvector
빌드 시간 길고 업데이트 비용 높음
GiST
지리 데이터, 범위 타입
PostGIS, tsrange, daterange
GIN보다 빌드 빠르나 검색은 느림
BRIN
시계열·로그 대용량 테이블
물리적으로 정렬된 timestamp 컬럼
크기 매우 작지만 선택성 낮음
Hash
등호 조건 전용
= 연산만
범위 조건 불가. 일반적으로 B-tree 권장
복합 인덱스를 설계할 때는 선택성이 높은 컬럼을 앞에 두는 것이 기본 원칙이다. 단, WHERE a = 1 AND b > 10 ORDER BY b 처럼 등호 조건 컬럼을 앞에, 범위·정렬 컬럼을 뒤에 배치하면 인덱스 하나로 필터와 정렬을 모두 처리할 수 있다.
부분 인덱스(Partial Index)는 특정 조건을 만족하는 행만 인덱싱해 인덱스 크기를 줄이고 쓰기 비용을 낮춘다. WHERE status = 'active'처럼 조건이 고정된 쿼리 패턴이 많을 때 매우 효과적이다.
B-tree, GIN, GiST, BRIN 인덱스의 적합한 사용 시나리오.
복합 인덱스와 부분 인덱스 생성
-- 복합 인덱스: 자주 함께 조회되는 컬럼
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC);
-- 부분 인덱스: 특정 조건만 인덱싱
CREATE INDEX idx_orders_active
ON orders (created_at)
WHERE status = 'active';
자주 발생하는 성능 병목 5가지
현장에서 반복적으로 마주치는 성능 병목 패턴을 정리한다. 원인과 해결법 중심으로 서술한다.
1. N+1 쿼리
ORM을 사용할 때 가장 흔하게 발생한다. 목록을 가져온 뒤 각 항목마다 연관 데이터를 개별 쿼리로 조회하는 패턴이다. 100개 행이면 101번의 쿼리가 발생한다. 해결책은 JOIN으로 한 번에 가져오거나, ORM의 eager loading(includes, preload)을 명시적으로 사용하는 것이다.
2. 인덱스 미사용 — 암묵적 타입 캐스팅
컬럼 타입이 integer인데 조건값을 문자열로 넘기거나, 함수를 컬럼에 적용하면 인덱스가 무시된다. 예: WHERE LOWER(email) = 'test@example.com'은 email 컬럼 인덱스를 사용하지 못한다. 이 경우 CREATE INDEX ON users (LOWER(email))처럼 함수 인덱스(Expression Index)를 만들어야 한다.
3. 불필요한 SELECT *
필요하지 않은 컬럼까지 전송하면 네트워크 비용과 메모리 사용량이 늘어난다. 특히 TEXT, JSONB, BYTEA 같은 대용량 컬럼이 포함된 테이블에서는 영향이 크다. 실제로 사용하는 컬럼만 명시적으로 지정하면 Index Only Scan 적용 가능성도 높아진다.
4. OFFSET 기반 대용량 페이지네이션
OFFSET 10000 LIMIT 20은 내부적으로 10,020개 행을 읽고 앞의 10,000개를 버린다. 페이지가 뒤로 갈수록 선형으로 느려진다. 해결책은 Keyset Pagination(커서 기반)이다. 마지막으로 조회한 행의 ID나 타임스탬프를 커서로 사용해 WHERE id < $last_id ORDER BY id DESC LIMIT 20 형태로 변경하면 항상 일정한 성능을 유지할 수 있다.
5. 인덱스가 없는 외래 키
PostgreSQL은 외래 키 제약을 선언해도 자동으로 인덱스를 생성하지 않는다. 부모 테이블에서 행을 삭제하거나 업데이트할 때 자식 테이블 전체를 Seq Scan하게 된다. 모든 외래 키 컬럼에는 인덱스를 수동으로 생성해야 한다.
JOIN 최적화와 서브쿼리 리팩토링
JOIN 성능은 옵티마이저가 선택하는 알고리즘과 데이터 분포에 크게 의존한다. 실행 계획에서 확인해야 할 JOIN 전략은 세 가지다.
Nested Loop Join은 외부 테이블의 각 행에 대해 내부 테이블을 반복 탐색한다. 내부 테이블에 인덱스가 있고 결과 행 수가 적을 때 효율적이다. rows가 많아지면 루프 비용이 기하급수적으로 증가한다.
Hash Join은 작은 쪽 테이블을 메모리에 해시 테이블로 올리고, 큰 테이블을 스캔하면서 매칭한다. 대용량 테이블 간 JOIN에서 인덱스가 없을 때 옵티마이저가 선택한다. work_mem이 충분하면 빠르지만, 메모리가 부족하면 디스크로 spill되어 급격히 느려진다.
Merge Join은 두 테이블이 모두 JOIN 키 기준으로 정렬되어 있을 때 사용한다. 정렬 비용이 선행되므로 이미 인덱스로 정렬 순서가 보장된 경우에 유리하다.
CTE vs 서브쿼리 성능
PostgreSQL 12 이전까지는 CTE(WITH 절)가 무조건 최적화 장벽(optimization fence)으로 동작했다. 즉, 옵티마이저가 CTE를 독립적으로 실행한 뒤 결과를 임시 저장해 사용했기 때문에 술어 푸시다운(predicate pushdown)이 적용되지 않았다. PostgreSQL 12부터는 CTE를 인라인 처리할 수 있게 되었다. 하지만 WITH RECURSIVE나 MATERIALIZED 키워드를 사용하면 여전히 장벽으로 동작한다.
성능이 중요한 서브쿼리는 가능하면 일반 JOIN으로 리팩토링하거나, CTE에 NOT MATERIALIZED를 명시해 인라인 최적화를 허용하는 것이 좋다.
LATERAL JOIN 활용
LATERAL JOIN은 서브쿼리 내부에서 바깥 테이블의 컬럼을 참조할 수 있다. "각 사용자의 가장 최근 주문 3건"처럼 상관 관계가 있는 Top-N 쿼리를 깔끔하게 표현할 수 있다. CROSS JOIN LATERAL 또는 JOIN LATERAL ... ON TRUE 형태로 사용한다.
Hash Join과 Nested Loop Join의 실행 비용 차이.
pg_stat_statements로 슬로우 쿼리 찾기
pg_stat_statements는 PostgreSQL의 공식 확장 모듈로, 서버에서 실행된 모든 쿼리의 통계를 누적 집계한다. 슬로우 쿼리를 찾는 가장 신뢰할 수 있는 방법이다.
활성화 방법:
postgresql.conf에 shared_preload_libraries = 'pg_stat_statements' 추가 후 재시작
대상 데이터베이스에서 CREATE EXTENSION pg_stat_statements; 실행
주요 컬럼 설명:
컬럼
의미
활용
mean_exec_time
쿼리 1회 평균 실행 시간 (ms)
단건 느린 쿼리 탐지
calls
누적 호출 횟수
빈도 높은 쿼리 식별
total_exec_time
누적 총 실행 시간 (ms)
DB 전체 부하 기여도 측정
rows
반환된 총 행 수
과도한 결과셋 탐지
shared_blks_hit / read
캐시 히트 / 디스크 읽기 블록 수
I/O 병목 여부 판단
우선순위를 정할 때는 mean_exec_time이 높은 쿼리(단건 비용이 큰 것)와 total_exec_time이 높은 쿼리(빈도×비용이 큰 것)를 구분해서 각각 대응 전략을 세워야 한다. 통계를 초기화하고 싶을 때는 SELECT pg_stat_statements_reset();을 실행한다.
슬로우 쿼리 Top 10 조회
SELECT query,
calls,
round(mean_exec_time::numeric, 2) AS avg_ms,
round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
실무 체크리스트
PostgreSQL 쿼리 성능 최적화 작업을 진행할 때 아래 순서로 점검하면 놓치는 항목 없이 체계적으로 접근할 수 있다.
진단 단계
슬로우 쿼리 로그 또는 pg_stat_statements로 상위 10개 쿼리 식별
EXPLAIN (ANALYZE, BUFFERS)로 실행 계획 확인
추정 rows vs actual rows 괴리 10배 이상 시 ANALYZE 실행
Seq Scan + 대용량 테이블 조합 확인
인덱스 점검
모든 외래 키 컬럼에 인덱스 생성 여부 확인
자주 사용되는 WHERE 조건 컬럼에 인덱스 존재 여부 확인
함수를 컬럼에 적용하는 조건 → Expression Index 적용 검토
사용되지 않는 인덱스 정기 삭제 (pg_stat_user_indexes.idx_scan = 0 확인)
JSONB 컬럼 조회 빈도 높으면 GIN 인덱스 적용
쿼리 리팩토링
SELECT * → 필요 컬럼만 명시
OFFSET 기반 페이지네이션 → Keyset Pagination으로 변경
N+1 패턴 → JOIN 또는 eager loading으로 통합
암묵적 타입 캐스팅 제거 (조건값 타입 명시)
CTE 최적화 장벽 여부 확인 (PostgreSQL 버전별 동작 차이)
설정 최적화
work_mem: Hash Join, Sort 메모리 조정 (세션 레벨에서 먼저 테스트)
effective_cache_size: OS 파일 시스템 캐시 크기 알려주기 (기본값 4GB → 실제 서버 메모리의 50~75%)
random_page_cost: SSD 환경이면 1.1~1.5로 낮춰 인덱스 사용을 유도
자동 VACUUM 설정 확인 — Dead tuple이 과다하면 Seq Scan 비용 증가
운영 주의사항: 대용량 테이블에 인덱스를 추가할 때는 CREATE INDEX CONCURRENTLY를 사용해 테이블 잠금 없이 생성하라. 일반 CREATE INDEX는 쓰기 잠금이 발생한다.
