쿼리 옵티마이저

-- 통계 정보 갱신 (옵티마이저에 최신 정보 제공)
-- MySQL
ANALYZE TABLE orders;
ANALYZE TABLE customers;

-- PostgreSQL
ANALYZE orders;
ANALYZE customers;

-- 옵티마이저가 선택한 실행 계획 확인
EXPLAIN (ANALYZE, COSTS, FORMAT TEXT)
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > '2024-01-01'
GROUP BY c.id, c.name;

-- 옵티마이저 힌트 사용 (MySQL)
SELECT /*+ JOIN_ORDER(c, o) INDEX(o idx_order_date) */
    c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > '2024-01-01'
GROUP BY c.id, c.name;

-- PostgreSQL 힌트 (pg_hint_plan 확장)
/*+ Leading(c o) IndexScan(o idx_order_date) */
SELECT c.name, COUNT(o.id)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > '2024-01-01'
GROUP BY c.id, c.name;

-- 옵티마이저 통계 확인
-- MySQL
SHOW TABLE STATUS LIKE 'orders';
SHOW INDEX FROM orders;

-- PostgreSQL 통계 확인
SELECT
    tablename,
    n_live_tup,
    n_dead_tup,
    last_analyze
FROM pg_stat_user_tables
WHERE tablename = 'orders';

-- 컬럼별 통계 분포 확인
SELECT * FROM pg_stats
WHERE tablename = 'orders' AND attname = 'order_date';