Database performance work usually begins with someone proposing a bigger instance, and it usually ends with the discovery that one query was doing a sequential scan over four million rows on every page load. The hardware was never the constraint. The plan was.

That pattern is consistent enough to be worth stating as a default assumption. When an application is slow and the database is busy, the cause is almost always a small number of specific queries, not a general shortage of capacity, and scaling up hides the problem for exactly as long as it takes the table to grow again.

Before changing anything, measure. Optimising a query you guessed at is how teams spend a week adding indexes that make writes slower and reads no faster. Every database can tell you which statements consume the most total time. Start there, fix the top one, measure again. Two or three iterations of that usually ends the incident.

Database Performance Starts With Finding the Query

Total time matters more than worst case. A query taking two seconds and running twice a day is irrelevant. A query taking forty milliseconds and running eight thousand times a minute is your problem, and it will never appear in a slow query log with a one-second threshold.

In Postgres, the pg_stat_statements extension aggregates exactly this: calls, total time and mean time per normalised statement. Sort by total time and the offender is usually in the top three. MySQL offers similar aggregation through the performance schema.

Two things worth checking before you conclude the query itself is at fault. Is it slow every time, or only at certain hours, which points at contention rather than the plan? And is it slow in isolation, or only under concurrency, which points at locking or connection limits?

Read the Plan Instead of Guessing

Once you have the statement, ask the database how it intends to run it. Postgres exposes this through EXPLAIN , and the important variant is EXPLAIN ANALYZE, which actually executes the query and reports real timings rather than estimates.

Three things in that output carry most of the signal.

Sequential scan on a large table. The database is reading every row. On a small table that is correct and fast. On a large one it means there is no usable index for the condition you wrote, or the planner decided the index was not worth using.

A large gap between estimated and actual row counts. The planner chooses its strategy from statistics, so when its estimate is off by orders of magnitude it is choosing badly for reasons that have nothing to do with your query. Stale statistics are a common and easily fixed cause.

Time concentrated in one node. Plans are trees, and the fix belongs at whichever node consumed the time. Optimising anything else changes nothing.

The instinct to add an index the moment you see a sequential scan is often right and worth resisting for thirty seconds, because the reason the index is not being used sometimes matters more than its absence.

Why Indexes Fail to Help

An index that exists is not an index that gets used.

The condition is not sargable. Wrapping a column in a function, or applying arithmetic to it, generally prevents the index on that column from being used, because the index stores the column values and not the transformed ones. Rewriting the condition to leave the column bare usually restores it.

Column order in a composite index is wrong. A composite index supports queries that use its leading columns. An index on one column then another does not help a query that filters only on the second, and this catches people constantly.

The planner thinks a scan is cheaper. If a query returns a large fraction of the table, reading it sequentially genuinely is faster than jumping through an index. This is correct behaviour, and the fix is to return less.

Statistics are stale. After a bulk load or a large deletion, the planner’s picture of the data can be badly wrong until statistics are refreshed.

And every index costs something. Writes have to maintain it, and it consumes memory that would otherwise cache data. A table with fifteen indexes usually has several nobody needs, each making every insert slower.

The N+1 Problem Is Still the Biggest Single Cause

More application slowness comes from this than from any plan problem, and it never shows up as a slow query because each individual query is fast.

The shape is familiar. Fetch a list of a hundred records, then loop over them and fetch related data for each. The result is a hundred and one round trips where one or two would do. Every query returns in three milliseconds and the page still takes half a second, because the cost is the round trips and not the work.

Object-relational mappers make this easy to write by accident, since the related lookup looks like a property access rather than a database call. The fix is to load the related data in one query alongside the parent set, which every mature ORM supports and which most default to not doing.

Detecting it is straightforward: count queries per request. If a page issues a number of queries proportional to the number of items shown, you have found it. This is also the single highest-value thing to check when an application is slow at the edge, as our Cloudflare Hyperdrive guide covers, because round trips cost far more when the distance is greater.

Connections and Contention

Two problems that look like slowness but are not.

Connection exhaustion. Every database has a ceiling on concurrent connections, and each one costs memory. When an application opens more than the pool allows, requests queue waiting for a connection and the application appears slow while the database sits idle. The symptom is high application latency with low database CPU, and the fix is pooling rather than a larger machine.

Lock contention. A long transaction holding a lock blocks everything behind it. The usual cause is a transaction left open across work that does not need the database, such as an HTTP call to another service. Keep transactions short and confined to the database work itself.

Both cases are worth ruling out early, because both are easy to misread as a query problem and neither is fixed by an index.

What to Do in Order

Find the statements consuming the most total time. Run EXPLAIN ANALYZE on the worst one and read where the time actually goes. Check the query count per request to rule out N+1 before optimising anything. Refresh statistics before adding an index, because sometimes that is the whole fix. Then add the narrowest index that serves the condition, and measure again.

Mecanik does this as part of our software development work, and the outcome is nearly always the same: two or three queries were responsible, the fix was small, and the larger instance nobody had bought was never needed.

Related reading: API Versioning: When to Break and How Not To , How to Build a Web App in 2026 - The UK Developer’s Guide , Password Storage: What to Use in 2026 and Custom Software Development UK - The Complete Buyer’s Guide .

Frequently Asked Questions

How do I find which query is slowing my application? Sort by total time rather than worst case. A forty-millisecond query running eight thousand times a minute costs far more than a two-second query running twice a day, and it will never appear in a slow query log with a one-second threshold. In Postgres, pg_stat_statements aggregates calls and total time per statement; MySQL offers the same through the performance schema.

What should I look for in EXPLAIN ANALYZE output? Three things carry most of the signal: a sequential scan on a large table, meaning no usable index; a large gap between estimated and actual row counts, meaning the planner is working from bad statistics; and time concentrated in one node of the plan tree, which is where the fix belongs. Optimising any other node changes nothing.

Why is my index not being used? Usually one of four reasons. The condition wraps the column in a function or arithmetic, so the index no longer matches. The composite index has its columns in an order that does not support the query. The query returns a large enough fraction of the table that a scan genuinely is cheaper. Or statistics are stale after a bulk load or deletion.

What is an N+1 query problem? Fetching a list of records, then issuing a separate query for related data on each one, producing a hundred and one round trips where one or two would do. It never appears in a slow query log because each query is fast; the cost is the round trips. Detect it by counting queries per request and looking for a count proportional to items shown.

Will a bigger database server fix slow queries? Rarely, and only temporarily. When an application is slow and the database is busy, the cause is almost always a small number of specific queries rather than a shortage of capacity, so scaling up hides the problem until the table grows again. High application latency with low database CPU usually indicates connection pool exhaustion instead, which more hardware does not address.