Let us work through one case from start to finish. The setting is an imaginary orders table in PostgreSQL with 2 million rows. The “My Orders” screen loads a customer’s recent orders with a given status, newest first, showing 20. In testing it took 8 milliseconds. In production, with the full table, it takes 1.6 seconds. We want to know why, and we will use the database’s own explanation.
Step one: ask the database how it plans to run the query
Every serious relational database lets you see the execution plan. In PostgreSQL you put EXPLAIN in front of the query; MySQL has EXPLAIN too, and SQL Server shows plans in its tools. The plan is a tree of operations: how each table is read, how results are joined, sorted and filtered, with the planner’s cost estimates.
EXPLAIN ANALYZE goes further and actually runs the query, then reports real timings and real row counts beside the estimates. Real numbers are what you want. One caution: because it executes the statement, using it on something that writes data really does write the data. Wrap such tests in a transaction you roll back, or use a copy.
Step two: read from the innermost line outward
Plans are indented, and the most deeply indented lines run first. In our case the innermost line says Seq Scan on orders. A sequential scan means the database is reading every row of the table in order. Beneath it there is a line saying Rows Removed by Filter, with a figure close to 2 million. So the database read the whole table, threw away almost all of it, and kept about 40 rows that matched the customer and status. Above that sits a Sort node, because the screen wants the newest first, and a Limit node that takes the top 20.
That already tells us the shape of the problem: the work done (2 million rows read) is wildly out of proportion to the work needed (20 rows returned). That ratio is the single most useful thing to look for in any plan.
Step three: why no index?
Suppose we check and find that there is an index on the customer ID column alone. Why did the planner not use it? Three common reasons are worth testing in order.
- The index does not match the query. If the filter wraps the column in a function, for example lower-casing it or casting its type, an ordinary index on the raw column cannot be used. Matching the expression, or an expression index, is the usual cure.
- The planner believes the index will not help. Planners estimate how many rows a condition returns. If it thinks a condition matches a large share of the table, say 20 percent, reading the whole table sequentially can really be cheaper than jumping around via an index. Our case does not look like that, so we continue.
- The estimates are wrong. Compare the planner’s estimated rows with the actual rows. If it expected 500,000 matches and found 40, its statistics are out of date or too coarse. Running
ANALYZErefreshes them, and autovacuum normally does this in the background, but a table that changed heavily may lag.
In our imaginary case, the estimate and actual rows are close, the query has no function wrapping, and an index on the customer exists but the plan still shows a sequential scan. Looking at the plan from a different angle, a test with a different customer ID uses the index happily. For the heavy customer with 80,000 orders, the planner correctly decides the index is not selective enough. This is the interesting part: the “slow” customers are the ones with the most data, and the index on customer alone narrows 2 million rows to 80,000 and then still has to filter by status and sort.
Step four: build the index the query actually needs
The query filters by customer, filters by status, and sorts by creation time. A composite index on those three columns in that order, customer first, status second, created-at third, lets the database jump to the right customer and status, and read entries already in date order. It can stop after 20 rows, with no separate sort step and no filtering of unwanted rows. After creating it (on a busy production table, using the concurrent build option so writes are not blocked), the plan shows an Index Scan reading about 20 index entries, and the time drops to a couple of milliseconds. The principle behind the column order is the equality conditions first, then the sort column, and it is covered more fully in a separate article on composite indexes.
What you pay for it
Each index must be updated on every insert and on updates to its columns, and it uses disk and memory. Adding an index for every slow query ends in a table that is slow to write. Before adding one, check whether an existing index can be extended instead, and drop ones that nothing uses; database statistics views show how often each index is read.
The habit to keep
The method generalises beyond this example. Find the slow statement from logs or monitoring. Get the real plan on production-like data volumes, since a plan on 500 rows tells you nothing about 2 million. Compare rows examined with rows returned. Compare estimates with actuals. Change one thing, then read the plan again. And remember that plan details differ by database and version, so the words change, but the questions do not.
Figures and names in the examples are illustrative.
Leave a Reply