EXPLAIN — 실행 계획 읽기

16편에서 인덱스를 만들었어도 옵티마이저가 그걸 쓸지 는 별개. EXPLAIN 은 "이 쿼리를 어떻게 실행할 생각인지" 보여줍니다. 17편은 EXPLAIN 출력 트리 읽기, 스캔 종류 5개, 그리고 ANALYZE 로 실제 측정까지.

EXPLAIN — 계획만 (실행 안 함)

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

                            QUERY PLAN
─────────────────────────────────────────────────────────────────
 Index Scan using ix_users_email on users  (cost=0.29..8.30 rows=1 width=80)
   Index Cond: (email = '[email protected]'::text)

읽는 법:

• Index Scan using ix_users_email — 어떤 인덱스를 쓰는지
• cost=0.29..8.30 — (시작 비용)..(전체 비용), 단위는 "디스크 페이지 가져오기 평균 비용"
• rows=1 — 옵티마이저가 예측한 결과 행 수
• width=80 — 한 행 평균 크기 (bytes)

EXPLAIN ANALYZE — 실제로 실행 + 측정

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

                            QUERY PLAN
─────────────────────────────────────────────────────────────────
 Index Scan using ix_users_email on users
   (cost=0.29..8.30 rows=1 width=80)
   (actual time=0.012..0.014 rows=1 loops=1)
   Index Cond: (email = '[email protected]'::text)
 Planning Time: 0.123 ms
 Execution Time: 0.045 ms

스캔 종류 5가지

JOIN 알고리즘 — Nested Loop · Hash · Merge

EXPLAIN ANALYZE
SELECT u.name, o.id
FROM   users u
JOIN   orders o ON o.user_id = u.id
WHERE  u.active = true;

-- 작은 결과 (수십 행)
Nested Loop
  -> Index Scan on users      -- 외부 (적은 행)
  -> Index Scan on orders     -- 각 user 마다 인덱스로 orders 검색

-- 큰 양쪽
Hash Join
  Hash Cond: (o.user_id = u.id)
  -> Seq Scan on orders
  -> Hash
       -> Seq Scan on users

-- 양쪽 미리 정렬돼 있음 (예: PK)
Merge Join
  -> Index Scan on users
  -> Index Scan on orders

예상 vs 실제 — 통계 어긋남 진단

-> Index Scan on big_table
   (cost=0.42..8.50 rows=10 width=80)
   (actual time=0.5..120.0 rows=50000 loops=1)
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   예상 10 행 vs 실제 50000 행 — 통계 어긋남!

옵티마이저는 통계 정보 를 보고 계획을 세웁니다. 통계가 오래되면 잘못된 계획 선택. 해결:

ANALYZE big_table;     -- 통계 재수집
-- autovacuum 이 보통 자동, 큰 INSERT 직후엔 수동

BUFFERS 옵션 — IO 확인

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

-- Buffers: shared hit=120 read=45
--          ^ 메모리에 있던 페이지 / 디스크에서 읽은 페이지
-- read 가 크면 IO 병목, hit 가 크면 캐시 OK

JSON 포맷 — 시각화 도구

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

-- 결과를 https://explain.dalibo.com 같은 도구에 붙여 시각화

흔한 패턴 분석

① WHERE 에 함수 — 인덱스 무력화

-- ❌ 함수가 컬럼에 적용되면 인덱스 못 씀
SELECT * FROM users WHERE lower(email) = '[email protected]';
-- → Seq Scan

-- ✅ 표현식 인덱스 (16편)
CREATE INDEX ix_users_email_lower ON users ((lower(email)));
-- 또는 데이터 자체를 lowercase 로 저장

② 형 변환 — 인덱스 무력화

-- id 가 bigint 인데 문자열 비교
SELECT * FROM users WHERE id = '5';   -- 암묵적 cast → Seq Scan 위험

-- 명시적
SELECT * FROM users WHERE id = 5;     -- 또는 $1::bigint

③ LIKE 앞 와일드카드

-- B-Tree 못 씀
SELECT * FROM users WHERE name LIKE '%준성';

-- 16편의 pg_trgm GIN 인덱스 필요

④ OR 여러 컬럼

SELECT * FROM users WHERE name = $1 OR email = $1;
-- 보통 둘 다 Seq Scan

-- 분리해서 UNION (각각 인덱스 사용)
SELECT * FROM users WHERE name = $1
UNION
SELECT * FROM users WHERE email = $1;

pg_stat_statements — 느린 쿼리 자동 추적

CREATE EXTENSION pg_stat_statements;

-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- (재시작 필요)

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

운영 환경에서는 이게 가장 강력한 도구. "총 시간 가장 많이 잡아먹는 쿼리 10개" 를 자동으로 알려줍니다.

한 줄 진단 체크리스트

• Seq Scan 인데 큰 테이블 → 인덱스 부재 또는 통계 어긋남
• 예상 vs 실제 행 수 10배 이상 차이 → ANALYZE 필요
• BUFFERS read 가 큼 → IO 병목, shared_buffers 부족 가능
• cost 작은데 actual time 큼 → 디스크 IO 또는 락 대기
• Nested Loop + 큰 외부 → Hash Join 으로 안 풀린 이유 확인