Sequential Scan, Page by Page
With no index, WHERE email = ? is a loop over every page of the table file: fetch page, test each row, fetch the next page. It is linear, it cannot stop early without a uniqueness guarantee, and it is the cheapest I/O per page the storage layer can do — which is exactly why the planner keeps choosing it.
Why this exists
The mechanism as the answer to a problem — read this before the name.
- Problem
Find the one row where
email = 'alice@example.com'in a table of 10 million rows stored as 8 KB pages on disk.↓ - Naive solution
Ask the storage layer for page 1, compare the email on every row, ask for page 2, and continue until the file ends. This is the sequential scan; it is the only thing the engine can do when nothing tells it where the row is.
↓ - Why it breaks
The work is proportional to the table. 10 million rows ≈ 125,000 pages ≈ 1 GB read for one row. Worse, it cannot stop at the first match: without a UNIQUE constraint a second Alice may sit on the last page.
↓ - Better idea
Notice what the scan wastes: it reads rows that could have been ruled out by a single comparison if the data were sorted. A sorted, separate copy of the column would let us skip almost everything.
↓ - Internal mechanism
That separate sorted copy is an index; the next lesson derives it. But the scan is not a mistake — it reads pages in file order, so the OS prefetches and each page costs roughly one sequential I/O unit, which is 4× cheaper than a random one.
↓ - Trade-offs
Cheap per page, expensive in pages. It wins when the query needs a large fraction of the table (an index would touch the same pages, randomly) and loses badly when it needs one row out of millions.
↓ - Real database
PostgreSQL shows
Seq Scanin EXPLAIN, usesseq_page_cost = 1.0versusrandom_page_cost = 4.0, and lets concurrent scans share one pass through the pages with synchronized scans.
Choose your depth
The same mechanism at four altitudes. Start where you are; come back deeper.
A sequential scan is the database version of Linear Search: look at every element until you have looked at all of them. With no index the executor has no idea which page holds alice@example.com, so it reads page 1, page 2, page 3 … and applies the WHERE test to every row on each.
The cost grows with the table. A query that took 2 ms at launch takes 2 s once the table has a thousand times more rows — and nothing in the query changed.
The loop, page by page
The executor does not see rows; it sees a file of fixed-size pages (see Pages: The Unit of Everything). A sequential scan is a loop over block numbers: fetch the page, walk its slot directory, test each row against the predicate, emit the matches, move to the next block. There is no data structure involved — the only thing being used is the order in which pages happen to lie in the file.
The trace below is a 10-million-row users table at ~100 rows per 8 KB page. The row we want happens to be on page 41,207. The scan does not know that, and after finding it, it does not know that there is not another one.
PAGE 1 (block 0) 100 rows compare × 100 0 matches PAGE 2 (block 1) 100 rows compare × 100 0 matches PAGE 3 (block 2) 100 rows compare × 100 0 matches ... PAGE 41207 (block 41206) 100 rows compare × 100 1 match ← slot 37: alice@example.com PAGE 41208 ... 0 matches (cannot stop: no UNIQUE guarantee) ... PAGE 100000 (block 99999) 100 rows compare × 100 0 matches pages read 100,000 (sequential, ~1 unit each) rows compared 10,000,000 rows returned 1 plan Seq Scan on users Filter: (email = '...') Rows Removed by Filter: 9999999
Why it is sequential I/O, and why that is cheap
Reading block 0, then 1, then 2 is the friendliest access pattern a storage device knows. The OS read-ahead notices the pattern and fetches blocks before they are asked for; an SSD serves consecutive blocks at its full bandwidth; a spinning disk does not move its head. The planner encodes this as seq_page_cost = 1.0 against random_page_cost = 4.0: a page reached by jumping around costs four times a page reached in order.
This is the number that decides the break-even in The Planner: Enumerating Ways to Answer and Cost-Based Optimization. An index does not make reading pages cheaper; it makes reading *fewer* pages possible — at random-read prices. When the predicate matches a large slice of the table, fewer-but-random loses to all-but-sequential, and the planner correctly picks the scan.
| Access | Pattern | Planner cost | Why |
|---|---|---|---|
| Sequential scan | block 0, 1, 2, … | 1.0 per page | read-ahead, streaming bandwidth, no seeks |
| Index → heap fetch | block 41206, 7, 88120, … | 4.0 per page | each row may be on a different page, no prefetch |
| Bitmap heap scan | sorted block list | between the two | pages fetched in block order after collecting TIDs |
When it may stop early
The scan returns every row that satisfies the predicate. It may stop early only when something proves there are no more matches: a UNIQUE index or constraint on email, a primary key, or a LIMIT that the query itself asked for. Absent those, a duplicate alice@example.com on the last page is a legal state of the table, and the engine must find it.
The interactive has a "stop at first match" toggle for exactly this reason. Turn it on without the guarantee and watch the second match go unreported — that is a wrong answer, not an optimisation. It also explains a real-world effect: adding a UNIQUE constraint can make a query faster even when the planner chooses a scan, because the executor now knows it can stop.
What the practical layer sees
In EXPLAIN ANALYZE the node reads Seq Scan on users … Rows Removed by Filter: 9999999. That number — rows read and thrown away — is the direct measure of the loop above; see Reading EXPLAIN ANALYZE. Buffers: shared read=100000 is the page count. Both grow linearly with the table, which is why Why Is This Query Slow? Indexes frames the same query as "fine at launch, the slowest thing in the system two years later".
Nothing about this loop uses the contents of the data to skip work. The next lesson, The Index, Derived from First Principles, asks the only question that can change the picture: how can we avoid reading every page?
1EXPLAIN (ANALYZE, BUFFERS)2SELECT * FROM users WHERE email = 'alice@example.com';3 4-- Seq Scan on users (cost=0.00..225000.00 rows=1 width=80)5-- (actual time=0.031..1840.22 rows=1 loops=1)6-- Filter: (email = 'alice@example.com'::text)7-- Rows Removed by Filter: 99999998-- Buffers: shared hit=32 read=99968Key points
- A sequential scan is linear search over pages: fetch each page in file order and test every row on it.
- The cost is measured in pages, not rows; 10 million rows is ~100,000 page fetches and the comparisons are nearly free by comparison.
- It cannot stop after the first match unless a UNIQUE guarantee or a LIMIT proves there are no more.
- Sequential I/O is ~4× cheaper per page than random I/O — this is the number behind the planner's index-vs-scan break-even.
Rows Removed by Filterin the plan is the direct measure of wasted work.
Sequential scan, page by page
When to use — and when not
- This access path fits when the query needs a large fraction of the table — an index would touch the same pages at random-read prices.
- Small tables of a few pages, where the whole table is one or two reads anyway.
- Aggregates and reports that genuinely read everything.
- This access path is wrong when the predicate keeps a few rows out of millions — every page read is wasted.
- Latency-sensitive point lookups by a selective key.
- Tables that no longer fit in memory, where the scan evicts the working set of everything else.
Failure modes
- A point lookup with no index, discovered when the table grew; see Why Is This Query Slow? Indexes.
- A scan on a hot table that thrashes the buffer pool for everyone else.
- Assuming the engine stops at the first match; it does not without a uniqueness guarantee.
- Reading a plan's
Seq Scanas "wrong" when the predicate matches 40% of the rows — the planner is right.
Where you meet this
Back up to the practical layer, and across to the rest of Engineer Atlas.