How to systematically optimize slow PostgreSQL queries beyond indexing?

[JosephNJ]

I'm a backend developer working on a reporting feature that queries a large PostgreSQL database with several complex joins across tables containing millions of rows, and the performance is becoming unacceptable as the dataset grows. I've added basic indexes on the foreign keys, but the query planner is still choosing sequential scans in some cases, and I'm not sure how to proceed with more advanced optimization. For database experts, what's your systematic approach to diagnosing and fixing slow queries beyond looking at the execution plan? When is it appropriate to consider denormalization, materialized views, or query rewriting versus simply adding more indexes, and how do you effectively measure the impact of changes in a production environment without causing downtime?