Suppose you're given a dataset with 10 million rows and you need to perform a complex query that involves aggregating data from multiple tables. How would you optimize the query for better performance?

Start by ensuring that the join columns and any columns used in WHERE or GROUP BY clauses are indexed. If the tables are large, partition them on the most selective filter columns to reduce scan size. Use the query planner’s explain plan to identify full table scans or expensive joins. Prefer hash or merge joins over nested loops when the data set is large. If one table is small, broadcast it to avoid shuffling. Consider materialized views or pre‑aggregated tables for frequently used aggregates. Use window functions sparingly; if you need a running total, a window can be cheaper than a self‑join. Store the data in a columnar format (Parquet, ORC) if using a data lake, which speeds up column pruning and vectorized execution. Enable statistics collection so the optimizer can choose the best plan. Finally, test incremental changes: add indexes, adjust join order, or rewrite the query to use subqueries that reduce the data set early. Each tweak should be validated with an explain plan and performance benchmark to ensure it actually improves throughput.