PostgreSQL 심화 — EXPLAIN · 인덱스

느린 SELECT의 원인은 인덱스뿐 아니라 잘못된 조인, 오래된 통계, 과도한 반환량, 잠금, 디스크 읽기일 수 있습니다. EXPLAIN은 추측을 실행 경로로 바꾸는 출발점입니다.

측정에서 운영 반영까지

대표 쿼리

실제 조건·정렬·LIMIT을 선택합니다.

실행 계획

읽은 행·블록과 sort를 확인합니다.

후보 검증

staging에서 새 인덱스의 쓰기 비용과 개선 폭을 잽니다.

운영 반영

멱등 적용 뒤 카탈로그 drift 0을 확인합니다.

1. EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;

출력:

Limit  (cost=0.42..8.44 rows=20) (actual time=0.023..0.187 rows=20 loops=1)
  ->  Index Scan using idx_posts_user_created on posts
        (cost=0.42..812.04 rows=2030) (actual time=0.023..0.183 rows=20)
        Index Cond: (user_id = 123)

Index Scan — 조건에 맞는 키 범위에서 행을 찾음
Index Only Scan — 필요한 값이 인덱스에 있고 visibility map 조건도 맞아 heap 접근을 줄임
Bitmap Index Scan — 여러 후보를 모아 heap을 묶어 읽을 때 유리할 수 있음
Seq Scan — 전체 범위를 읽음. 작은 테이블이나 많은 행을 반환할 때는 올바른 선택일 수 있음
actual time — 실제 실행 시간

EXPLAIN ANALYZE는 쿼리를 실제 실행합니다. 쓰기 쿼리나 운영 데이터에는 먼저 트랜잭션·복제 환경·롤백 가능성을 확인하세요.

2. 인덱스가 동작하지 않는 패턴

-- 함수 감싸기
WHERE LOWER(email) = 'a@b.c'         -- idx_email 무용
-- → 함수형 인덱스 필요: CREATE INDEX ON users (LOWER(email))

-- 앞부분 와일드카드
WHERE name ILIKE '%kim%'             -- btree 무용
-- → pg_trgm GIN 인덱스

-- 타입 변환
WHERE user_id::text = '123'          -- 인덱스 무용
-- → 타입 맞추기

-- OR
WHERE a = 1 OR b = 2                 -- 각 컬럼 따로 인덱스여도 비효율
-- → UNION 으로 분해 또는 복합 인덱스

3. 복합 인덱스 — 컬럼 순서

CREATE INDEX ON posts (user_id, created_at DESC);

왼쪽부터 매칭 가능:

WHERE user_id = 1                         -- ✓ 사용
WHERE user_id = 1 AND created_at > ...    -- ✓ 사용
WHERE created_at > ...                    -- ✗ 사용 안 됨

보통 반복되는 등식 조건을 앞에 두고 그 뒤에 범위·정렬 컬럼을 둡니다. “선택도가 높은 컬럼부터”라는 규칙만으로 정하지 말고 실제 WHERE와 ORDER BY를 한 세트로 봅니다.

LIMIT이 있어도 정렬 비용은 사라지지 않는다

SELECT id, name, district
FROM facilities
WHERE region_code = $1 AND type_code = $2
ORDER BY district NULLS LAST, name
LIMIT 100;

region_code 인덱스만 있으면 PostgreSQL은 조건에 맞는 수천 행을 읽고 top-N heapsort한 뒤 100행만 반환할 수 있습니다. 다음 인덱스는 등식 조건 뒤에 사용자 정렬을 이어서 첫 100행에서 멈출 수 있게 합니다.

CREATE INDEX idx_facilities_region_type_list
ON facilities (region_code, type_code, district, name);

유형 없는 목록도 자주 쓰면 (region_code, district, name)이 별도로 필요합니다. 중간의 type_code를 건너뛴 인덱스가 같은 정렬 순서를 보장하지 않기 때문입니다. 새 계획이 Index Scan에서 Limit까지 sort 없이 이어지는지 확인한 뒤, 실제 조회가 없고 새 복합 인덱스에 포괄되는 단독 type_code 인덱스만 제거합니다.

