Your Database Will Tell You Exactly Why It Is Slow. Most People Never Ask.
Anyone who has used a map app in a busy city knows two numbers. One is the time it promised when you set off. The other is the time the trip really took. When those two numbers are close, the app understood the road. When they are miles apart, the app had the wrong picture, and every decision it made on the way was based on that wrong picture.
Databases work in the same way.
When you send a query, the database does not just run it. First it plans: which table to read first, whether to use an index, how to join things together. That plan is a route, and the database picks it using a set of guesses about how much data is where. Then it follows the route. If the guesses were good, the query is quick. If they were bad, you get the database equivalent of a taxi taking the long way round.
The good news is that every serious database will show you the route, and most will also show you how the trip actually went. Almost nobody looks. They add an index by instinct, or throw more hardware at the problem, or blame the network. Today is about reading the plan, which is the single most useful skill in database performance work.
In a previous post here, we looked at indexes, the cabinet of cards that lets a database go straight to what it needs. A query plan is how you prove the database is actually using that cabinet.
1. Ask for the plan
Each database has its own way of asking. Here is the same question from our previous post here (this customer’s open orders, newest 20) in all four.
PostgreSQL uses the EXPLAIN command [S1][S2]:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = ‘open’
ORDER BY created_at DESC
LIMIT 20;
SQL Server shows plans in SQL Server Management Studio. Choose Include Actual Execution Plan on the Query menu and run the query [S3]. If you prefer code, SET STATISTICS XML ON returns the plan information for each statement after it runs [S3]. You need the SHOWPLAN permission on every database the query touches [S3].
SET STATISTICS IO ON;
SELECT TOP (20) id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = ‘open’
ORDER BY created_at DESC;
Oracle gives you two routes. EXPLAIN PLAN followed by DBMS_XPLAN.DISPLAY shows the estimated plan [S7]. For the plan that really ran, add a GATHER_PLAN_STATISTICS hint to the query and then call DISPLAY_CURSOR [S7]
SELECT /*+ GATHER_PLAN_STATISTICS */ id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = ‘open’
ORDER BY created_at DESC
FETCH FIRST 20 ROWS ONLY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => ‘ALLSTATS LAST’));
MongoDB has the explain method, and the executionStats mode runs the query and reports what happened [S8]
db.orders.find({ customer_id: 42, status: “open” })
.sort({ created_at: -1 })
.limit(20)
.explain(“executionStats”)
A safety rule that saves careers. In PostgreSQL, plain EXPLAIN only plans. Adding ANALYZE makes the statement actually execute, and the manual is blunt about it: any side effects will happen as usual [S1][S2]. That is fine for a SELECT. For an INSERT, UPDATE, or DELETE, the manual’s advice is to wrap it so the change is undone [S2]
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = ‘closed’ WHERE customer_id = 42;
ROLLBACK;
Remember, even a rolled back statement still did the work, so be careful running it against a busy production database.
2. Spot the scan type
Every plan, in every database, has a line that says how the table is read. This is the line that tells you whether your index is being used.
PostgreSQL’s manual uses an example table to show three ways to read it. A sequential scan reads the whole table, and when a query has no WHERE clause, it is the only sensible choice [S1]. A bitmap scan is used for moderately selective conditions [S1]. An index scan is used for very selective lookups, such as fetching a single row [S1]. The manual shows the difference in the plan lines. These two come from two different queries on the same table, so do not read them as a before and after:
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
Get Rufus Kehinde’s stories in your inbox
Join Medium for free to get updates from this writer.
SQL Server uses its own names. A Table Scan retrieves all rows from a table without a clustered index, and a Clustered Index Scan reads the whole clustered index [S5]. An Index Seek uses the seeking ability of an index to process only the rows that satisfy its seek predicate [S5]. The operator to watch for is the Key Lookup: when the index found the rows but did not hold every column the query needs, SQL Server goes back to the clustered index to fetch the rest, and Microsoft says a Key Lookup with a high number of executions relative to the table size might benefit from a covering index [S5]. That is the covering index idea from a previous post here.
MongoDB reduces it to two words. COLLSCAN means the server scanned the entire collection document by document, which Mongo’s documentation says is generally expensive and can result in slow queries. IXSCAN means an index was used [S8].
Here is the best example I know of, taken from MongoDB’s own documentation [S8]. A query for movies released between 2000 and 2005 returns 3,809 documents:
With no index, MongoDB examined all 21,349 documents to find those 3,809.
After adding an index on year, it examined only 3,809 index keys and 3,809 documents.
For a second query that also filtered on the rating (235 matches), a compound index with the rating first meant it examined exactly 235 keys and 235 documents.
When the number of things examined matches the number returned, the database did no wasted work. That ratio is worth memorising.
3. Compare the guess with reality
Back to the map app. The promised time and the real time are the two numbers that matter, and so it is with plans. A plan shows what the database expected, and the execution shows what it found.
PostgreSQL’s manual says the thing that’s usually most important to look for is whether the estimated row counts are reasonably close to reality [S1]. In the output, the estimate appears as rows inside the first set of brackets, and the real number appears as rows inside the actual section [S1]. There is a third number worth understanding, called loops: how many times that step ran. The timings and row counts shown are averages per run, so you multiply by loops to get the true total [S1].
Oracle makes the comparison explicit. With the statistics hint, the Rows column is replaced by E-Rows (estimated) and A-Rows (actual) side by side [S7]. SQL Server’s actual plan carries the real row counts, and its Analyze Actual Execution Plan feature exists to make it easier to find scenarios of inaccurate cardinality estimation (that is the word for a wrong row count guess) and to suggest mitigations [S4]. MongoDB gives you the pair directly: nReturned, and the number of documents and keys examined [S8].
What do you do when the guess is far off?
The usual cause is stale information. A database makes its estimates from statistics it collected about your data. PostgreSQL’s ANALYZE command refreshes them, and the manual notes that its estimates come from a random sample and can change a little after each ANALYZE [S1]. If a table has changed a lot since statistics were last gathered, the database is planning a route using an old map. Refresh the map first, then look at the plan again.
4. Count the work, not just the seconds
Time is a noisy measure. The same query can be fast the second time simply because the data is now in memory. So alongside time, look at how much work was done.
PostgreSQL’s BUFFERS option reports how many blocks were found already in cache (a hit means a read was avoided because the block was already in memory), and how many had to be read in [S2]. In current versions, buffer information is included automatically when you use ANALYZE [S2]. SQL Server’s SET STATISTICS IO reports scan count, logical reads (pages read from the data cache), and physical reads (pages read from disk) [S6]. Oracle’s plan with runtime statistics also includes Starts, A-Time, and Buffers columns [S7]. And MongoDB’s equivalent is the examined versus returned pair from step two [S8].
A rule of thumb that works on all four: if the database touches thousands of rows or pages to hand back twenty, something is wrong, and it is usually a missing index, the wrong column order, or a function wrapped around a column, all topics from a previous post here.
5. Know the traps
Cost is not time. PostgreSQL’s cost numbers are in arbitrary units, with the cost of reading one page sequentially as the baseline of 1.0 [S1]. They are for comparing plans, not for predicting seconds. The same manual says the actual time figure, in milliseconds, is a different thing and not comparable to cost units [S1].
Small tables lie. The manual warns that a plan that looks expensive on a tiny test table may not reflect production behaviour [S1]. A table with a hundred rows is often faster to scan than to index, so the database will happily choose the scan. Test on realistic amounts of data.
LIMIT can make numbers look smaller than estimates. The manual explains that when a query has a LIMIT, the actual row counts and times for some steps can look lower than the estimates, and that this is a display artifact, not an estimation error [S1].
A plan shows one query, not the whole page. If a page runs 10,000 small queries, each plan will look fine, and the page will still crawl. That is the N+1 problem from a previous post here. Plans find slow queries. Logs and traces find slow pages.
A routine you can repeat
1. Pick the slow query and find the exact version your application sends, with real values.
2. Get the plan with runtime statistics, using the safety rule if it changes data.
3. Check the scan type, then compare estimated against actual rows.
4. Check the work done, meaning blocks, reads, or documents examined versus returned.
5. Change one thing, such as an index or refreshed statistics, and read the plan again.
Step five is the one people skip. A plan before and after is the evidence that your change did what you think. Without it, you are guessing, and with the right index in the wrong order, a guess can feel like a fix.
The common thread
Databases are not mysterious. They make a plan, they record how it went, and they will show you both if you ask. The engineers who look fast at a problem are rarely smarter than the rest. They simply read the plan before they start changing things.
Sources
S1: PostgreSQL manual, Using EXPLAIN. https://www.postgresql.org/docs/current/using-explain.html
S2: PostgreSQL manual, EXPLAIN command reference. https://www.postgresql.org/docs/current/sql-explain.html
S3: Microsoft Learn, Display an Actual Execution Plan. https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver17
S4: Microsoft Learn, Analyze an actual execution plan. https://learn.microsoft.com/en-us/sql/relational-databases/performance/analyze-an-actual-execution-plan?view=sql-server-ver17
S5: Microsoft Learn, Logical and Physical Showplan Operator Reference. https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-ver17
S6: Microsoft Learn, SET STATISTICS IO. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql?view=sql-server-ver17
S7: ORACLE-BASE, DBMS_XPLAN: Display Execution Plans. https://oracle-base.com/articles/9i/dbms_xplan
S8: MongoDB manual, Interpret Explain Plan Results. https://www.mongodb.com/docs/manual/tutorial/analyze-query-plan/
