PostgreSQL 쿼리 최적화: 느린 쿼리에서 밀리초 미만까지

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

BEGIN;
EXPLAIN ANALYZE DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ...;

-- 상관된 열에 대한 확장 통계 생성
CREATE STATISTICS orders_status_date (dependencies)
ON status, created_at FROM orders;
ANALYZE orders;

CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 쿼리: WHERE status = 'active' AND created_at > '2026-01-01' ORDER BY total DESC
 
-- 좋음: 동등 먼저, 그다음 범위, 그다음 정렬
CREATE INDEX idx_orders_optimized ON orders (status, created_at, total);
 
-- 나쁨: 동등 전에 범위
CREATE INDEX idx_orders_bad ON orders (created_at, status, total);

-- 자주 실행하는 쿼리:
-- SELECT id, total, status FROM orders WHERE customer_id = 42;
 
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id) INCLUDE (total, status);

-- 주문의 2%만 'pending'이지만 지속적으로 쿼리함
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 또 다른 일반적인 패턴: 소프트 삭제
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;
 
-- 활성 레코드에만 유니크 제약 조건
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

-- JSONB 포함 쿼리
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
 
-- 이제 이것이 빠름:
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
 
-- 전문 검색
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', title || ' ' || body));

CREATE INDEX idx_products_metadata_pathops
ON products USING GIN (metadata jsonb_path_ops);

-- IP 범위 조회
CREATE INDEX idx_ip_ranges ON ip_blocks USING GiST (ip_range);
 
SELECT * FROM ip_blocks WHERE ip_range @> '192.168.1.50'::inet;
 
-- 시간 범위 (스케줄 충돌)
CREATE INDEX idx_bookings_period ON bookings USING GiST (
  tstzrange(start_time, end_time)
);
 
-- 겹치는 예약 찾기:
SELECT * FROM bookings
WHERE tstzrange(start_time, end_time) && tstzrange('2026-03-01 10:00', '2026-03-01 12:00');

-- 큰 텍스트 열에 대한 동등 전용 조회에 좋음
CREATE INDEX idx_sessions_token ON sessions USING HASH (session_token);
 
-- 이것은 빠름:
SELECT * FROM sessions WHERE session_token = 'abc123def456...';
 
-- 이것은 hash 인덱스를 사용할 수 없음:
SELECT * FROM sessions WHERE session_token LIKE 'abc%';

-- 사용되지 않는 인덱스 찾기
SELECT
  schemaname, tablename, indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint
    WHERE contype IN ('p', 'u')  -- 기본 키와 유니크 제약 조건 제외
  )
ORDER BY pg_relation_size(indexrelid) DESC;

-- 중복 인덱스 찾기 (같은 열, 같은 순서)
SELECT
  a.indexrelid::regclass AS index1,
  b.indexrelid::regclass AS index2,
  a.indrelid::regclass AS table_name
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
  AND a.indexrelid < b.indexrelid
  AND a.indkey = b.indkey;

# Python / SQLAlchemy — 클래식 함정
orders = session.query(Order).filter_by(status='pending').all()
 
for order in orders:
    # 각 주문에 대해 별도의 쿼리를 트리거
    print(f"Customer: {order.customer.name}")

-- 쿼리 1: 모든 대기 중 주문 가져오기
SELECT * FROM orders WHERE status = 'pending';
 
-- 쿼리 2: 주문 1의 고객 가져오기
SELECT * FROM customers WHERE id = 101;
 
-- 쿼리 3: 주문 2의 고객 가져오기
SELECT * FROM customers WHERE id = 102;
 
-- ...쿼리 502: 주문 500의 고객 가져오기
SELECT * FROM customers WHERE id = 600;

-- 0ms 이상 걸리는 모든 쿼리 로깅 (즉, 모든 쿼리)
SET log_min_duration_statement = 0;
 
-- 프로덕션에 더 좋음: 느린 쿼리만 로깅
ALTER SYSTEM SET log_min_duration_statement = 50;  -- 50ms 임계값
SELECT pg_reload_conf();

-- 느린 쿼리에 대한 auto_explain 활성화
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 100;  -- 100ms
SET auto_explain.log_nested_statements = ON;
SET auto_explain.log_analyze = ON;

# SQLAlchemy — joinedload가 같은 쿼리에서 고객을 가져옴
from sqlalchemy.orm import joinedload
 
