Slow SQL queries are the most common performance problem in web applications — and almost always self-inflicted. The good news is that SQL databases are remarkably transparent about what they are doing. The query plan tells you exactly where the time is going; you just have to know how to read it. This is the 2026 playbook for finding and fixing slow queries in PostgreSQL and MySQL.
What changed in 2026
- pgvector is mainstream. Hybrid queries mixing vector similarity (
<->) with traditional SQL filters are common. Vector indexes (IVFFlat, HNSW) have their own optimisation rules — most importantly, pre-filtering before ANN search.
- Query plan visualisers are better.
EXPLAIN output is still text, but tools like explain.dalibo.com, PgAdmin 4, and Datadog's query analysis visualise plans interactively. Use them.
- CTE optimisation changed. PostgreSQL 12+ inlines simple CTEs by default. If you are relying on a CTE as an "optimisation fence," add
MATERIALIZED explicitly.
- Connection pooling is non-negotiable. PgBouncer and Pgpool-II are standard in 2026 stacks; direct Postgres connections at scale cause contention that no index can fix.
Step 1: find the slow query
Do not optimise by intuition. Use your database's slow query log.
-- PostgreSQL: enable in postgresql.conf
log_min_duration_statement = 1000 -- log queries > 1 second
-- Or query pg_stat_statements for the top offenders
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
Step 2: read EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.email;
Key things to look for in the output:
| Signal |
What it means |
Seq Scan on a large table |
No usable index — add one |
rows=1 estimated vs rows=500000 actual |
Stale statistics — run ANALYZE table_name |
Hash Join vs Nested Loop |
Nested loop on large sets is slow; planner may need a hint or index |
High Buffers: shared hit=0 read=X |
Cold data fetched from disk — consider caching layer |
Sort (cost=...) on large dataset |
Missing index on ORDER BY column |
The N+1 problem
The most common production killer in ORMs:
# Bad: 1 query for users + 1 per user for orders = N+1
users = db.query(User).all()
for user in users:
orders = db.query(Order).filter(Order.user_id == user.id).all()
# → 1 + N queries
# Good: 1 query with a JOIN
users = db.query(User).options(joinedload(User.orders)).all()
# → 1 query
Detect N+1 with query logging: if you see the same query repeated with different parameter values, you have N+1. Tools like sqlalchemy-utils explain or Django Debug Toolbar will surface it automatically.
Index strategy
-- Index columns you filter on
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite index: most selective column first
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- Index for ORDER BY + LIMIT (avoid filesort)
CREATE INDEX idx_users_created_desc ON users(created_at DESC);
-- Partial index: only index the rows you actually query
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Index for text search (PostgreSQL)
CREATE INDEX idx_products_name_gin ON products USING GIN (to_tsvector('english', name));
Do not index everything. Each index adds ~10–30% overhead to INSERT/UPDATE/DELETE on that table. Add indexes only when EXPLAIN shows a Seq Scan on a large table and you have confirmed the query is slow in production.
Common slow query patterns and fixes
-- Pattern 1: LIKE with leading wildcard (can't use B-tree index)
-- Bad
WHERE name LIKE '%smith%'
-- Fix: use full-text search or trigram index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
-- Pattern 2: function on indexed column disables the index
-- Bad
WHERE LOWER(email) = 'user@example.com'
-- Fix: use a functional index
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- Pattern 3: implicit type cast
-- Bad (email is varchar, but passing an int coerces)
WHERE user_id = '42' -- in a typed column
-- Fix: match types exactly
-- Pattern 4: SELECT * with an index-only scan opportunity
-- Bad
SELECT * FROM orders WHERE status = 'pending';
-- Good: only the columns needed
SELECT id, user_id, total FROM orders WHERE status = 'pending';
Pagination and large result sets
-- Offset pagination degrades with large offsets (Postgres must scan all skipped rows)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000; -- slow at scale
-- Keyset pagination: always fast
SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 20;
How to pick an optimisation approach
- Profile first —
pg_stat_statements or slow query log.
- Read the plan —
EXPLAIN (ANALYZE, BUFFERS).
- Fix N+1 first — highest ROI, requires no schema change.
- Add a targeted index — only if
EXPLAIN shows Seq Scan on a large table.
- Rewrite the query — switch OFFSET to keyset, eliminate correlated subqueries.
- Denormalise or cache — only as a last resort after the above are exhausted.
Common mistakes
Adding indexes before profiling. You might add the wrong one and slow down writes for no benefit.
Not updating statistics. EXPLAIN estimates are based on table statistics. Run ANALYZE after large bulk loads.
Correlated subqueries in SELECT. A subquery in the SELECT list runs once per row — use a JOIN instead.
Ignoring query reuse. The same query run 1,000 times/second benefits enormously from connection pooling and prepared statements.
What to skip
- Query hints in PostgreSQL — they do not exist in Postgres (unlike MySQL). Fix the root cause instead.
- Premature denormalisation. Add indexes first; denormalise only when you have proven the indexed query is still too slow.
- Caching before fixing the query. Cache a fast query; do not cache to hide a slow one — the slow query will still fire on cache miss.
FAQ
How do I know if my query is using an index?
Run EXPLAIN (ANALYZE) and look for Index Scan or Index Only Scan. Seq Scan means no index is being used.
My EXPLAIN estimate is wildly off from actual rows. Why?
Table statistics are stale. Run ANALYZE table_name or increase default_statistics_target for skewed columns.
How many indexes is too many?
There is no hard rule, but if a table has more indexes than columns it is worth auditing. Use pg_stat_user_indexes to find indexes with zero scans and drop them.
Does the ORM slow things down?
The ORM itself is not usually the bottleneck — the queries it generates are. Always log and inspect the SQL your ORM produces in development.
Where to go next