How to find and fix slow SQL queries — reading EXPLAIN plans, using indexes effectively, avoiding N+1 queries, and the common mistakes that quietly kill database performance.
A slow application is very often a slow-query problem in disguise. As data grows, a query that was instant at launch can crawl, dragging down user experience and inflating server costs. The good news: query optimization often yields dramatic, order-of-magnitude improvements from small changes. Here are the techniques that matter most, applicable to PostgreSQL, MySQL, and most relational databases.
The optimization techniques
Read the execution plan Before optimizing, understand what the database is actually doing. EXPLAIN (and EXPLAIN ANALYZE) shows the query plan — whether it's using indexes or doing a full table scan. Optimization without reading the plan is guessing.
Use indexes effectively The most common cause of slow queries is a missing index on a column used in WHERE, JOIN, or ORDER BY. Add the right index and a query can go from seconds to milliseconds. Confirm the index is actually used via the plan — see our [database design guide](/blog/database-design-best-practices) for indexing strategy.
Rewrite inefficient queries Sometimes the fix is the query itself: avoid SELECT * (fetch only the columns you need), replace correlated subqueries with joins where appropriate, and filter as early as possible.
Kill N+1 queries A classic application-layer killer: fetching a list, then firing one query per row. Batch these into a single query (or use your ORM's eager-loading). N+1 is one of the most common performance bugs in real apps.
Common performance mistakes
- Missing (or unused) indexes on filtered/joined columns.
- SELECT * pulling far more data than needed.
- Functions on indexed columns in WHERE clauses, which prevent the index being used.
- Type mismatches in comparisons that force scans.
- Unnecessary joins and subqueries that bloat the plan.
When it's not the query
If queries are optimized and it's still slow, look at connection pooling, caching frequently-read data (Redis), read replicas for read-heavy loads, and hardware. But optimize the queries first — it's usually the cheapest, biggest win.
Frequently asked questions
How do I find my slowest queries? Enable the slow-query log (or use pg_stat_statements in PostgreSQL) to identify the worst offenders, then run EXPLAIN ANALYZE on them.
Will adding indexes always speed things up? Reads, yes; but every index slows writes and uses space. Add indexes that serve real query patterns, and remove unused ones.
What's an N+1 query problem? Running one query to get a list, then one additional query per item — turning one database call into hundreds. Batch or eager-load to fix it.
Fighting a slow application or database? Talk to us — performance tuning is part of how we build and maintain software.