튜닝 검증: before/after 측정, plan regression, 부작용 확인

튜닝이 끝난 게 아니라 검증이 시작된 것이다

인덱스를 추가하거나 쿼리를 재작성하면 성능이 나아졌다는 느낌이 든다. 그런데 배포 후 다른 쿼리가 오히려 느려지거나, 쓰기 처리량이 줄어들거나, 며칠 후 옵티마이저가 다른 플랜을 선택하는 일이 생긴다. 튜닝 작업은 적용 시점이 아니라 검증이 완료될 때 끝난다.

좋은 검증은 세 가지 질문에 답한다.

1. Before/After: 목표 쿼리가 실제로 빨라졌는가? 얼마나?
2. Plan regression: 변경이 다른 쿼리의 실행계획을 바꾸지 않았는가?
3. 부작용: 쓰기 성능, 잠금 동작, 통계 갱신 등에 예상치 못한 영향이 없는가?

Before/After 측정 방법론

기준선(baseline) 확보

변경 전에 반드시 기준선을 찍어둔다. 기억이나 로그 파편에 의존하면 비교가 부정확하다.

PostgreSQL — pg_stat_statements 초기화 후 측정

-- 변경 전: 통계 초기화 (슈퍼유저 권한 또는 pg_stat_statements_reset 권한 필요)
SELECT pg_stat_statements_reset();

-- 대표 워크로드를 실행하거나 트래픽을 일정 시간(15~30분) 흘린다.

-- 기준선 스냅샷 저장
CREATE TABLE perf_baseline AS
SELECT
    queryid,
    left(query, 200)              AS query_sample,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    rows,
    shared_blks_hit,
    shared_blks_read
FROM pg_stat_statements
WHERE calls > 10
ORDER BY total_exec_time DESC
LIMIT 100;

MySQL — Performance Schema 통계 초기화

-- 변경 전: 특정 쿼리 요약 초기화
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- 또는 전체 초기화 (영향도 큰 시스템에서는 신중하게)
-- CALL sys.ps_truncate_all_tables(FALSE);

-- 기준선 스냅샷 저장
CREATE TABLE perf_baseline AS
SELECT
    digest,
    LEFT(digest_text, 200)        AS query_sample,
    count_star                    AS calls,
    round(avg_timer_wait/1000000, 2) AS avg_ms,
    round(sum_timer_wait/1000000000, 2) AS total_sec,
    sum_rows_examined,
    sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE count_star > 10
ORDER BY sum_timer_wait DESC
LIMIT 100;

변경 적용 후 비교

-- 변경 적용 (인덱스 생성, 쿼리 수정 등)
-- 동일한 워크로드를 동일한 기간 실행

-- PostgreSQL: 변경 후 비교
SELECT
    n.left_query                  AS query_sample,
    b.mean_ms                     AS before_mean_ms,
    round(n.mean_exec_time::numeric, 2) AS after_mean_ms,
    round(n.mean_exec_time::numeric, 2) - b.mean_ms AS delta_ms,
    round((n.mean_exec_time - b.mean_ms) / nullif(b.mean_ms, 0) * 100, 1) AS pct_change
FROM pg_stat_statements n
JOIN perf_baseline b ON n.queryid = b.queryid
WHERE ABS(n.mean_exec_time - b.mean_ms) > 5  -- 5ms 이상 차이
ORDER BY pct_change DESC;

pct_change가 음수면 개선, 양수면 regression이다.

EXPLAIN ANALYZE로 플랜 검증

통계 수치와 함께 실행계획의 구조가 실제로 바뀌었는지 확인한다.

-- PostgreSQL: 상세 옵션 활용
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders
WHERE customer_id = 42
  AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 10;

확인 포인트:

-- MySQL: EXPLAIN ANALYZE (8.0.18+)
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 42
  AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 10;

Plan Regression 탐지

Regression이란

튜닝 전에는 빨랐던 쿼리가 변경 후 느려지는 현상이다. 직접적인 원인보다 간접 원인이 더 흔하다.

• 새 인덱스가 생기면 옵티마이저가 기존 쿼리에도 그 인덱스를 쓰려고 시도한다.
• 통계 갱신(ANALYZE)이 cardinality 추정치를 바꾸어 플랜이 바뀐다.
• 인덱스 추가 후 shared_buffers 사용 패턴이 달라져 다른 쿼리의 캐시 히트율이 떨어진다.

pg_stat_statements로 퇴행 쿼리 탐지

