PostgreSQL 인덱스 최적화 — 느린 쿼리 진단과 인덱스 전략

-- pg_stat_statements 활성화 (postgresql.conf)
shared_preload_libraries = 'pg_stat_statements'

-- 설치
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 상위 10개 느린 쿼리
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

-- 단일 컬럼
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 복합 인덱스 (선택성 높은 것 먼저)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- 부분 인덱스 (조건에 맞는 행만 인덱싱)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 표현식 인덱스
CREATE INDEX idx_users_email_lower ON users (lower(email));

-- 온라인 생성 (잠금 없이)
CREATE INDEX CONCURRENTLY idx_posts_category ON posts (category);

-- B-tree: 기본, 범위 쿼리 (=, <, >, BETWEEN, LIKE 'prefix%')
CREATE INDEX idx_b ON events (created_at);

-- Hash: = 조건만, 빠름
CREATE INDEX idx_h ON sessions USING HASH (session_token);

-- GIN: 배열, JSONB, 전문 검색
CREATE INDEX idx_gin ON posts USING GIN (tags);
CREATE INDEX idx_jsonb ON products USING GIN (metadata jsonb_path_ops);

-- 테이블의 인덱스 목록과 사용 횟수
SELECT
  indexname,
  pg_size_pretty(pg_relation_size(indexname::regclass)) AS size,
  idx_scan AS scans
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

-- 사용되지 않는 인덱스 (제거 검토)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

-- 통계 갱신 (플래너가 올바른 실행계획 선택)
ANALYZE orders;

-- VACUUM + ANALYZE
VACUUM ANALYZE orders;

-- 자동 VACUUM 상태 확인
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';