Database Query Profiling: Finding and Fixing Performance Bottlenecks

Query profiling identifies why a query is slow. Rather than guessing, profiling measures where time is spent: CPU, I/O, locks, or network. This data guides targeted optimization.

Profiling Tools

PostgreSQL: EXPLAIN ANALYZE BUFFERS shows execution plan with actual timing and buffer access. pg_stat_statements tracks query statistics. auto_explain logs slow queries automatically. pgBadger analyzes PostgreSQL logs for query performance patterns.

MySQL: EXPLAIN ANALYZE (MySQL 8.0.18+) shows execution plan. performance_schema tracks query execution statistics. sys schema provides query performance summaries. pt-query-digest analyzes slow query logs.

Key Metrics

Execution time: total time and time per execution. Buffer usage: shared hit reveals cache efficiency. Rows examined vs returned: high examined-to-returned ratio suggests missing indexes. Wait events: what the query is waiting for (I/O, locks, CPU).

Optimization Workflow

Identify slow queries via monitoring. Profile with EXPLAIN ANALYZE. Check for sequential scans on large tables. Verify index usage. Examine join strategies. Review sort operations. Test the fix. Profile again to confirm improvement. Monitor in production.