-- 기준선과 비교해 평균 응답시간이 20% 이상 오른 쿼리
SELECT
    n.queryid,
    left(n.query, 160)             AS query_sample,
    b.mean_ms                      AS before_ms,
    round(n.mean_exec_time::numeric, 2) AS after_ms,
    round((n.mean_exec_time - b.mean_ms) / b.mean_ms * 100, 1) AS pct_worse
FROM pg_stat_statements n
JOIN perf_baseline b ON n.queryid = b.queryid
WHERE n.mean_exec_time > b.mean_ms * 1.20   -- 20% 이상 악화
  AND b.mean_ms > 1                          -- 원래 1ms 이상인 쿼리만
ORDER BY pct_worse DESC;

auto_explain으로 플랜 변화 포착

pg_stat_statements는 통계만 수집하고 플랜은 담지 않는다. 플랜이 실제로 바뀌었는지 확인하려면 auto_explain을 활성화한다.

-- postgresql.conf (변경 후 reload)
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = 100   -- 100ms 이상 쿼리의 플랜을 로그로 남김
-- auto_explain.log_analyze = on
-- auto_explain.log_buffers = on

-- 또는 세션 수준으로 임시 활성화 (reload 불필요)
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;
SET auto_explain.log_analyze = on;

로그에서 같은 쿼리의 Before/After 플랜을 비교하면 Seq Scan → Index Scan 또는 Hash Join → Nested Loop 같은 전환이 명확하게 보인다.

MySQL: Performance Schema 비교

-- 변경 후 top 쿼리와 기준선 비교
SELECT
    n.digest,
    LEFT(n.digest_text, 160)       AS query_sample,
    b.avg_ms                       AS before_ms,
    round(n.avg_timer_wait/1000000, 2) AS after_ms,
    round((n.avg_timer_wait/1000000 - b.avg_ms) / b.avg_ms * 100, 1) AS pct_change
FROM performance_schema.events_statements_summary_by_digest n
JOIN perf_baseline b ON n.digest = b.digest
WHERE n.avg_timer_wait/1000000 > b.avg_ms * 1.20
ORDER BY pct_change DESC;

부작용 확인 항목

1. 쓰기 오버헤드

인덱스는 읽기를 빠르게 하지만 쓰기는 느리게 만든다. 인덱스를 추가할 때마다 INSERT/UPDATE/DELETE 시 인덱스 갱신 비용이 발생한다.

-- MySQL: 인덱스별 쓰기 I/O 확인
SELECT
    index_name,
    count_read,
    count_write,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
  AND object_name = 'orders'
ORDER BY count_write DESC;

-- PostgreSQL: 테이블 쓰기 지연 간접 확인
SELECT
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

인덱스 추가 후 쓰기 집중 테이블에서 처리량(rows/s)이 눈에 띄게 줄었다면, 해당 인덱스의 실제 사용 빈도를 확인한다.

-- PostgreSQL: 인덱스 사용 통계 (사용 안 되는 인덱스 탐지)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders'
ORDER BY idx_scan ASC;

idx_scan = 0이거나 극히 낮은 인덱스는 쓰기 비용만 높이고 있을 가능성이 있다.

2. 통계 갱신 타이밍

인덱스 생성 후 바로 ANALYZE가 실행되거나 autovacuum/auto-analyze가 트리거될 수 있다. 이 시점에 통계가 대폭 변경되면 여러 쿼리의 플랜이 한꺼번에 바뀐다.

-- PostgreSQL: 마지막 통계 갱신 시각 확인
SELECT
    relname,
    last_analyze,
    last_autoanalyze,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'customers', 'products');

통계 갱신 시각과 성능 변화 시각이 겹친다면 통계 갱신이 플랜을 바꾼 원인일 수 있다.

3. DDL 잠금

인덱스 생성은 테이블에 잠금을 건다. MySQL에서 Online DDL 없이 큰 테이블에 인덱스를 추가하면 서비스 중단이 생긴다.

-- MySQL: ALGORITHM=INPLACE, LOCK=NONE (Online DDL)
ALTER TABLE orders
ADD INDEX idx_customer_status (customer_id, status),
ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL: CONCURRENTLY 옵션 (테이블 잠금 없이 인덱스 생성)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status);
-- 단: 실패 시 INVALID 인덱스가 남으므로 명시적으로 DROP해야 함

대규모 배포에서 인덱스 생성은 배포 전날 밤 낮은 트래픽 시간대에 미리 실행하거나, 별도의 마이그레이션 단계로 분리한다.

