Suppose you have a dataset with 10 million rows and need to run a complex query that aggregates data from multiple tables. How would you optimize the query for better performance?

To optimize a complex aggregation query on 10 million rows, start by ensuring that all columns used in JOIN, WHERE, and GROUP BY clauses are indexed. Use partitioning on the largest table (e.g., by date or a high‑cardinality key) so the optimizer can prune data early. Consider materialized views or pre‑aggregated tables for frequently used aggregates. Rewrite the query to avoid cartesian joins; use explicit join types and filter early. Leverage window functions only when necessary, as they can be expensive. If the database supports it, enable parallel execution and set appropriate parallelism settings. Keep statistics up to date so the optimizer can choose efficient join orders. For very large scans, switch to a columnar storage format (e.g., Parquet) if the DB supports it, which reduces I/O for aggregation. Finally, test the query plan with EXPLAIN, identify bottlenecks, and iterate on indexes or query rewrite. The overall complexity remains O(n) for the scan, but careful indexing and partitioning reduce the constant factor dramatically.