Optimizing Large Transaction Table Joins

Context

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.

Question

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.

Scope guidance

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.