Database Query Performance: The Five Patterns That Hurt the Most

What you actually need to know

• N plus one queries, sequential scans, unbounded results, cross joins, and lock contention.
• These five patterns dominate database performance issues.
• EXPLAIN ANALYZE reveals which pattern is hurting any given query.
• Each pattern has a known fix.
• Most teams have one or two of these dominating their issues.

The core argument

Database performance problems look diverse in symptoms but reduce to a small set of patterns in cause. The slow page that the team has been debugging for weeks is usually one of five things. The N plus one. The missing index. The unbounded result. The bad join. The lock contention. The fix is mechanical once the pattern is identified.

The discipline is to recognize the pattern from the symptom. Burst of similar queries in the log is N plus one. EXPLAIN showing sequential scan on a large table is a missing index. Memory spike on a specific endpoint is an unbounded result. Query that never returns is a cross join. Intermittent slowness with deadlock messages is lock contention.

The teams that learn these five patterns recognize most performance problems in minutes. The teams that do not spend weeks investigating each new symptom. The investment in learning the patterns is hours. The savings on debugging time compound across every performance problem the team ever faces.

The other discipline is to catch the patterns in code review. Each of the five has tells in the code. A query inside a loop that does not look batched. A WHERE clause without an indexed column. A query without LIMIT. A JOIN that is missing the ON clause. A transaction that holds the lock for too long. The reviewer who knows the patterns catches them before they reach production.

The five patterns in detail

The N plus one. A loop calls the ORM to fetch related data for each item. The total query count is one for the outer query plus N for the inner queries. The fix is to use the ORM's eager loading capability or to write a JOIN that fetches everything in one query.

The sequential scan. The query has a WHERE clause on a column without an index. The database reads every row. EXPLAIN shows the seq scan. The fix is to add the right index. The query usually drops from seconds to milliseconds.

The unbounded result. The query returns all matching rows without a LIMIT. At small data size the result is fine. At large data size the result is megabytes or gigabytes. The application slows. The memory grows. The fix is LIMIT plus pagination, or a tighter filter.

The cross join. A JOIN written without a proper ON clause, often as a typo or a misunderstanding of the data model. The result is the cartesian product of the two tables. The query never returns. The fix is to add the explicit join condition with the right columns.

The lock contention. Two transactions want the same row. One blocks the other. Under load the contention cascades. The fix is shorter transactions, accessing rows in a consistent order, and advisory locks for known hot paths.

How much does this cost

The cost of learning the patterns is hours. The cost of fixing them when found is minutes to hours per occurrence. The savings on customer facing performance is significant. The math is favorable.

Features the performance discipline must have

• Slow query log enabled with a reasonable threshold.
• pg_stat_statements or equivalent for query analysis.
• APM tool that captures query timing per endpoint.
• Code review that catches the patterns.
• A practice of running EXPLAIN ANALYZE on suspect queries.
• Performance regression tests for the critical paths.
• An owner for query performance.

Expert opinion

The teams that own database performance well learn the five patterns and recognize them quickly. The teams that do not own it spend weeks on each new performance investigation. The patterns are stable across databases and across years. The investment in learning them is hours. The return compounds across every performance investigation the team ever runs.

>

Yashveer Singh, founder of Yashveer Labs

How this played out on a real project

A client SaaS reported that their dashboard was slow. The team had spent two weeks investigating. We ran EXPLAIN ANALYZE on the dashboard's main query and the burst of follow up queries.

The main query was fine. The follow up queries were a classic N plus one. The dashboard fetched twenty items and then fetched related data for each item one at a time. Twenty one queries per dashboard load. We rewrote it with eager loading. The dashboard latency dropped from 1800 milliseconds to 140.

The same investigation found a missing index on a frequently used filter. Adding the index dropped another slow query from 400 milliseconds to 5. The two fixes took half a day. The dashboard felt fast for the first time in months.

For more on the related work, see the N+1 query problem detection prevention and refactoring and EXPLAIN ANALYZE a tour of PostgreSQLs best diagnostic tool.

Common mistakes teams make

1. Ignoring the slow query log.
2. Not running EXPLAIN ANALYZE on suspect queries.
3. Pagination only on user facing lists, not on internal queries.
4. JOIN syntax mistakes that produce cross joins.
5. Long transactions that produce contention.
6. No code review for the patterns.
7. Treating performance as solved after the first fix.
8. Treating database scaling as the answer when the patterns are the cause.

A 30 day query performance plan

1. Week one. Enable the slow query log. Identify the top offenders.
2. Week two. Run EXPLAIN ANALYZE on each. Categorize by pattern.
3. Week three. Fix the top five. Measure.
4. Week four. Establish the weekly review cadence.

For more on the related work, read the N+1 query problem detection prevention and refactoring and database indexes a practical primer for SaaS engineers. On the broader performance side, backend performance budgets how to set them is the natural next read.