database-optimizer

Slow query optimization starts with analyzing EXPLAIN ANALYZE execution plans to identify bottlenecks, then creating strategic indexes and rewriting queries to reduce full table scans. Test changes in non-production, measure performance before and after, and monitor write performance to catch replication lag or lock contention in production replicas.

Index design balances read speed with write overhead. Use covering indexes to serve queries entirely from the index, partial indexes for filtered datasets, and multi-column indexes for compound WHERE clauses. Avoid over-indexing writes; validate each index reduces query time measurably before deploying to production.

Lock contention diagnosis involves analyzing slow query logs, execution plans, and replication lag metrics across replicas. Tuning configuration settings, rewriting queries to reduce lock scope, and partitioning strategies can resolve contention while accounting for delays in replicated writes.

Measure performance by running EXPLAIN ANALYZE on the original and optimized query, comparing execution time and resource usage metrics. Document changes, test in staging first, and monitor production metrics post-deployment to confirm the improvement persists under real traffic.

Yes. Execution plans from EXPLAIN ANALYZE reveal which operations consume the most time and resources—sequential scans, missing indexes, or inefficient joins. Reading plans is essential to target optimization efforts and validate that changes actually eliminate bottlenecks.

Always test optimizations in non-production environments first. Validate that new indexes don't degrade write performance or replication lag. Document all changes, monitor lock contention and query times post-deployment, and have a rollback plan in case performance degrades under real load.