4. 플랜 강제(Plan Hint / Force)

옵티마이저가 잘못된 플랜을 계속 선택한다면 임시로 플랜을 강제할 수 있다.

-- MySQL: 인덱스 힌트로 특정 인덱스 강제
SELECT *
FROM orders FORCE INDEX (idx_customer_status)
WHERE customer_id = 42 AND status = 'PENDING';

-- PostgreSQL: 플래너 파라미터로 특정 노드 비활성화
SET enable_seqscan = off;   -- Index Scan을 선호하도록 강제
EXPLAIN ANALYZE SELECT ...;
RESET enable_seqscan;

힌트나 플래너 파라미터는 임시 진단 수단이다. 통계를 최신 상태로 유지하거나 쿼리/인덱스를 재설계하는 게 올바른 해결책이다.

검증 체크리스트

실전 시나리오: 인덱스 추가 후 이웃 쿼리 퇴행

-- 목표 쿼리 개선을 위해 인덱스 추가
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC);

-- 목표 쿼리: 빨라짐 ✓
SELECT id, amount FROM orders WHERE status = 'PENDING' ORDER BY created_at DESC LIMIT 20;
-- Before: 450ms → After: 12ms

-- 예상치 못한 퇴행 발견 ✗
-- 이 쿼리가 Hash Join → Nested Loop로 전환되어 느려짐
SELECT o.id, c.name, o.amount
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'SHIPPED' AND o.created_at > NOW() - INTERVAL '7 day';
-- Before: 80ms → After: 340ms

원인 분석:

EXPLAIN ANALYZE
SELECT o.id, c.name, o.amount
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'SHIPPED' AND o.created_at > NOW() - INTERVAL '7 day';

-- After 플랜에서 발견:
-- -> Index Scan using idx_orders_status_created on orders
--      (cost=0.43..12400.55 rows=45000 ...)
--      (actual time=0.08..290.1 rows=85000 ...)
-- 새 인덱스가 o.status = 'SHIPPED' 조건에도 선택됐지만,
-- 실제 rows가 추정의 2배 → NL join에서 85,000번 반복 → 퇴행

대응:

-- 방법 1: 통계 갱신으로 cardinality 재추정
ANALYZE orders;

-- 방법 2: 퇴행 쿼리에 힌트로 원래 플랜 강제 (임시)
SET enable_indexscan = off;
EXPLAIN ANALYZE SELECT ...; -- Hash Join 복원 확인
RESET enable_indexscan;

-- 방법 3: 퇴행 쿼리에 맞는 별도 인덱스 추가
CREATE INDEX CONCURRENTLY idx_orders_shipped_created
ON orders (created_at DESC) WHERE status = 'SHIPPED';
-- partial index로 SHIPPED만 커버 → 새 인덱스와 충돌 없음

References

• PostgreSQL Documentation 18, "Using EXPLAIN" — https://www.postgresql.org/docs/current/using-explain.html
• PostgreSQL Documentation 18, "auto_explain" — https://www.postgresql.org/docs/current/auto-explain.html
• PostgreSQL Documentation 18, "pg_stat_statements" — https://www.postgresql.org/docs/current/pgstatstatements.html
• MySQL 8.4 Reference Manual, "Performance Schema Statement Event Tables" — https://dev.mysql.com/doc/refman/8.4/en/performance-schema-statement-event-tables.html
• Philip McClarence, "Catching Query Plan Regressions Before They Become Incidents" — https://medium.com/@philmcc/catching-query-plan-regressions-before-they-become-incidents-5645eb256583
• Microsoft Research, "Understanding and Detecting Query Performance Regression in Practical Index Tuning" — https://www.microsoft.com/en-us/research/publication/understanding-and-detecting-query-performance-regression-in-practical-index-tuning/
• EnterpriseDB Blog, "PostgreSQL Query Optimization Performance Tuning with EXPLAIN ANALYZE" — https://www.enterprisedb.com/blog/postgresql-query-optimization-performance-tuning-with-explain-analyze
• Severalnines, "Query observability and performance tuning with pg_stat_monitor and pg_stat_statements" — https://severalnines.com/blog/query-observability-and-performance-tuning-with-pg_stat_monitor-and-pg_stat_statements/
• DEV Community (moh_moh701), "Query Store Part 2: Regression Detection and Execution Plan Analysis" — https://dev.to/moh_moh701/-query-store-part-2-regression-detection-and-execution-plan-analysis-a-guide-e9d