orders = (
    session.query(Order)
    .options(joinedload(Order.customer))
    .filter_by(status='pending')
    .all()
)

SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

-- 접근 1: 단일 JOIN (일대다이면 중복 데이터를 생산할 수 있음)
SELECT o.*, i.product_name, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'pending';
 
-- 접근 2: 두 쿼리 (주문에 열이 많으면 더 적은 데이터 전송)
SELECT * FROM orders WHERE status = 'pending';
-- 애플리케이션이 order_ids = [1, 2, 3, ...]를 수집
SELECT * FROM order_items WHERE order_id = ANY($1);

-- WHERE의 서브쿼리 — 종종 괜찮지만 때로는 끔찍함
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'US'
);
 
-- JOIN — 보통 최선의 선택
SELECT o.* FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country = 'US';
 
-- CTE — PG 11 이하에서는 구체화됨, PG 12+에서는 최적화됨
WITH us_customers AS (
  SELECT id FROM customers WHERE country = 'US'
)
SELECT o.* FROM orders o
JOIN us_customers uc ON uc.id = o.customer_id;

-- EXISTS — 첫 매치에서 멈춤, 존재 확인에 좋음
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);
 
-- 서브쿼리가 있는 IN — 먼저 전체 결과 집합을 만듦
SELECT * FROM orders
WHERE id IN (SELECT order_id FROM returns);
 
-- JOIN — returns에 주문당 여러 행이 있으면 중복을 생산할 수 있음
SELECT DISTINCT o.* FROM orders o
JOIN returns r ON r.order_id = o.id;

-- 서브쿼리가 적은 행을 반환하면 IN이 완전히 괜찮음
SELECT * FROM orders
WHERE customer_id IN (1, 2, 3, 4, 5);

-- 나쁨: 킬로바이트의 데이터가 있는 TEXT 열을 포함한 25개 열 모두 가져옴
SELECT * FROM products WHERE category = 'electronics';
 
-- 좋음: 필요한 것만 가져옴
SELECT id, name, price FROM products WHERE category = 'electronics';

-- 느림: 상관 서브쿼리가 행당 한 번 실행
SELECT
  o.*,
  (SELECT COUNT(*) FROM order_items i WHERE i.order_id = o.id) AS item_count,
  (SELECT SUM(amount) FROM payments p WHERE p.order_id = o.id) AS total_paid
FROM orders o
WHERE o.status = 'completed';
 
-- 빠름: 윈도우 함수가 단일 패스로 계산
SELECT
  o.*,
  COUNT(i.id) OVER (PARTITION BY o.id) AS item_count,
  SUM(p.amount) OVER (PARTITION BY o.id) AS total_paid
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
LEFT JOIN payments p ON p.order_id = o.id
WHERE o.status = 'completed';

-- 느림: 상관 서브쿼리
SELECT * FROM notifications n
WHERE n.created_at = (
  SELECT MAX(created_at) FROM notifications
  WHERE user_id = n.user_id
);
 
-- 빠름: DISTINCT ON (PostgreSQL 전용, 매우 유용)
SELECT DISTINCT ON (user_id) *
FROM notifications
ORDER BY user_id, created_at DESC;
 
-- 역시 빠름: ROW_NUMBER 윈도우 함수
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM notifications
) sub
WHERE rn = 1;

CREATE INDEX idx_notifications_user_latest
ON notifications (user_id, created_at DESC);

-- 1페이지: 빠름
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 0;
 
-- 500페이지: 느림 — PostgreSQL이 9,980행을 스캔하고 버려야 함
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 9980;
 
-- 5000페이지: 매우 느림 — 20개를 반환하기 위해 99,980행을 스캔
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 99980;

-- 첫 페이지
SELECT * FROM products ORDER BY id LIMIT 20;
 
-- 다음 페이지: 이전 페이지의 마지막 id 사용
SELECT * FROM products WHERE id > 12345 ORDER BY id LIMIT 20;

-- 여러 정렬 열이 있는 키셋 페이지네이션
SELECT * FROM products
WHERE (price, id) > (29.99, 12345)
ORDER BY price, id
LIMIT 20;

-- 단일 테이블 분석
ANALYZE orders;
 
-- 전체 데이터베이스 분석
ANALYZE;
 
