A database query is slow for a short list of reasons: it has no index so the database scans the whole table, it runs many times when it should run once (the N+1 pattern), it returns or joins too much data, or it is waiting on locks under concurrent writes. You do not have to guess which. The query plan tells you how the database is running the query, and a production trace tells you how long it really takes and how often it runs. Here is how to read both, and how to fix each cause.
The short list of causes
Slow queries feel mysterious but they rarely are. Almost every slow query traces back to one of four things, and once you can name them you know what to look for:
- No index. The database reads the entire table to find your rows, which gets slower in direct proportion to how much data you have.
- Run too many times. The query itself is fine, but the N+1 pattern runs it once per item in a loop, so a single request fires hundreds of them.
- Too much data. Selecting every column, joining large tables, or returning far more rows than the page shows. The work is real even when the index exists.
- Locks and contention. Under concurrent writes the query waits for another transaction to release a lock, so it is slow only when other users are active.
The first two account for most of what people hit, especially in apps built quickly or with AI, so we spend the most time there.
The big one: a missing index
An index is a lookup structure that lets the database jump straight to the rows matching a condition instead of reading the whole table. Without one, a query like finding a user by email does a sequential scan, reading every row to check each one.1 That is why the same query is instant in development and slow in production: a sequential scan of a thousand test rows is nothing, and a sequential scan of a million real rows reads a million rows every single time.
The fix is to add an index on the column or columns you filter and join on. Markus Winand's guide to indexing makes the core point that the index is part of how you write the query, not an afterthought: the query and its supporting index are designed together.2 One caveat worth knowing: an index is not free. It speeds reads but adds a little cost to every write and takes storage, and on a small table the planner may correctly choose a sequential scan anyway because scanning is cheaper than using the index. So add indexes for the queries that need them, not everywhere.
The N+1 pattern: one slow request, many queries
The N+1 pattern is when a single request runs one query to load a list, then one more query for each item in that list. Load 200 orders, then loop over them fetching each order's customer with its own query, and you have run 201 queries instead of two. Each is fast, so no single query looks slow, but the request pays the network round trip to the database 201 times and the page hangs.
It is common because it reads naturally in code and works fine on small data, which is exactly why it slips through development and through AI-generated code. The tell is not one slow query, it is many identical fast ones in a single request. The fix is to fetch the related data in one query, using a join or the eager-loading option your ORM provides, so the whole page is one or two queries instead of hundreds. This is one of the most frequent reasons a page gets slow as its list grows, which ties directly into why is my app slow.
Too much data, and locks
The other two causes are less common but worth recognizing. Returning too much data is when the query has an index and still does real work: selecting every column when the page needs three, joining two large tables, or pulling ten thousand rows to show fifty. The fix is to ask for less, select only the columns you use, filter before you join, and page large result sets rather than loading them whole.
Lock contention is the sneaky one, because the query is only slow when other users are active. Under concurrent writes a query can wait for another transaction to commit and release its lock, so it looks fast when you test it alone and slow in production during busy periods. This is the cause most likely to be misread as "the database is overloaded" when it is really two transactions contending for the same rows. You spot it because the slowness correlates with concurrent write traffic, not with data size.
How to read the query plan with EXPLAIN
The tool that turns guessing into knowing is EXPLAIN. Every major database has it. You put EXPLAIN in front of your query and it shows the plan, the steps the database will take to run it, as a tree of nodes. EXPLAIN ANALYZE goes further and actually runs the query, showing the real time spent in each node alongside the estimates.1 This is where you confirm which of the causes above you have.
What to look for in the plan:
| What you see in the plan | What it usually means | What to do |
|---|---|---|
| Sequential scan on a large table | No usable index for this filter | Add an index on the filtered column |
| Index scan | The database is using an index | Good, this is what you want |
| Rows estimated far below rows returned | Reading and discarding lots of rows | Narrow the filter, select fewer columns |
| Most of the time in one join node | A large or unindexed join | Index the join keys, or reduce the joined set |
| The same query in the plan many times | An N+1 pattern in the calling code | Fetch the related data in one query |
Finding the slow query in production
EXPLAIN tells you why a query you already suspect is slow. The harder half is finding which query to suspect in the first place, and for that you look at production, because a query that is fast on your laptop can be slow on real data and load. Two things get you there.
The first is the slow query log, a database feature that records any query taking longer than a threshold you set. Both PostgreSQL and MySQL support it, and it hands you a ranked list of your slowest queries instead of making you guess. The second is tracing. When your app is instrumented with OpenTelemetry, each database call in a request becomes a span with its own duration, so a trace of a slow request shows the query, how long it took, and how many times it ran.3 That last part is what makes an N+1 obvious: you see the same query span repeated two hundred times in one request. We cover the wider method in log analysis and APM for small teams.
Finding it in plain language
The steps above work, and they still ask you to keep a slow query log, wire up tracing, and read plans by hand. Fixter collapses that. You drop in the SDK, which is OpenTelemetry under the hood, and your traces and metrics are stored with the database spans already captured. Then you ask about them in plain language over MCP, from Claude Code or Cursor, and Fixter finds the slow query, tells you how often it ran, and points at the missing index or the N+1 in the calling path.
Fixter does the first pass of the investigation and hands you the finding. You keep control of the change, adding the index or rewriting the query in your own editor, and your code never leaves your machine. The database spans it reads are production data, not your source.
Key takeaways
- Slow queries almost always come from a missing index, an N+1 pattern, too much data, or lock contention
- A missing index means a sequential scan that gets slower as your data grows, the most common cause
- N+1 is many fast queries in one request, not one slow query; a trace makes it obvious
- EXPLAIN and EXPLAIN ANALYZE show the plan and confirm which cause you have
- Fixter finds the slow query and the missing index in plain language over MCP, without needing your code
Frequently asked questions
Why is my database query slow?
Most often because it has no usable index, so the database scans the whole table instead of jumping to the rows it needs. The other common causes are the N+1 pattern running the query hundreds of times, returning or joining too much data, and lock contention under concurrent writes.
How do I find out why a query is slow?
Run EXPLAIN or EXPLAIN ANALYZE on the query to see its plan. A sequential scan on a large table points to a missing index, while high row counts point to returning too much data. In production, a trace of the request shows the query's real duration and how many times it ran.
Does adding an index always make a query faster?
No. An index speeds up reads that select a small fraction of a large table, but it adds cost to every write and takes storage. On a small table a sequential scan can be faster than an index, which is why the query planner sometimes ignores an index you added.
What is the N+1 query problem?
It is running one query to fetch a list, then one more query per item in that list, so displaying 200 rows fires 201 queries. Each query may be fast on its own, but hundreds of round trips in a single request make the page slow. The fix is to fetch the related data in one query.
Why is my query fast in development but slow in production?
Because production has more data and more concurrency. A query with no index is instant on a thousand test rows and slow on a million real ones, and locks and connection limits only bite when many users hit the database at once.
What is a slow query log?
It is a database feature that records queries taking longer than a threshold you set, so you can find your slowest queries without guessing. PostgreSQL and MySQL both support it, and it is the fastest way to get a list of queries worth running EXPLAIN on.