Why Your Database Query Is Slow

A query that returned instantly against a hundred test rows takes six seconds against a real table. Nothing in the code changed. What changed is the amount of work the database has to do per request, and the only way to fix that reliably is to look at the work instead of guessing at it. This post walks through reading an execution plan, deciding whether an index will help, and spotting the slow queries that no index will ever fix.

Ask the database what it is doing

Every relational database can explain its own plan. In PostgreSQL you put EXPLAIN ANALYZE in front of the query; MySQL has EXPLAIN ANALYZE too, and SQL Server shows an actual execution plan in its client. EXPLAIN on its own shows the plan the planner intends to use. Adding ANALYZE actually runs the query and reports what happened, which is the version you want.

EXPLAIN ANALYZE
SELECT id, title, published_at
FROM posts
WHERE tenant_id = 42 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

The output is a tree, read from the most indented line outwards. Three things on it matter more than the rest:

• The access method. A sequential scan reads every row in the table. An index scan jumps to the rows that match. On a small table a sequential scan is often the right choice and the planner knows it; on a large one it is usually where your seconds are going.
• Estimated rows versus actual rows. The plan shows both. When the estimate says 30 and the reality is 300,000, the planner picked its strategy on bad information, and the fix is often stale statistics rather than a missing index.
• Where the time accumulates. Each node reports its own timing and how many times it ran. A node that costs two milliseconds but runs four thousand times is the problem, not the node with the largest total at the top.

Run the query twice and use the second result. The first run pays for reading pages from disk into memory, which makes a fast query look slow for reasons that have nothing to do with its shape.

What an index actually is

An index is a second, sorted structure that holds the indexed columns plus a pointer back to the row. Because it is sorted, the database can find a value by narrowing the range instead of reading everything. That is the whole trick, and it explains the rules that follow.

Sorting is also why column order matters in a multi-column index. An index on (tenant_id, status, published_at) is sorted by tenant first, then by status within a tenant, then by date within that. It can serve a query that filters on tenant, or on tenant and status, or on all three. It cannot efficiently serve a query that only filters on status, for the same reason a phone book sorted by surname is no help when all you know is a first name.

The other consequence is that an index only helps when the database can compare the stored value directly. Wrap the column in a function and the sorted order no longer applies:

-- cannot use an index on created_at
WHERE DATE(created_at) = '2026-09-16'

-- can
WHERE created_at >= '2026-09-16' AND created_at < '2026-09-17'

If you genuinely need the function, most databases let you index the expression itself, so an index on lower(email) makes WHERE lower(email) = ... fast.

The four shapes that usually need an index

• Foreign keys used in joins. Many databases index the primary key automatically and the referencing column not at all. Joining a large child table on an unindexed foreign key is one of the most common causes of a slow report.
• Columns you filter on constantly. A tenant identifier, a status, an owner. If it appears in nearly every WHERE clause, it belongs at the front of an index.
• Sorting with a limit. ORDER BY published_at DESC LIMIT 20 can walk an index backwards and stop after twenty rows. Without one, the database sorts the entire matching set to hand you twenty rows.
• Uniqueness you rely on. A unique index enforces the rule and speeds up the lookup at the same time.

Combine the first three where they occur together. For the example query, an index on (tenant_id, status, published_at DESC) covers the filter and the sort in one structure, which is why the plan can stop reading after twenty rows.

When an index makes things worse

Every index is a copy that has to be kept current. Each insert, update and delete on an indexed column writes to the index too, so a table with a dozen indexes pays for all of them on every write. Indexes also take disk space and memory, and memory used by an index nobody reads is memory unavailable to the ones that matter.

Indexes also do little for columns with very few distinct values. An index on a boolean that is true for most rows will usually be ignored by the planner, correctly: reading the table straight through is cheaper than bouncing between index and table for half the rows. The exception is a partial index, which covers only the rare case, such as rows where the status is failed.

Before adding one, check whether it already exists. Most databases have a view listing indexes and how often each has been used. Unused indexes are pure cost, and dropping them is one of the few performance changes that is also a simplification.

The slow queries an index cannot fix

Some queries are slow for reasons the plan will not flag as a missing index.

• The N+1 pattern. One query fetches fifty rows, then the code loops and runs a second query per row. Each of the fifty-one queries is fast and the page still takes a second. You see it in application logs rather than in a single plan: the same statement repeating with different parameters.
• Fetching columns nobody uses. Selecting every column from a table with a large text or JSON column moves far more data across the connection than the page needs.
• Unbounded result sets. A query with no LIMIT behind a list view works until the table grows. Pagination that counts every row to show a page number has the same problem in a quieter form.
• Waiting rather than working. If the plan says the query is fast but the request is slow, the time may be going to connection setup, a saturated connection pool, or a lock held by another transaction. The database usually has a view showing which sessions are waiting and what they are waiting for.

A routine that works

Measure first: find which query is actually slow, using your database's statement statistics rather than intuition. Run EXPLAIN ANALYZE on it against realistic data, since a plan taken from a near-empty development database tells you nothing. Change one thing, re-run the plan, and keep the change only if the plan itself improved, not just the wall clock on one attempt. Then check the write path: if the table takes heavy inserts, confirm they did not get slower.

Done in that order, database performance stops being folklore about which query style is fast. You have a measurement, a plan that explains it, and a change you can defend.