What happens when you run a SQL query
One line of SQL becomes a parse tree, a costed plan, an index scan or a full sweep, and a stream of pages through the buffer cache — every stage narrated.
Your app sends a query
Behind one line of SQL is a small factory. You send text; the database has to work out what it means, decide the fastest way to fetch it, and then actually go and get it, usually in less than a millisecond.
Connection handler
First the connection handler picks up your statement. Opening a fresh connection costs a few milliseconds of handshaking and memory, which is why real applications keep a pool of them open and hand the same sockets around all day.
Parser
The parser reads your text character by character and turns it into a tree. This is the only stage that cares about your commas and your keywords, and it is what catches a misspelled SELECT before anything touches a disk.
Binder resolves names
Next the binder gives those names meaning. The word customers is just a string until the database matches it to a real table, confirms that every column you asked for exists, and pins down the exact type of each one.
System catalog
It finds all of that in the system catalog, a set of tables that describe the other tables. A database really does store its own schema as ordinary rows, which is why asking for a list of columns is just another query.
Rewriter expands views
The rewriter then quietly edits your query. Views are pasted in as subqueries, security rules are stapled on, and constant expressions are folded away, so the plan gets built from what you meant rather than exactly what you typed.
Query planner
Now the interesting part. The planner invents many different ways to answer the same question and prices each one. With six tables there are more than seven hundred ways to order the joins alone, so it prunes hard rather than pricing every one.
Table statistics
Its prices come from statistics: how many rows a table holds, how many distinct values a column has, and histograms of how those values spread out. Stale statistics are the single most common reason a fast query suddenly turns slow.
Index scan
If a useful index exists, the planner picks an index scan. An index is a balanced tree, and even a table of one billion rows is only about five levels deep, so a lookup touches a handful of pages instead of the whole table.
Full table scan
Sometimes reading everything is faster. If your filter matches a third of the rows, hopping around an index costs more than sweeping straight through the file, so the planner deliberately chooses the full scan. That is a decision, not a failure.
Executor pipeline
The executor turns the plan into a pipeline of small operators, each pulling rows from the one below it. Nothing is built in full unless it has to be, which is how a database can stream you a result far larger than its memory.
Buffer cache
Every read goes through the buffer cache, a slab of memory holding fixed size pages of eight or sixteen kilobytes each. A page already in cache arrives in about a hundred nanoseconds. The same page fetched from a solid state drive takes roughly a thousand times longer, and from a spinning disk a hundred thousand times longer.
Data files on disk
Only on a miss does the database touch the disk. It reads whole pages, never single rows, and it writes every change to a sequential log before the data files, so a power cut in the middle of your query cannot leave a half written table.
Join and match
Joins are where plans live or die. A hash join builds a lookup table from the smaller input in memory and streams the bigger one past it, turning what looks like multiplying two tables together into a single pass over each of them.
Sort, group and limit
Finally the rows are sorted, grouped or counted. If the set outgrows its memory budget it spills to temporary files, which is why moving a limit earlier in the plan can turn a report that took a minute into one that takes a second.
Rows come back
The rows travel home over the same connection and your application sees a tidy table. Nearly all of that work is thrown away, except the cached pages and sometimes the plan itself, so the next identical query feels almost instant.
Watch it explain itself
Every step above is narrated aloud. Play it, or open it in the editor and make it yours — no account needed.