Need systematic approach to diagnosing slow PostgreSQL queries on large datasets

[James.M]

I'm a data analyst working with a large PostgreSQL database where some of our core reporting queries have started to time out as our dataset has grown into the hundreds of millions of rows. I've added basic indexes on the columns in our WHERE clauses, but the performance gains were marginal, and I suspect the issue lies with inefficient joins, subqueries, or the way we're aggregating data across several large tables. For database specialists, what is your systematic approach to diagnosing and optimizing a slow SQL query beyond just looking at the execution plan? Are there specific anti-patterns in query writing you always check for first, and how do you decide when to refactor the query logic versus when to propose changes to the underlying database schema or indexing strategy for a more fundamental fix?