After a few years on call for backend systems, you start seeing ghosts. Not the metaphorical kind, the slow-query kind. You recognize the exact shape of a query killing production before you’ve even run EXPLAIN ANALYZE. The frustrating part isn’t that these patterns exist. It’s that they all sailed through code review with clean approvals.
The eight patterns below account for roughly nine out of ten slow query incidents. The interesting thing isn’t the patterns themselves, it’s why your review process keeps letting them through.
The N+1 Query That Hides Inside Your ORM
This is the undisputed heavyweight champion of production SQL failures. It’s so common that experienced engineers have developed a kind of sixth sense for it.
You write code like this:
orders = Order.objects.all()
for order in orders:
print(order.customer.name)
The first query fetches 1,000 orders. The problem is that accessing order.customer.name triggers a separate query for each order. 1,000 orders becomes 1,001 queries. The endpoint that used to take 200ms now takes 8 seconds.
What makes this pattern so insidious is that the database itself looks healthy. As one engineer pointed out, slow query logs are “totally blind to it.” Each individual query is fast, fractions of a millisecond. The database isn’t the problem. The application is drowning in hundreds of tiny network round trips, and your monitoring tools won’t flag a single slow query.
The fix is straightforward: use a join, a fetch hint, or a batch loader. Spring Data has @EntityGraph. Django has select_related and prefetch_related. Hibernate has fetch joins. Most ORMs in most languages have some equivalent.
But the fix isn’t the interesting part. The interesting part is that the slow code looks correct. It passed your code review because nobody ran it against a realistic dataset. The team tested locally with two orders and never noticed.
The Missing Index That Should Have Been Obvious
This pattern is the easiest to recognize after the fact and the hardest to prevent upfront.
Someone writes a query that filters on a column that isn’t indexed. The query runs fine in development with 1,000 rows. It runs fine in staging with 50,000 rows. It becomes a problem in production at 2 million rows.
The diagnostic is embarrassingly simple: run EXPLAIN ANALYZE. If you see “Seq Scan” on a large table when you expected an index scan, you’ve found your problem.
The reason this pattern survives code review is that nobody actually looked at the execution plan. The query was tested. The query was correct. The query was code-reviewed. But the review focused on what the query returned, not how it returned it.
If you take one operational habit from this article, make it this: before any non-trivial query ships, look at the explain plan with realistic data volumes. Five minutes of explain plan inspection saves multiple hours of midnight debugging.
This applies especially when dealing with large-scale data operations. We recently covered insert performance cliffs caused by indexing and batching failures that show exactly how quickly a missing index turns a production database into a smoking crater.
The Function Call in the WHERE Clause
This pattern catches experienced engineers off guard more than any other.
You have an index on created_at. You write WHERE DATE(created_at) = '2026-06-01'. The query is slow. The index is ignored.
The reason is straightforward: wrapping created_at in a function call prevents the database from using the index directly. The database has to evaluate DATE(created_at) for every row before it can compare.
The fix is to rewrite the query so the function is on the other side:
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02'
Same logical result. Index used. Hundred-x speedup.
This pattern shows up in real code all the time because the intuitive way to write a “find rows from this day” query is the slow way. The non-intuitive rewrite is the fast way. Other common variants include WHERE LOWER(email) = 'user@example.com' and WHERE YEAR(created_at) = 2026.
The OFFSET Pagination That Gets Slower As You Scroll
Pagination is one of those features that works fine in development and degrades quietly in production.
A query like SELECT * FROM orders ORDER BY created_at LIMIT 50 OFFSET 100000 looks like it returns 50 rows. What the database actually does is fetch and discard 100,000 rows before returning the 50 that matter.
For the first page, this is fine. For page 2,000 with OFFSET 100,000, you have a slow query. The total time across all pages is O(N²), and this doesn’t account for the impact of large data volumes.
One experienced engineer noted that while newer PostgreSQL versions have improved performance for moderate offsets, “massive offsets are really bad in MySQL even if the query fully uses indexes.”
The fix is keyset pagination (also called cursor-based pagination):
WHERE created_at > [last_seen_value]
ORDER BY created_at
LIMIT 50
Each page references the position of the previous page. The database can use the index to jump directly to the next set of rows. It becomes a P99 nightmare only after your data grows or customers start scrolling deep.
SELECT * Pulling More Than the Query Needs
This pattern is undramatic on its own. It compounds with everything else.
A query that does SELECT * FROM users WHERE id = 123 pulls every column. If the users table has a long bio text, a JSONB column, or a TOAST-stored value, the query is doing more work than it needs.
The fix is brutal: select only what the query actually uses.
SELECT id, name, email FROM users WHERE id = 123
The query becomes smaller. The bytes shipped across the wire become fewer. The buffer cache pressure becomes lower.
The reason this matters is not the single query, it’s the cumulative cost across millions of queries. A 20 percent reduction in data shipped per query, multiplied across your total request volume, often translates to a meaningful reduction in database CPU and network throughput.
Most ORMs default to SELECT *. Most engineers don’t override that default. The cost is invisible in any single trace.
The NOT IN Clause With Nullable Columns
This pattern catches engineers who haven’t been bitten by it specifically before.
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blocked_users)
The intent is to find users who aren’t blocked. The behavior is correct if blocked_users.user_id is never null. It’s silently wrong if it contains nulls.
SQL’s three-valued logic treats NOT IN (..., NULL, ...) as UNKNOWN, which is filtered out the same way FALSE is. The result is an empty set, or a subset of what the engineer expected.
The fix is to use NOT EXISTS:
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM blocked_users b
WHERE b.user_id = u.id
)
This isn’t strictly a performance pattern, it’s a correctness pattern that masquerades as a performance pattern, because the symptoms often look like “the query is returning the wrong data, also it’s slow.”
Statistics That Are Out of Date
This pattern turns a fast query slow without anyone changing the query.
Your query has run with P99 of 50ms for six months. Suddenly it’s taking 4 seconds. The code didn’t change. The data is the same shape it’s always been.
What changed is that the table statistics the query planner uses to choose an execution plan are out of date. The planner thinks the table has 10,000 rows when it actually has 10 million. It picks a plan that would be optimal for the smaller table, which is terrible for the larger one.
The fix in PostgreSQL is ANALYZE table_name. In MySQL it’s ANALYZE TABLE. The diagnostic signal is that EXPLAIN ANALYZE shows a large gap between estimated rows and actual rows. If the planner thought 100 rows would match a filter but actually 50,000 did, your statistics are stale.
This is the pattern I see diagnosed incorrectly most often. Engineers see a slow query that used to be fast and conclude the query needs rewriting, or that an index needs adding, or that the database needs more resources. The actual fix is often a 30-second ANALYZE.
The OR Clause That Prevents Index Usage
SELECT * FROM users
WHERE email = 'x@company.com' OR phone = '+1234567890'
There’s an index on email. There’s an index on phone. The query should be able to use either index. In practice, depending on the database and the optimizer, it often uses neither. The OR forces a sequential scan.
The fix is to rewrite the query as a UNION:
SELECT * FROM users WHERE email = 'x@company.com'
UNION
SELECT * FROM users WHERE phone = '+1234567890'
Each side of the UNION can use its own index. The combined result is correct.
This pattern is the most database-specific of the eight. Some optimizers handle OR clauses with multiple indexed columns well. Others handle them poorly. The only way to know which group your database falls into is to look at the execution plan.
The One I Got Wrong Myself Last Month
I recently spent three hours debugging a slow paginated list of customer events for a dashboard. It had been running fine for months. Suddenly it was taking 6 seconds at P99.
I spent the first hour assuming stale statistics. I ran ANALYZE. No change.
I spent the second hour assuming a missing index. I added one. No change.
I spent the third hour assuming OFFSET pagination. I started designing the keyset rewrite. The on-call rotated to a colleague who solved it in about ten minutes.
The actual problem was the OR clause pattern, hidden inside an ORM. The dashboard filter logic was generating a query with an OR clause I hadn’t noticed in the generated SQL because the ORM hid the structure. The fix was a small change to the filter that forced a UNION-shaped query.
The reason I got it wrong was that I diagnosed the patterns I knew best instead of looking at the actual query. The lesson, three hours late, was the lesson I tell other engineers all the time: look at the execution plan before you start guessing.
The Pattern Across the Patterns
After writing these out, something becomes clear: these eight patterns are different on the surface, but they share something critical. Every single one can ship through code review with a clean approval and never trigger a linter warning.
They become problems only when the data grows, the load shifts, or the statistics drift.
The code review process catches most code problems. It catches almost none of the database performance problems, because the performance problems aren’t in the code. They’re in the interaction between the code and the data, and the data is invisible at review time.
The teams that get good at this build the explain-plan habit. Every non-trivial query gets an explain plan attached to the pull request. The reviewer looks at the plan, not just the code. This catches roughly seven of the eight patterns above before they ship.
The eighth pattern, stale statistics, is operational rather than developmental. It’s caught by routine ANALYZE jobs or by autovacuum tuning. Teams that run their own database should have someone responsible for autovacuum behavior. Teams that use managed databases should at least know where to look when statistics get suspicious.
The question isn’t whether you’ll hit these patterns. The question is whether you’ll catch them in a five-minute explain plan review or during a 3 AM incident call. One of those options involves a lot less caffeine.
And if you’re curious about how text-to-SQL is making this worse by generating hidden SQL performance patterns automatically, that’s a topic for next week.
