blog

Postgres SQL - 실무에서 쓰기 좋은 실행계획 분석 및 활용법

날짜: 2025-08-23

목록으로

예제 스키마 (공통)

-- 주문/고객 간단 스키마
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status TEXT NOT NULL,                 -- 'paid', 'refund', ...
  total_amount NUMERIC(12,2) NOT NULL,  -- 합계 금액
  ordered_at TIMESTAMPTZ NOT NULL
);

-- 인덱스
CREATE INDEX idx_orders__user_id ON orders(user_id);
CREATE INDEX idx_orders__status__ordered_at ON orders(status, ordered_at DESC);
CREATE INDEX idx_orders__ordered_at ON orders(ordered_at DESC);

1) 들어가며: 실행계획이 왜 중요한가

2) EXPLAIN / EXPLAIN ANALYZE 기본기

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE status='paid' ORDER BY ordered_at DESC LIMIT 20;

포인트

3) 자주 등장하는 노드 빠르게 읽기

4) 실무 사례별 분석

4-1) WHERE 조건 최적화

문제 쿼리:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123 AND status='paid';

개선 팁:

CREATE INDEX idx_orders__user_id__status ON orders(user_id, status);

체크포인트:

4-2) ORDER BY/LIMIT 최적화

문제 쿼리:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at FROM orders
WHERE status='paid'
ORDER BY ordered_at DESC
LIMIT 20;

개선 인덱스(커버링 지향):

-- 정렬열이 선두가 되도록 설계하면 Top-N 빠름
CREATE INDEX idx_orders__status__ordered_at__cover
  ON orders(status, ordered_at DESC, id);

효과:

4-3) JOIN 순서/방법

문제 쿼리:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status='paid' AND o.ordered_at >= now() - interval '7 days';

체크:

-- 조인에 자주 쓰이는 조건 인덱스 강화
CREATE INDEX idx_orders__status__ordered_at__user_id
  ON orders(status, ordered_at DESC, user_id);

효과:

4-4) GROUP BY/COUNT

문제 쿼리:

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', ordered_at) d, count(*)
FROM orders
WHERE status='paid'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 7;

개선 후보:

5) 실행계획에서 꼭 볼 지표

6) 자주 하는 실수

7) 실무 최적화 전략 체크리스트

8) 도구 활용

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- postgresql.conf 예시
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '200ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

9) 마무리: 루틴화 팁

  1. pg_stat_statements로 후보 찾기 →
  2. EXPLAIN (ANALYZE, BUFFERS)로 병목 노드 확인 →
  3. 인덱스/쿼리/통계 순으로 개선 →
  4. 전/후 수치(시간, Buffers, Rows) 기록 및 공유.

부록) 미니 튜닝 전/후 예시

전: 느린 Top-N

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at 
FROM orders 
WHERE status='paid'
ORDER BY ordered_at DESC
LIMIT 20;
/*
Sort (cost: 높음) -> Seq Scan orders
Actual ... Buffers: read 다수, time 수백 ms
*/

후: 커버링 인덱스 도입

CREATE INDEX CONCURRENTLY idx_orders__status__ordered_at__cover
  ON orders(status, ordered_at DESC, id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, ordered_at 
FROM orders 
WHERE status='paid'
ORDER BY ordered_at DESC
LIMIT 20;
/*
Index Only Scan using idx_orders__status__ordered_at__cover
Actual time: 수 ms, Buffers hit 위주, Sort 제거
*/

실무 점검 체크리스트 (요약)

CBO(Cost Based Optimizer)가 Cost를 계산하는 기준

🔎 CBO가 Cost를 판단하는 핵심 개념

Postgres는 쿼리를 실행할 때 여러 실행계획 후보를 만들어두고, 각각의 비용(Cost)을 계산한 후 가장 저렴한 플랜을 고릅니다. 여기서 Cost는 절대값이 아니라 상대값이에요. 즉, “플랜 A가 B보다 몇 배 빠르다” 정도로만 쓰입니다.

1. Cost 판단 기준 (실무 포인트)

(1) 통계 정보 (Statistics)

(2) 플래너 비용 파라미터 (GUC 설정)

Postgres는 CPU와 I/O 비용을 아래 값으로 단순화해서 계산합니다:

👉 실무 팁:

(3) 행 개수 추정 (Cardinality)

👉 실무 팁:

2. Cost 계산 과정 (간단하게)

플랜 노드별로 다음을 합산합니다:

(읽어야 할 페이지 수 × 페이지 비용)
+ (처리할 튜플 수 × CPU 비용)
+ (정렬/해시/병렬 등 추가 비용)

모든 후보 플랜 중 total cost가 가장 낮은 것 선택.

3. 실무 적용 체크리스트 ✅

  1. 통계 최신화

    • 대규모 DML 후 ANALYZE → 쿼리 성능 급변 방지.

  2. 환경 반영한 비용 조정

    • SSD라면 random_page_cost = 1.1~1.5
    • 메모리 크기에 맞게 effective_cache_size 튜닝.

  3. 실행계획 검증 루틴

    • EXPLAIN (ANALYZE, BUFFERS)로 예상 vs 실제 rows 오차 확인.
    • rows 예측 실패 → CREATE STATISTICS (다중 컬럼 상관, distinct 값 등) 고려.

  4. Prepared Statement 주의

    • 반복 쿼리는 PREPARE로 빠르게.
    • 단, 조건 값에 따라 rows 편차가 큰 쿼리는 custom plan 강제 필요할 수도 있음 (plan_cache_mode=force_custom_plan).

🔑 요약

목록으로