Your question is Optimizing Slow PostgreSQL Production Queries. Take a moment with it on the right.
Talk me through your thinking if you like. When you're confident, submit your answer and I'll grade it like a real screen (7/10 or better passes).
Slow queries in production affect latency, throughput, and operational stability. For a Software Engineer role, you are expected to explain not just how to add indexes, but how to diagnose the real bottleneck before changing schema or SQL.
You are asked to describe how you would optimize a slow-running PostgreSQL query in a production environment used by a Telus Digital platform. Explain how you would investigate the issue safely, what evidence you would collect, how you would use EXPLAIN / EXPLAIN ANALYZE, and how you would decide whether the problem is caused by joins, filtering, sorting, stale statistics, or poor indexing. Then describe the indexing strategies you would consider, including when to use composite, partial, covering, and expression indexes, and when partitioning may help.
The interviewer expects a structured production-focused answer: diagnosis first, then query changes, then index design, then validation and trade-offs such as write overhead, storage cost, and lock risk.