SELECT is a scan

In the last lesson a table became pages in a file. Now we read them.

Here is the plain truth about SELECT ... WHERE: unless something tells the engine where to look, it reads every row and throws most of them away. That is called a sequential scan, and it is the default plan for every query you write.

The loop under the query

Write the query as a loop and nothing is hidden:

result = []
for row in orders:                      # read every row, page by page
    if row.status == "cancelled":       # WHERE
        result.append((row.order_id,    # SELECT list (projection)
                       row.order_date))

Three parts, always in this order:

1. Scan. Walk the pages and produce rows.
2. Filter. Keep the rows where the WHERE condition is true.
3. Project. Keep only the columns in the SELECT list.

The SQL version is shorter, but it is the same loop:

SELECT order_id, order_date
FROM orders
WHERE status = 'cancelled'
ORDER BY order_id
LIMIT 10;

You get 10 rows back. How many did the engine look at? Count both sides of the filter:

SELECT count(*) AS scanned,
     count(*) FILTER (WHERE status = 'cancelled') AS kept
FROM orders;

1,000 rows scanned, 155 kept. The engine did 1,000 comparisons to hand you 155 rows. That ratio, rows touched over rows returned, is the number to watch. At 1,000 rows nobody cares. At 1 billion rows, 85% of the work is thrown away.

Read the plan

You do not have to guess what the engine did. EXPLAIN prints the plan. Here is what DuckDB prints for the filter query above:

┌───────────────────────────┐
│          SEQ_SCAN         │
│    ────────────────────   │
│           Table:          │
│     memory.main.orders    │
│                           │
│   Type: Sequential Scan   │
│                           │
│        Projections:       │
│          order_id         │
│         order_date        │
│                           │
│          Filters:         │
│     status='cancelled'    │
│                           │
│         ~250 rows         │
└───────────────────────────┘

SEQ_SCAN is the loop. Filters is the if. Projections is the list of columns it bothered to read. ~250 rows is the planner’s guess at how many rows survive. It guessed 250, the truth is 155. Planners guess from statistics, and the guesses are often off by 2x. That is normal.

Notice what is missing: there is no step that says “read only the cancelled rows”. Nothing in this table knows where the cancelled rows are. So the engine looks at all of them.

Three ways to touch fewer rows

Every speed-up for a scan is one of three ideas.

Read fewer columns. In a column store, Projections matters. The query above reads order_id, order_date and status, and skips customer_id entirely. That is why SELECT * is a bad habit on wide tables: you pay to read every column, then ignore most of them.

Skip whole pages. Remember the zone maps from lesson 1: each segment stores the min and max of its column. If you ask for order_date < '2024-01-01' and the segment says its minimum is 2024-02-06, the engine skips it without reading a single row. This works best when the data is roughly sorted by the column you filter on, which is why tables are often loaded in time order.

Use an index. An index is a second, sorted copy of one column with pointers back to the rows. Instead of scanning 1,000 rows to find order 412, you walk a tree of about 3 levels. We cover indexes later in the track. For now, know that they help when you keep a small fraction of rows, and stop helping when you keep most of them. If 85% of rows match, a scan is faster than 850 separate lookups.

Order of operations

SQL is written in one order and run in another. This trips up everyone once.

That is why you cannot use a column alias from SELECT inside WHERE in most databases: the alias does not exist yet when the filter runs. DuckDB is lenient and allows it, but PostgreSQL will reject it. Write portable SQL and repeat the expression.

Try the rule on a real question. Which orders were placed in December 2025 and not delivered?

SELECT order_id, order_date, status
FROM orders
WHERE order_date >= DATE '2025-12-01'
AND order_date <  DATE '2026-01-01'
AND status <> 'delivered'
ORDER BY order_date, order_id;

Use a half-open range (>= the first day, < the first day of the next month). It works for dates and timestamps alike and never misses the last day.

Ship it

Pick one question about bitshop that needs a filter. For example: returned orders in 2024, or customers who signed up in Q1 2025.

1. Write the query.
2. Above it, in a comment, write your estimate: rows scanned and rows returned.
3. Run a count(*) with a FILTER clause, like the one above, to check both numbers.
4. Run EXPLAIN on your query and find the SEQ_SCAN and its Filters line.

You have shipped it when your estimate of rows scanned is exactly right. With no index, it is always the row count of the table.