Optimize Slow PostgreSQL Reporting Queries

Context

Slow SQL queries affect dashboards, APIs, and scheduled reports. In PostgreSQL, optimization usually requires understanding both the SQL itself and how the database executes it.

Question

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

1. How you would identify where the query is spending time
2. What you would check in the execution plan
3. Common query-level fixes, such as filtering earlier or reducing unnecessary work
4. When indexing helps and when it does not
5. How you would validate that the optimized query is actually better

Scope Guidance

Keep the discussion practical and interview-focused. The interviewer expects a structured approach rather than a list of random tuning ideas. You do not need to cover deep PostgreSQL internals, but you should be able to explain the main optimization workflow clearly and mention concrete tools such as EXPLAIN or EXPLAIN ANALYZE.