You say what, it decides how
SQL describes the rows you want. The planner estimates the cost of every way to find them and picks one. The same query can be fast or slow depending on what it has to work with.
What happens between sending a query and getting rows back?
The database turns your SQL into a plan, runs it against pages in memory, and goes to disk only for the pages it doesn't have. Writes go to a log first, which is how a crash loses nothing that was committed.
Modelled on PostgreSQL: one orders table of a million rows, one optional index, one statement at a time. Costs per page are round numbers.
No index, so the only way to find customer 42's orders is to read the whole table: 12,500 pages and a million rows, to return eight. Run it again and every page is already in memory.
ReadyAn orders table with 1,000,000 rows, no indexes. Memory is empty. Run a query.
Ready. An orders table with 1,000,000 rows, no indexes. Memory is empty. Run a query.
Run a statement
Schema and settings
Server
Statements, newest first
| # | statement | plan | pages read / hit | rows examined | ms |
|---|
Counters
0Statements
0Rows examined
0Pages from disk
0Pages from memory
0Log flushes
0Commits lost
Sheet 12 · One statement, from SQL to diskOne table, one optional indexCosts per page illustrative
Three ideas explain most of it
Everything is 8 KB pages
Tables and indexes are files of pages, and a query's cost is mostly how many it touches and whether they're already in memory. An index exists to touch fewer.
Log first, tidy up later
A change is appended to the write-ahead log before it counts. Updating the table files can wait, because after a crash the log can redo it.
Ask the database what it did
Live: EXPLAIN for the latest statement
=# (no query yet)Live: the write-ahead log
$ tail pg_wal
(empty)EXPLAIN (ANALYZE, BUFFERS) runs a query and reports the plan it chose. shared hit counts pages found in memory and read counts pages fetched from disk. Rows Removed by Filter is the work that went nowhere.
An index is a B-tree: a sorted copy of one column with pointers back to the table. Finding customer 42 means following three pages from the root to a leaf, then fetching each matching row. A million rows or a billion, it's still only a few levels deep.
A commit is a promise that the change will survive a crash. PostgreSQL keeps it by flushing the log, not the table, which is why a commit costs one fsync rather than one write for every page it changed. A checkpoint writes the dirty pages out later, in bulk.
Fast until it isn't
Fine on a laptop
A missing index is invisible with a thousand rows and painful with a million. And while the table fits in memory a scan seems fast, until the day it doesn't fit.
The index that isn't used
Wrap a column in a function, cast it, or search with a leading wildcard, and an ordinary index can't help. Check with EXPLAIN instead of assuming.
Saved, then not
Turning off synchronous commits, or running on a disk that lies about fsync, makes writes faster by making “committed” mean “probably”.
Two predictions
orders has an index on customer_id. A query asks for WHERE lower(email) = '[email protected]'. How many rows does the database examine?
An INSERT returns COMMIT, and a moment later the server loses power. The changed page was still in memory. Is the order there after restart?
What this model leaves out
Many queries at once
Real databases run many transactions at the same time. PostgreSQL keeps several versions of a row (MVCC), so readers never wait for writers, and locks only where two writes collide.
Statistics and estimates
The planner's choice is only as good as its statistics, gathered by ANALYZE. Stale estimates are a common reason a query suddenly picks a bad plan.
The operating system's cache
Below the buffer pool, the OS caches file pages too, so a “read” may never touch the disk. Here there's one layer of memory to keep the picture clear.
Replication
The same log that powers recovery can be streamed to other servers, which replay it to stay in sync. That's how replicas and point-in-time backups work.