Split View: PostgreSQL 성능 튜닝 실전 가이드 — 쿼리 최적화, 인덱스 전략, EXPLAIN 분석

-- Estimated execution plan (does NOT actually execute)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Actual execution + measured time included (Warning: actually executes the query)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Most detailed analysis (includes buffer usage)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01';

Hash Join  (cost=1250.00..5680.50 rows=15000 width=48)
           (actual time=12.5..89.3 rows=14800 loops=1)
  Hash Cond: (o.user_id = u.id)
  -> Seq Scan on orders o  (cost=0.00..3500.00 rows=15000 width=16)
                           (actual time=0.02..45.1 rows=14800 loops=1)
       Filter: (created_at > '2026-01-01')
       Rows Removed by Filter: 85200
  -> Hash  (cost=1000.00..1000.00 rows=50000 width=36)
           (actual time=12.3..12.3 rows=50000 loops=1)
       -> Seq Scan on users u  (cost=0.00..1000.00 rows=50000 width=36)
                               (actual time=0.01..6.8 rows=50000 loops=1)
Planning Time: 0.25 ms
Execution Time: 95.8 ms

# Seq Scan on large table -> index needed
Seq Scan on orders  (rows=1000000)

# Nested Loop with large rows -> change join strategy
Nested Loop  (actual loops=50000)

# Sort with external disk usage -> insufficient work_mem
Sort Method: external merge  Disk: 128000kB

# Estimated rows vs actual rows mismatch -> ANALYZE needed
(rows=100) ... (actual rows=50000)

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- Partial index (conditional index)
CREATE INDEX idx_orders_active
ON orders(user_id)
WHERE status = 'active';

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Index: (user_id, created_at, status)

-- Can use index (matches from left)
WHERE user_id = 1
WHERE user_id = 1 AND created_at > '2026-01-01'
WHERE user_id = 1 AND created_at > '2026-01-01' AND status = 'active'

-- Cannot use index (skips middle column)
WHERE user_id = 1 AND status = 'active'  -- skips created_at
WHERE created_at > '2026-01-01'          -- missing user_id
WHERE status = 'active'                  -- missing first column

-- JSONB field index
CREATE INDEX idx_products_metadata
ON products USING GIN(metadata);

-- JSONB search is now fast
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Array index
CREATE INDEX idx_posts_tags
ON posts USING GIN(tags);

SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

-- Index ignored when function is applied
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- Create a functional index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- Type mismatch
SELECT * FROM users WHERE id = '123';  -- id is integer but comparing with string
-- Correct type
SELECT * FROM users WHERE id = 123;

-- Leading wildcard in LIKE
SELECT * FROM users WHERE name LIKE '%kim%';  -- Full Scan
-- Fixed prefix
SELECT * FROM users WHERE name LIKE 'kim%';  -- Index can be used

-- OR condition
SELECT * FROM users WHERE email = 'a@b.com' OR name = 'Kim';
-- Split with UNION
SELECT * FROM users WHERE email = 'a@b.com'
UNION ALL
SELECT * FROM users WHERE name = 'Kim';

-- Find unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index size per table
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(tablename::regclass)) as total_size,
       pg_size_pretty(pg_indexes_size(tablename::regclass)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

-- N+1 pattern (common with ORMs)
-- Query 1: SELECT * FROM users LIMIT 100;
-- Query 2-101: SELECT * FROM orders WHERE user_id = ?;

-- Use JOIN to fetch at once
SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
LIMIT 100;

-- Or use subquery + IN
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE created_at > '2026-01-01'
);

-- OFFSET approach (slower as pages get deeper)
SELECT * FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20;

-- Keyset Pagination (cursor-based)
SELECT * FROM orders
WHERE id < 900000  -- last id of the previous page
ORDER BY id DESC
LIMIT 20;

-- Covering index + subquery
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20
) sub ON o.id = sub.id;

-- EXISTS is more efficient for large datasets (correlated subquery)
-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 10000
);

-- IN (when subquery result is large)
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 10000
);

-- Exact COUNT (causes Full Scan)
SELECT COUNT(*) FROM orders;

-- Approximate value when sufficient
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'orders';

-- Conditional COUNT optimization
-- Index Only Scan possible with an index
SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- Required: CREATE INDEX idx_orders_status ON orders(status);

-- In PostgreSQL 12+, CTEs are inlined by default
-- MATERIALIZED hint forces materialization

-- Auto-inlined (optimizer optimizes)
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE name LIKE 'K%';

-- Forced materialization (prevents redundant execution)
WITH active_users AS MATERIALIZED (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users au
JOIN orders o ON au.id = o.user_id;

# shared_buffers: 25% of total RAM (most important!)
# Based on 16GB RAM
shared_buffers = 4GB

# effective_cache_size: Expected memory including OS cache (75% of RAM)
effective_cache_size = 12GB

# work_mem: Memory for sort/hash operations (per session x per query)
# Caution: connections x work_mem = total possible memory usage
work_mem = 256MB

# maintenance_work_mem: Used for VACUUM, CREATE INDEX
maintenance_work_mem = 1GB

# WAL size (write-heavy workloads)
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB

# Checkpoint interval
checkpoint_completion_target = 0.9

# Enable parallel queries
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# Random I/O cost (lower for SSD)
random_page_cost = 1.1  # HDD: 4.0, SSD: 1.1

# Statistics collection precision
default_statistics_target = 200  # Default: 100

-- Check dead tuples per table
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Manual VACUUM (normally handled by autovacuum)
VACUUM ANALYZE orders;

-- VACUUM FULL (caution: table lock! — reclaims disk space)
VACUUM FULL orders;  -- Do NOT use in production!

# Autovacuum default settings
autovacuum = on
autovacuum_max_workers = 5

# Per-table settings for large tables
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,  -- Run when 1% are dead tuples
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005
);

-- When EXPLAIN estimated rows differ significantly from actual
ANALYZE orders;

-- Update statistics for entire DB
ANALYZE;

-- Increase statistics precision for a specific column
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries
SELECT query,
       calls,
       ROUND(total_exec_time::numeric, 2) as total_time_ms,
       ROUND(mean_exec_time::numeric, 2) as avg_time_ms,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Most frequently called queries (by total time)
SELECT query, calls,
       ROUND(total_exec_time::numeric, 2) as total_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- Queries waiting for locks
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.relation = bl.relation
  AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;