Seeking a systematic SQL optimization plan for slow nested queries in legacy DB

[EthanH]

I'm a junior data analyst, and I've inherited a legacy reporting database with several key queries that are taking over ten minutes to run, crippling our daily dashboard updates. I've added basic indexes on the foreign keys, but the performance gains were minimal. For experienced database developers, what's your systematic approach to SQL query optimization when you're dealing with poorly written, nested subqueries and joins across massive tables? How do you use execution plans to identify the most costly operations, and what are your go-to strategies for rewriting queries, considering materialized views, or even restructuring the underlying schema? Are there specific tools or profiling techniques you'd recommend for pinpointing bottlenecks in a live production environment?