How a database runs a query
You write SELECT * FROM users WHERE email = 'asha@example.com'. On your laptop it comes
back in 2 milliseconds. In production, same code, same database version, it takes four
seconds and the on call engineer is paged. Nothing is wrong with SQL. The database simply
did something completely different with the same sentence, because it was looking at a
different table.
Once you can picture what happens between pressing Enter and getting rows back, slow queries stop being mysterious. This chapter walks that path, using Postgres and MySQL as the examples, though every relational database works much the same way.
A query is fast on your laptop and slow in production. Same code, same database version. What is the most likely difference, and how would you check?
The journey of one query#
Your SQL is a description of what you want, not instructions for how to get it. The database has to turn it into a plan and then run the plan. Step through it.
The planner decides almost everything#
For the email query there are two obvious plans. A sequential scan reads every page of the table and checks every row. An index scan walks a small sorted structure straight to the matching row. On 300 rows, the scan is often faster, because the whole table fits in a couple of pages. On 30 million rows, the scan reads gigabytes and the index reads four pages.
That is the answer to the question at the top. Your laptop has a few hundred test users, so the planner happily scans and it is fast. Production has millions, and if the index you assumed exists was never created there, or if the planner's statistics are out of date and it thinks the table is tiny, it picks the scan.
A full scan grows with the table. An index lookup grows with the number of levels in the index, which barely moves: going from 30 million to 3 billion rows adds about one level. That difference is why "add an index" fixes so many slow queries, and why a missing index only shows up once there is real data.
Ask the database what it did#
Every relational database will show you its plan. In Postgres, EXPLAIN ANALYZE runs the
query and reports what actually happened at each step.
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'asha@example.com';
Seq Scan on users (cost=0.00..689412.00 rows=1 width=310)
(actual time=3810.2..3812.7 rows=1 loops=1)
Filter: (email = 'asha@example.com'::text)
Rows Removed by Filter: 29999999
Execution Time: 3812.9 msThree things to read. Seq Scan means it read the whole table. Rows Removed by Filter:
29999999 means it looked at thirty million rows to keep one. And the execution time tells you
that this one line is your entire four seconds. After CREATE INDEX ON users (email), the
same command shows an Index Scan and a fraction of a millisecond.
MySQL has the same idea: EXPLAIN shows type: ALL for a full scan and type: ref or
const when an index is used, and EXPLAIN ANALYZE exists in MySQL 8 too.
A query plan depends on how much data there is and how it is spread. Testing against 500
rows tells you the SQL is correct and nothing about its speed. Before a query ships to a
large table, run EXPLAIN against production sized data, a staging copy or a replica.
Checkpoint#
1. Why can the same query use a full scan on your laptop and still be fast?
2. EXPLAIN shows the planner estimated 1 row but the step actually returned 2 million. What do you do first?
3. Roughly how much slower is reading a page from SSD than from the buffer pool?
- SQL says what, the planner decides how, using table statistics.
- Pages are 8 KB in Postgres, 16 KB in InnoDB, cached in the buffer pool.
- Memory is roughly 1,000 times faster than SSD for a page read.
- EXPLAIN ANALYZE before guessing. Look for Seq Scan and rows removed by filter.
A query goes through a parser, which checks the SQL and builds a tree, a planner, which estimates the cost of different ways to run it using table statistics and picks the cheapest, and an executor, which runs the plan by reading pages through the buffer pool, going to disk on a miss. Most performance problems are the planner choosing a full scan, either because an index is missing, the query cannot use the index, or the statistics are stale. A full scan grows with the table while an index lookup barely grows, so these problems only appear with real data volumes. I would always check with EXPLAIN ANALYZE rather than guess.