Your question is Optimizing Large Transaction Table Joins. 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).
Large joins across transaction-heavy tables are common in trading and post-trade data systems, and poor performance usually comes from a mix of bad join order, missing indexes, unnecessary row volume, and stale statistics.
You are asked to explain how you would optimize a slow-running PostgreSQL query that joins several large transaction tables, such as fills, orders, executions, and instrument reference data. Walk through how you would diagnose the bottleneck, what you would inspect in the execution plan, how you would decide whether indexes or partitioning would help, and how you would rewrite the query if the issue is excessive intermediate rows or inefficient filtering.
The interviewer expects a practical, database-focused answer rather than a generic performance checklist. You should cover EXPLAIN (ANALYZE, BUFFERS), join strategies, index design, predicate pushdown, table statistics, and when schema changes such as partitioning or pre-aggregation are justified.