Systematic PostgreSQL performance tuning for large analytics workloads

[LukeJJ]

Our customer analytics dashboard has become unusably slow as our dataset grew into the hundreds of millions of rows, and after some profiling, it's clear the bottleneck is a handful of complex SQL queries with multiple joins and aggregations running on our PostgreSQL database. I've added basic indexes on the foreign keys, but query plans still show sequential scans and high-cost sorts. For database administrators dealing with large-scale analytics queries, what is your systematic approach to SQL performance tuning beyond throwing indexes at everything? How do you effectively rewrite queries or redesign schemas for better performance, and what specific PostgreSQL configuration parameters or extensions have you found most impactful for optimizing join and aggregation performance on large tables?