Optimizing Slow PostgreSQL Queries

Context

Slow queries affect application latency, reporting jobs, and database cost. In PostgreSQL interviews, this question tests whether you can diagnose performance issues methodically instead of guessing.

Core question

Explain how you would optimize a slow-running query in PostgreSQL. Your answer should cover:

1. How you would identify whether the bottleneck is in joins, filtering, sorting, aggregation, or I/O
2. How you would use tools such as EXPLAIN or EXPLAIN ANALYZE
3. What query rewrites, indexing changes, or schema adjustments you would consider
4. How you would validate that the optimization actually improved performance without changing results

Scope guidance

The interviewer expects a practical, structured explanation rather than a list of random tips. Focus on a repeatable workflow: inspect the execution plan, identify the expensive step, choose an appropriate optimization, and verify the impact. Mention PostgreSQL-specific considerations such as sequential scans, index scans, join strategies, and the trade-offs of adding indexes.