SQL Query Performance Tuning

Finding slow queries

#

• Enable the slow-query log: log_min_duration_statement
• Analyze plans with EXPLAIN ANALYZE
• Watch pg_stat_statements for the most frequent SQL

Common performance killers

#

• Full table scans: missing indexes or conditions that can’t use them
• Implicit type casts: functions on indexed columns disable the index
• N+1 queries: querying row-by-row inside a loop
• Large-offset pagination: OFFSET 1000000 gets slower as you page

Fixes

#

• Rewrite SQL: drive big tables with small result sets (join order)
• Use indexes wisely: covering and composite indexes
• Denormalize judiciously: precomputed summary tables

Summary

#

SQL tuning is about understanding the execution plan. Locate the bottleneck first, then fix it — don’t blindly add indexes.