Slow SQL can block dashboards, delay batch jobs, and create cascading issues in downstream systems. For an engineering manager, the interviewer wants to hear both technical depth and a structured debugging process.
You are told that a PostgreSQL query powering an Apple Analytics dashboard has grown from minutes to two hours. Walk through how you would debug and optimize it. Explain how you would isolate whether the problem comes from joins, aggregations, filters, window functions, subqueries, data growth, or missing indexes, and how you would validate that an optimization actually improved the query without changing the result.
Answer at the level of a senior engineer or manager: start with how you would inspect the query and execution plan, then discuss likely bottlenecks, concrete optimization techniques in PostgreSQL, and the trade-offs or risks of each change.