PostgreSQL 심화 — EXPLAIN · 인덱스

SELECT 느린 쿼리는 대부분 인덱스 문제. EXPLAIN 한 줄만 읽어도 50% 는 해결.

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 — 인덱스 사용 ✓
Seq Scan — 전체 테이블 스캔 ✗ (큰 테이블에서 느림)
actual time — 실제 실행 시간

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 > ...                    -- ✗ 사용 안 됨

선행 컬럼 순서 = 선택도 높은 · 등식 쿼리.

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;

95% 가 published = true 면 부분 인덱스가 작고 빠름.

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

하고픈 말

"느린 쿼리 → EXPLAIN → 인덱스 확인" 세 단계 반복이 DB 튜닝의 기본기. 추측보다 측정.

02-multi-pool-orchestration