PostgreSQL Query Optimization Techniques

Query performance is a common backend bottleneck. PostgreSQL provides rich diagnostic tools and optimization levers delivering orders-of-magnitude improvements.

Execution Plan Diagnosis

Use EXPLAIN (ANALYZE, BUFFERS) for real times and buffer hits. Watch for Seq Scans, row estimate deviations, Sort/Hash spills to disk.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, o.created_at, u.username, sum(oi.quantity * oi.price) AS total
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY o.order_id, o.created_at, u.username
HAVING sum(oi.quantity * oi.price) > 1000
ORDER BY total DESC
LIMIT 100;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_user
ON orders (created_at, user_id) INCLUDE (order_id);

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.01);
ANALYZE orders (order_id, user_id, created_at);

SET enable_hashjoin = off; SET enable_mergejoin = on;
EXPLAIN ANALYZE SELECT * FROM orders o JOIN users u ON o.user_id = u.user_id;
RESET enable_hashjoin; RESET enable_mergejoin;

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname IN ('orders','order_items','users') ORDER BY idx_scan DESC;

Index Design and Tuning

Choose index types by pattern: B-tree for equality, GIN for JSONB/arrays, BRIN for ordered time-series. Order composite index columns by selectivity (highest first).

Best Practices

Regularly analyze slow query logs, monitor mean_exec_time in pg_stat_statements, use CONCURRENTLY on large tables to avoid locking during index creation.