4. Covering Index

CREATE INDEX ON posts (user_id, created_at DESC) INCLUDE (title);

INCLUDE 로 컬럼 추가. 쿼리가 인덱스만으로 답변 가능 (Index Only Scan).

5. 부분 인덱스

CREATE INDEX idx_published_posts ON posts (created_at DESC)
WHERE published = true;

전체 행 중 published = true가 적고 대표 쿼리도 같은 조건을 명시할 때 인덱스가 작아집니다. 반대로 95%가 true라면 대부분의 행을 담으므로 크기 이점이 작습니다.

부분 인덱스의 조건은 쿼리가 논리적으로 함의해야 합니다. 다음 쿼리는 위 인덱스를 사용할 후보지만, published 조건이 없거나 매개변수 형태 때문에 플래너가 함의를 증명하지 못하면 선택되지 않을 수 있습니다.

SELECT id, title
FROM posts
WHERE published = true
ORDER BY created_at DESC
LIMIT 20;

읽기 경로	적합한 인덱스	확인할 비용
사용자별 최신 목록	`(user_id, created_at DESC)`	페이지 깊이와 반환 컬럼
미처리 작업 대기열	`(created_at, id) WHERE processed_at IS NULL`	claim 경합과 재시도
상태별 최신 목록	`(status, created_at DESC)`	상태 분포와 쓰기 증폭

6. GIN / GiST — 특수 인덱스

인덱스	용도
btree	기본 (등식 · 범위 · 정렬)
hash	등식 전용 (btree 로 대체 권장)
GIN	배열 · JSONB · 전문검색 (tsvector) · trigram
GiST	지리 (PostGIS) · 범위
BRIN	시계열 거대 테이블 (블록 범위)

CREATE INDEX ON posts USING gin (tags);            -- text[]
CREATE INDEX ON posts USING gin (content gin_trgm_ops);  -- 부분 일치
CREATE INDEX ON posts USING gin (metadata);        -- JSONB

7. 통계 · ANALYZE

ANALYZE posts;

PostgreSQL 이 쿼리 계획 세우는 기반. 대량 변경 후 수동 실행 권장. autovacuum 이 자동 처리하지만 지연될 수 있음.

8. 느린 쿼리 로그

postgresql.conf:

log_min_duration_statement = 1000     # 1초 이상 로그

실제로 어떤 쿼리가 느린지 측정 없이 추측 금지.

9. 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;

누적 가장 느린 쿼리 TOP 20. 최적화 1 순위.

10. 자주 걸리는 자리

인덱스 많이 만들면 쓰기 느림 — 각 INSERT/UPDATE 가 모든 인덱스 갱신
UUID 컬럼 btree — 랜덤이라 insert 시 페이지 조각. UUID v7 (시계열) 고려
NULL 값 많은 컬럼 인덱스 — 의미 있으면 부분 인덱스
VACUUM 안 하면 — 통계 낡아 잘못된 계획 · dead tuple

11. 운영에 안전하게 반영하기

실제 쿼리의 조건·정렬·LIMIT을 그대로 복사합니다.
대표 데이터가 있는 staging에서 EXPLAIN (ANALYZE, BUFFERS) 전후를 비교합니다.
새 인덱스를 먼저 만들고 읽기 계획을 확인한 뒤에만 포괄되는 중복 인덱스를 제거합니다.
외래키 선두 인덱스는 사용자 조회에 안 보여도 부모 삭제·갱신 검증에 필요할 수 있습니다.
운영 적용과 CREATE 정의를 같은 최종 상태로 맞추고 카탈로그 drift가 0인지 확인합니다.
작은 빈 테이블의 플래너 선택만으로 결론 내리지 않습니다. 통계와 분포가 달라지면 계획도 달라집니다.

하고픈 말

“대표 쿼리 → 실행 계획 → 읽은 블록과 행 수 → 쓰기 비용 → drift 검증”을 한 묶음으로 반복하는 것이 운영 DB 튜닝의 기본기입니다.

02-multi-pool-orchestration