Contents
see List개요
SQL 성능 최적화는 데이터베이스 운영의 핵심입니다. 느린 쿼리 하나가 전체 시스템의 응답 시간을 저하시킬 수 있으며, 데이터가 증가할수록 최적화의 중요성은 커집니다. 이 글에서는 PostgreSQL을 중심으로 실행 계획 분석 방법, 인덱스 설계 원칙, 그리고 실전 최적화 기법을 다룹니다.
핵심 개념
실행 계획(Execution Plan): 쿼리 옵티마이저가 SQL을 실행하기 위해 선택한 전략입니다. EXPLAIN ANALYZE를 통해 실제 실행 시간, 행 수, 메모리 사용량을 확인할 수 있습니다. 실행 계획의 각 노드는 Seq Scan, Index Scan, Hash Join 등의 연산자로 구성됩니다.
인덱스 종류: PostgreSQL은 B-tree(기본), Hash, GiST, SP-GiST, GIN, BRIN 인덱스를 지원합니다. B-tree는 등호/범위 검색에, GIN은 전문 검색과 배열/JSONB에, BRIN은 시계열 데이터의 범위 검색에 최적화되어 있습니다.
쿼리 플래너 통계: PostgreSQL은 각 테이블과 컬럼의 통계 정보를 기반으로 최적의 실행 계획을 선택합니다. ANALYZE 명령으로 통계를 최신 상태로 유지하는 것이 중요합니다.
커버링 인덱스: 인덱스만으로 쿼리를 완전히 처리할 수 있는 인덱스입니다. INCLUDE 절을 사용하여 인덱스에 추가 컬럼을 포함시킬 수 있어, 테이블 접근(Heap Fetch)을 제거할 수 있습니다.
실전 예제
실행 계획 분석과 인덱스 최적화 실전 예제입니다.
-- 1. 실행 계획 분석
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
AND o.status = 'completed'
GROUP BY u.name
ORDER BY total DESC
LIMIT 10;
-- 2. 복합 인덱스 설계 (선택도 높은 컬럼 우선)
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC)
INCLUDE (user_id, amount)
WHERE status = 'completed';
-- 3. 느린 쿼리 개선 - 서브쿼리를 JOIN으로 변환
-- Before (느린 버전)
SELECT * FROM products
WHERE id IN (SELECT product_id FROM order_items WHERE quantity > 10);
-- After (빠른 버전)
SELECT DISTINCT p.*
FROM products p
JOIN order_items oi ON p.id = oi.product_id
WHERE oi.quantity > 10;
-- 4. 페이지네이션 최적화 - OFFSET 대신 키셋 페이지네이션
-- Before (느린 OFFSET)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- After (빠른 키셋 페이지네이션)
SELECT * FROM posts
WHERE created_at < '2024-06-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 20;
인덱스 사용 현황 모니터링 쿼리입니다.
-- 사용되지 않는 인덱스 찾기
SELECT
schemaname || '.' || relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan AS times_used
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan < 50
AND NOT indisunique
AND NOT indisprimary
ORDER BY pg_relation_size(i.indexrelid) DESC;
-- 테이블별 캐시 히트율 확인
SELECT
relname,
heap_blks_hit * 100.0 / NULLIF(heap_blks_hit + heap_blks_read, 0)
AS cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_hit + heap_blks_read > 0
ORDER BY cache_hit_ratio ASC;
활용 팁
- EXPLAIN 시각화 도구: explain.dalibo.com이나 pgMustard를 사용하면 실행 계획을 시각적으로 분석할 수 있어 병목 지점을 빠르게 파악할 수 있습니다.
- 부분 인덱스 활용: WHERE 조건이 자주 사용되는 경우 부분 인덱스로 인덱스 크기를 줄이고 성능을 높일 수 있습니다. 예: WHERE is_active = true인 행만 인덱싱.
- 표현식 인덱스: LOWER(email)이나 date_trunc('day', created_at) 같은 표현식 검색이 많다면 표현식 인덱스를 생성하세요.
- Connection Pooling: PgBouncer나 Supavisor를 사용하여 데이터베이스 연결을 풀링하면, 동시 접속이 많은 환경에서 성능이 크게 향상됩니다.
- 정기적 VACUUM: autovacuum 설정을 모니터링하고, 대량 삭제/업데이트 후에는 수동으로 VACUUM ANALYZE를 실행하세요.
마무리
SQL 성능 최적화는 한 번의 작업이 아닌 지속적인 과정입니다. 실행 계획을 정기적으로 분석하고, 인덱스 사용 현황을 모니터링하며, 데이터 증가에 따른 쿼리 성능 변화를 추적하는 것이 중요합니다. 작은 최적화가 쌓여 시스템 전체의 안정성과 사용자 경험을 크게 향상시킵니다.