PostgreSQL 인덱스 튜닝 패턴 2026

-- pg_stat_statements 확장 활성화 (postgresql.conf 또는 ALTER SYSTEM)
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- 변경 후 PostgreSQL 재시작 필요

-- 상위 20개 느린 쿼리: 총 실행시간 기준 정렬
SELECT
    queryid,
    substring(query, 1, 80) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((stddev_exec_time)::numeric, 2) AS stddev_ms,
    rows,
    round((shared_blks_hit * 100.0 /
        NULLIF(shared_blks_hit + shared_blks_read, 0))::numeric, 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 컬럼별 선택도 확인
SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('status', 'user_id', 'created_at');

-- 결과 예시:
-- status     | n_distinct = 5     (선택도 낮음 - 뒤에 배치)
-- user_id    | n_distinct = 50000 (선택도 높음 - 앞에 배치)
-- created_at | n_distinct = -0.95 (거의 유일 - 범위 검색이면 마지막)

-- 패턴 1: user_id 등호 + created_at 범위 (가장 흔한 OLTP 패턴)
CREATE INDEX CONCURRENTLY idx_orders_user_created
    ON orders (user_id, created_at DESC);

-- 패턴 2: status 필터 + user_id 검색 (status 카디널리티가 낮을 때)
-- Partial index가 복합 인덱스보다 효율적
CREATE INDEX CONCURRENTLY idx_orders_pending_user
    ON orders (user_id)
    WHERE status = 'pending';

-- 패턴 3: JSONB 필드 내부 검색
CREATE INDEX CONCURRENTLY idx_orders_metadata
    ON orders USING gin (metadata jsonb_path_ops);

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.user_id = 42
  AND o.created_at >= '2026-02-01'
  AND o.created_at < '2026-03-01'
ORDER BY o.created_at DESC
LIMIT 50;

Limit  (cost=0.56..125.43 rows=50 width=52)
       (actual time=0.089..0.342 rows=50 loops=1)
  ->  Nested Loop  (cost=0.56..4521.12 rows=1812 width=52)
                   (actual time=0.087..0.334 rows=50 loops=1)
        ->  Index Scan using idx_orders_user_created on orders o
              (cost=0.43..3890.21 rows=1812 width=28)
              (actual time=0.071..0.205 rows=50 loops=1)
              Index Cond: ((user_id = 42) AND (created_at >= ...))
              Buffers: shared hit=12
        ->  Index Scan using users_pkey on users u
              (cost=0.13..0.35 rows=1 width=24)
              (actual time=0.002..0.002 rows=1 loops=50)
              Index Cond: (id = o.user_id)
              Buffers: shared hit=100
Planning Time: 0.245 ms
Execution Time: 0.398 ms

-- 나쁜 예: 전체 인덱스 (1억 건 모두 인덱싱)
CREATE INDEX idx_orders_status ON orders (status, created_at);
-- 인덱스 크기: ~2.1GB

-- 좋은 예: Partial index (10만 건만 인덱싱)
CREATE INDEX CONCURRENTLY idx_orders_pending
    ON orders (created_at DESC)
    WHERE status = 'pending';
-- 인덱스 크기: ~2.4MB (875배 작음)

-- user_id로 검색하고 email, total_amount를 반환하는 쿼리가 빈번할 때
CREATE INDEX CONCURRENTLY idx_orders_user_covering
    ON orders (user_id, created_at DESC)
    INCLUDE (total_amount, status);

-- 이 인덱스로 Index Only Scan이 가능해진다
-- 단, visibility map이 갱신되어야 하므로 VACUUM이 중요

-- hypopg 설치
CREATE EXTENSION IF NOT EXISTS hypopg;

-- 가상 인덱스 생성
SELECT * FROM hypopg_create_index(
    'CREATE INDEX ON orders (user_id, created_at DESC) INCLUDE (total_amount)'
);
-- 결과: indexrelid = 14356, indexname = '<14356>btree_orders_user_id_created_at'

-- 가상 인덱스가 적용된 EXPLAIN 확인
EXPLAIN SELECT user_id, created_at, total_amount
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-02-01'
ORDER BY created_at DESC;
-- Index Only Scan using <14356>btree_orders_user_id_created_at

-- 가상 인덱스 제거
SELECT hypopg_drop_index(14356);

-- 모든 가상 인덱스 한번에 정리
SELECT hypopg_reset();

-- 30일 이상 사용되지 않은 인덱스 탐지
-- (pg_stat_user_indexes는 마지막 통계 초기화 이후 누적)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS scan_count,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
      SELECT indexrelid FROM pg_index WHERE indisunique
  )
ORDER BY pg_relation_size(indexrelid) DESC;

-- 인덱스 삭제 전 반드시 CONCURRENTLY로 시도
-- (일반 DROP INDEX는 테이블에 ACCESS EXCLUSIVE 잠금을 건다)
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_old_status;

-- 테이블별 dead tuple 비율과 마지막 vacuum 시점 확인
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2)
        AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- 대용량 테이블에 대한 autovacuum 개별 튜닝
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,    -- 기본값 0.2 대비 20배 민감
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005,
    autovacuum_vacuum_cost_delay = 2          -- 기본 2ms, 0으로 하면 최대 속도
);

-- pgstattuple 확장으로 인덱스 bloat 측정
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    indexrelid::regclass AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    round(100 - (avg_leaf_density)::numeric, 2) AS bloat_pct
FROM pg_stat_user_indexes,
     LATERAL pgstatindex(indexrelid::regclass::text) AS s
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024  -- 100MB 이상만
ORDER BY bloat_pct DESC
LIMIT 10;

-- PostgreSQL 14+ : REINDEX CONCURRENTLY (서비스 중단 없이)
REINDEX INDEX CONCURRENTLY idx_orders_user_created;

-- PostgreSQL 12+ : 전체 테이블의 인덱스를 한번에
REINDEX TABLE CONCURRENTLY orders;

-- PostgreSQL 17에서 효과가 커진 설정
ALTER SYSTEM SET effective_io_concurrency = 200;    -- NVMe SSD라면 200
ALTER SYSTEM SET maintenance_io_concurrency = 100;  -- VACUUM/CREATE INDEX용
ALTER SYSTEM SET huge_pages = try;                  -- 대용량 shared_buffers 시 TLB miss 감소
SELECT pg_reload_conf();

-- 문제 쿼리
EXPLAIN SELECT * FROM orders WHERE created_at::date = '2026-03-01';
-- 결과: Seq Scan on orders (함수 적용으로 인덱스 무력화)

-- 해결: 함수 사용 제거, 범위 조건으로 변경
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2026-03-01'
  AND created_at < '2026-03-02';
-- 결과: Index Scan using idx_orders_created_at

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890;
        blocked by process 23456.

-- long-running transaction 확인
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;

-- 필요시 해당 세션 종료
SELECT pg_terminate_backend(12345);

-- 실패한 INVALID 인덱스 정리
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY idx_orders_failed;

# 업그레이드 직후 전체 데이터베이스 통계 재수집
vacuumdb --analyze-in-stages --all --jobs=4

# 1단계: 최소 통계 수집 (default_statistics_target = 1)
# 2단계: 기본 통계 수집 (default_statistics_target = 10)
# 3단계: 전체 통계 수집 (default_statistics_target = 현재 설정값)