How to diagnose and optimize an analytical query with joins and aggregations?

[MadisonW]

I'm a database administrator for an e-commerce platform, and we're experiencing severe performance degradation on a critical reporting query that joins several large tables with complex aggregations. I've added basic indexes, but the execution plan still shows inefficient table scans and high-cost sorts. For other DBAs who have optimized similar heavy analytical queries, what are your go-to strategies for query rewriting, index tuning, or considering materialized views, and how do you systematically identify the root cause bottleneck when the plan is complex?