Slow queries are rarely the database engine's fault. PostgreSQL, MySQL, and their managed cousins are fast when asked sensible questions; most performance problems come from missing indexes, queries that scan more rows than they need, and application code that fires hundreds of small queries where one would do. The good news is that query optimization is a measurable engineering task, not a dark art. This guide walks the loop: measure, identify, fix, verify.
What changed in 2026
- EXPLAIN got friendlier. Visual plan analyzers and built-in dashboards in managed databases make reading a query plan less intimidating than the raw text output.
- Managed databases surface slow queries automatically. Most cloud Postgres and MySQL offerings ship a slow-query insights panel, so finding the worst offenders no longer requires manual log parsing.
- ORMs still hide N+1 problems. Despite better tooling, lazy-loaded relations in popular ORMs remain the number-one cause of surprise slowdowns at scale, and they only show up under real data volume.
- AI assistants suggest indexes. Tools can read a plan and propose an index. The suggestions are often reasonable but sometimes redundant with an existing index, so verify before applying. Whether you are tuning a relational or document store, the techniques here apply once you have chosen between SQL and NoSQL for your workload.
Step by step
- Find the slow query. Use the slow-query log or your provider's insights panel. Optimize the queries that cost the most total time, which is frequency times duration, not just the single slowest one.
- Read the plan. Run EXPLAIN ANALYZE. Look for sequential scans on large tables, high row estimates that miss reality, and nested loops over big inputs. These point straight at the problem.
- Add or fix an index. If the plan shows a sequential scan filtering on a column, an index on that column usually turns it into an index scan. Composite indexes help multi-column filters; order matters.
- Reduce the work. Select only the columns you need, paginate large result sets, and push filtering into the query rather than the application.
- Kill N+1 patterns. Replace per-row queries in a loop with a single join or a batched query. Most ORMs offer eager loading for exactly this.
- Re-measure. Run EXPLAIN ANALYZE again and confirm the plan and timing improved with realistic data. A fix that helps on 100 rows can still fail on 10 million.
Common fixes, ranked by typical impact
| Problem |
Symptom in the plan |
Fix |
Typical impact |
| Missing index |
Sequential scan with a filter |
Add index on filtered column |
Large |
| N+1 queries |
Many identical small queries |
Eager-load or batch with a join |
Large |
| Over-fetching |
SELECT * returning wide rows |
Select needed columns, paginate |
Medium |
| Bad join order |
Nested loop over big tables |
Index join keys, update statistics |
Medium |
| Stale statistics |
Row estimates far off actual |
Run ANALYZE / refresh stats |
Medium |
| Redundant index |
Slow writes, large storage |
Drop unused indexes |
Improves writes |
Common mistakes
- Indexing everything. Each index slows inserts and updates and consumes storage. Add indexes that serve queries you actually run, then drop the ones nothing uses.
- Optimizing the wrong query. A 5-second query run once a day matters less than a 50-millisecond query run a million times. Sort by total cost.
- Testing on tiny data. Plans flip at scale. The optimizer chooses a sequential scan on small tables on purpose. Test against production-sized data.
- Ignoring writes. Read-heavy tuning that cripples write throughput trades one bottleneck for another. Measure both.
FAQ
What is the fastest way to speed up a slow query?
Run EXPLAIN ANALYZE, find a sequential scan on a large filtered column, and add an index on that column. Missing indexes are the most common single cause of slow reads.
What is the N+1 query problem?
It is when code runs one query to fetch a list, then one more query per item to fetch related data. That is N+1 round trips where one batched query or join would do. It is the most common ORM performance bug.
Should I add an index to every column?
No. Indexes speed up reads but slow down writes and use storage. Index the columns your real queries filter and join on, and drop indexes nothing uses.
Does the database engine matter for performance?
Less than the query and schema. A well-indexed PostgreSQL or MySQL setup handles most workloads. Switching engines rarely fixes a query that scans the whole table.
Where to go next
Pick a database that fits your workload, understand SQL versus NoSQL trade-offs, and add observability to catch slow queries early.