Diagnosing and resolving SQL bottlenecks in large PostgreSQL/MySQL systems

[HarperXW]

I'm responsible for a critical reporting database where some key analytical queries have started taking several minutes to complete as our dataset has grown into the hundreds of millions of rows. I've added basic indexes, but the query planner is still choosing sequential scans in some complex joins. For database administrators who've optimized large-scale PostgreSQL or MySQL systems, what's your systematic approach to diagnosing and resolving these performance bottlenecks, and how do you decide when to refactor a query versus redesigning the schema or indexing strategy?

[Jonathan48]

Great problem. Start with a reproducible baseline: enable slow query logging, log_lock_waits, and auto_explain so you get an EXPLAIN ANALYZE with buffers for slow queries without chasing it manually. Collect the top offenders from pg_stat_statements, then run EXPLAIN ANALYZE (BUFFERS) on those queries to see where the time actually goes. Look for unselective sequential scans on large tables, large hash joins, or nested loops and verify if the planner is actually using the indexes you expect.

[RyanQJ]

PostgreSQL playbook: ensure you have good multi-column indexes on join and filter predicates; consider partial indexes for common filter patterns; keeping statistics fresh (ANALYZE) and nudging planner costs with configuration like default_statistics_target can help. If you’re joining enormous tables on a date or id, partitioning (range/list) can prune scans dramatically. For expensive aggregations, a materialized view or summary table can be a huge win, and you can refresh it on a schedule. Don’t forget to enable parallelism (max_parallel_workers_per_gather) and adjust work_mem so big sorts/hashes don’t spill to disk.

[Brian_P]

MySQL side: run EXPLAIN (FORMAT=json) to understand index usage across large joins. Make sure you have appropriate composite indexes that cover the join and where conditions, then ANALYZE TABLE to refresh statistics. Partitioning (by date, region, etc.) can cut down scanned rows. Tuning innodb_buffer_pool_size and related memory settings helps a lot for analytics workloads. If you can, create pre-aggregated/summary tables or use a write-friendly reporting store to avoid heavy, repeated scans on the raw data.

[PenelopeCT]

Decision criteria: if a handful of queries are the bottleneck and can be made to use the existing indexes with small rewrites, do the refactor there. If the workload keeps scanning large portions of a hot table or you see poor selectivity due to data distribution, consider schema changes (partitioning, denormalization, or materialized views). For long-term resilience, combine indexing improvements with a schema strategy and a regular refresh plan; if needed, consider a hybrid approach (core queries optimized + summary tables for analytics).

[Brian67]

4-week practical plan: 1) Instrument and reproduce: enable slow_log, log_min_duration_statement, pg_stat_statements, and auto_explain; identify top 5-10 queries. 2) Tinker on queries: add composite or partial indexes, rewrite when helpful, and test with EXPLAIN ANALYZE. 3) If still slow, introduce partitioning or materialized views for heavy aggregations; 4) Tune memory and parallelism (work_mem, shared_buffers, max_parallel_workers_per_gather). 5) Validate under production-like load, monitor, and iterate. If you want, paste the top queries and schema shape and I’ll sketch a concrete plan.