23강: 인덱스와 쿼리 실행 계획

-- 자주 사용하는 쿼리의 실행 계획 확인
EXPLAIN QUERY PLAN
SELECT order_number, total_amount
FROM orders
WHERE customer_id = 42;

-- 자주 사용하는 쿼리의 실행 계획 확인
EXPLAIN
SELECT order_number, total_amount
FROM orders
WHERE customer_id = 42;

-- 자주 사용하는 쿼리의 실행 계획 확인
EXPLAIN ANALYZE
SELECT order_number, total_amount
FROM orders
WHERE customer_id = 42;

-- 데이터베이스의 모든 인덱스 목록
SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type = 'index'
  AND sql IS NOT NULL   -- PRIMARY KEY 자동 생성 인덱스 제외
ORDER BY tbl_name, name;

-- 데이터베이스의 모든 인덱스 목록
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME;

-- 데이터베이스의 모든 인덱스 목록
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- 인덱스 있음: 빠른 검색
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 결과: SEARCH orders USING INDEX idx_orders_ordered_at

-- 인덱스 있음: 빠른 검색
EXPLAIN
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 인덱스 있음: 빠른 검색
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE ordered_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 인덱스 없음: 전체 스캔
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE notes LIKE '%긴급%';
-- 결과: SCAN orders
-- (앞에 와일드카드가 붙은 LIKE '%...'는 B-트리 인덱스를 사용할 수 없음)

-- 인덱스 없음: 전체 스캔
EXPLAIN
SELECT * FROM orders
WHERE notes LIKE '%긴급%';

-- 인덱스 없음: 전체 스캔
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE notes LIKE '%긴급%';

EXPLAIN QUERY PLAN
SELECT order_number, total_amount
FROM orders
WHERE status = 'confirmed'
  AND ordered_at >= '2024-01-01';
-- SEARCH orders USING INDEX idx_orders_status_date (status=? AND ordered_at>?)

EXPLAIN
SELECT order_number, total_amount
FROM orders
WHERE status = 'confirmed'
  AND ordered_at >= '2024-01-01';

EXPLAIN ANALYZE
SELECT order_number, total_amount
FROM orders
WHERE status = 'confirmed'
  AND ordered_at >= '2024-01-01';

-- customer_id로 검색하고, ordered_at과 total_amount만 조회하는 쿼리가 빈번하다면:
CREATE INDEX IF NOT EXISTS idx_orders_covering
ON orders (customer_id, ordered_at, total_amount);

-- 이 쿼리는 인덱스만으로 응답 가능 (테이블 접근 불필요)
EXPLAIN QUERY PLAN
SELECT ordered_at, total_amount
FROM orders
WHERE customer_id = 42;
-- 기대 결과: SEARCH orders USING COVERING INDEX idx_orders_covering (customer_id=?)

-- 정리
DROP INDEX IF EXISTS idx_orders_covering;

-- customer_id로 검색하고, ordered_at과 total_amount만 조회하는 쿼리가 빈번하다면:
CREATE INDEX idx_orders_covering
ON orders (customer_id, ordered_at, total_amount);

-- EXPLAIN의 Extra 칼럼에 "Using index"가 표시되면 커버링 인덱스 사용
EXPLAIN
SELECT ordered_at, total_amount
FROM orders
WHERE customer_id = 42;

-- 정리
DROP INDEX idx_orders_covering ON orders;

-- PostgreSQL은 INCLUDE 절로 커버링 인덱스를 더 명확히 표현
CREATE INDEX IF NOT EXISTS idx_orders_covering
ON orders (customer_id) INCLUDE (ordered_at, total_amount);

-- "Index Only Scan"이 표시되면 성공
EXPLAIN ANALYZE
SELECT ordered_at, total_amount
FROM orders
WHERE customer_id = 42;

-- 정리
DROP INDEX IF EXISTS idx_orders_covering;

-- 활성 상품만 인덱싱 (is_active = 1인 행만)
CREATE INDEX IF NOT EXISTS idx_active_products
ON products (name) WHERE is_active = 1;

-- 활성 상품 검색 시 부분 인덱스 사용
EXPLAIN QUERY PLAN
SELECT id, name, price
FROM products
WHERE is_active = 1 AND name LIKE '삼성%';
-- SEARCH products USING INDEX idx_active_products (name>? AND name<?)

-- 정리
DROP INDEX IF EXISTS idx_active_products;

-- 활성 상품만 인덱싱
CREATE INDEX IF NOT EXISTS idx_active_products
ON products (name) WHERE is_active = true;

-- 미처리 주문만 인덱싱 (전체 주문 중 일부)
CREATE INDEX IF NOT EXISTS idx_pending_orders
ON orders (customer_id, ordered_at) WHERE status = 'pending';

EXPLAIN ANALYZE
SELECT order_number, ordered_at
FROM orders
WHERE status = 'pending' AND customer_id = 42;

-- 정리
DROP INDEX IF EXISTS idx_active_products;
DROP INDEX IF EXISTS idx_pending_orders;