-- 열의 현재 통계 보기
SELECT
  tablename, attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- 편향된 분포를 가진 열에 대한 통계 대상 증가
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- autovacuum 상태 확인
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 쓰기가 많은 테이블에 대한 더 공격적인 autovacuum
ALTER TABLE sessions SET (
  autovacuum_vacuum_scale_factor = 0.01,     -- 1% 죽은 튜플에서 vacuum (기본 20%)
  autovacuum_analyze_scale_factor = 0.005,   -- 0.5% 변경에서 analyze (기본 10%)
  autovacuum_vacuum_cost_delay = 2           -- 더 빠른 vacuum (최신 PG에서 기본 2ms)
);

-- 테이블 블로트 추정 (간소화 버전)
SELECT
  current_database(),
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  ) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

pg_repack --table orders --no-kill-backend -d mydb

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
 
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
; 풀 모드
pool_mode = transaction
 
; 풀 크기 조정
default_pool_size = 20
max_client_conn = 500
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
 
; 타임아웃
server_idle_timeout = 300
client_idle_timeout = 0
query_timeout = 30

const pool = new Pool({
  host: "127.0.0.1",
  port: 6432, // PgBouncer 포트, PostgreSQL 포트가 아님
  database: "mydb",
  max: 20, // PgBouncer default_pool_size에 맞춤
  statement_timeout: 30000,
  // PgBouncer transaction 모드를 위해 준비된 문 비활성화
  prepare: false,
});

-- 100ms 이상 걸리는 모든 쿼리 임시 로깅
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

-- 총 실행 시간별 상위 10 쿼리
SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_time_ms,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <느린 쿼리>;

-- 통계가 마지막으로 업데이트된 시점은?
SELECT
  relname,
  last_analyze,
  last_autoanalyze,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

ANALYZE orders;

-- 이 테이블에 어떤 인덱스가 존재하는가?
SELECT
  indexname,
  indexdef,
  idx_scan,
  idx_tup_read,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_indexes ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
WHERE tablename = 'orders'
ORDER BY idx_scan DESC;

-- 동시에 인덱스 생성 (테이블을 잠그지 않음)
CREATE INDEX CONCURRENTLY idx_orders_customer_status
ON orders (customer_id, status)
WHERE status IN ('pending', 'processing');
 
-- 인덱스가 사용되는지 확인
EXPLAIN (ANALYZE, BUFFERS) <느린 쿼리>;

-- work_mem: 정렬과 해시 조인을 위한 메모리 (연결당이 아니라 작업당)
-- 기본값은 4MB로, 복잡한 쿼리에는 너무 낮음
SET work_mem = '64MB';  -- 이것으로 시도하고 EXPLAIN ANALYZE 다시 실행
 
-- effective_cache_size: 플래너에게 얼마나 많은 디스크 캐시를 기대하는지 알려줌
-- 총 RAM의 ~75%로 설정
SHOW effective_cache_size;
 
-- random_page_cost: 랜덤 I/O 대 순차 I/O 비율
-- 기본값은 4.0, SSD에는 1.1-1.5로 설정
SHOW random_page_cost;

-- 변경 후 새 데이터를 보기 위해 pg_stat_statements 초기화
SELECT pg_stat_statements_reset();
 
-- 1시간/1일 후 확인
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round(max_exec_time::numeric, 2) AS max_time_ms,
  query
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY mean_exec_time DESC;

-- 1. pg_stat_statements 설치
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- 2. 느린 쿼리 로깅 활성화 (50ms가 제 임계값)
ALTER SYSTEM SET log_min_duration_statement = 50;
 
-- 3. SSD에 적합한 설정
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_io_concurrency = 200;
 
-- 4. 빈약한 기본값에서 work_mem 증가
ALTER SYSTEM SET work_mem = '32MB';
 
-- 5. effective_cache_size를 RAM의 75%로 설정
-- 16GB 서버의 경우:
ALTER SYSTEM SET effective_cache_size = '12GB';
 
-- 6. shared_buffers를 RAM의 25%로 설정
-- 16GB 서버의 경우:
ALTER SYSTEM SET shared_buffers = '4GB';
 
-- 7. autovacuum 반응성 개선
ALTER SYSTEM SET autovacuum_naptime = '10s';
 
-- 변경 적용
SELECT pg_reload_conf();
-- 참고: shared_buffers는 재시작 필요

-- 주의가 필요한 테이블
SELECT
  schemaname || '.' || relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 1)
    ELSE 0
  END AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;