Table Scan

A table scan is the database version of reading every page of a book when you only needed one paragraph.

Question: What is a table scan in SQL?

Answer: A table scan means the database reads every row, or every data page, in a table to find matching rows. It usually happens when there is no useful index, the filter matches many rows, or the predicate is written in a way the optimizer cannot use an index. It is not always bad: for small tables or broad queries, scanning can be faster than doing many random index lookups.

Interview-Ready Answer: I’d say a table scan is when the engine checks the whole table instead of jumping to rows through an index. That usually means either the index is missing, the condition is not selective, or the query makes the column hard to search, like wrapping it in a function. It is not automatically a problem, because for small tables or queries that need a big chunk of the data, a sequential scan can be the cheapest plan.

Detailed Explanation:

What a table scan is

A table scan is a sequential read of the table’s data pages. Different engines label the same idea differently: PostgreSQL often shows Seq Scan, SQL Server may show Table Scan or Clustered Index Scan, and MySQL often uses ALL in the plan. The key idea is simple: the engine starts at one end and checks row by row instead of jumping straight to matching rows through an index.

How it works under the hood

1. The SQL parser turns your query into an internal tree, and the optimizer notices which columns appear in the WHERE clause.
2. The optimizer checks statistics, which are summary numbers about the data. Selectivity means the fraction of rows expected to match; a low-selectivity filter matches lots of rows, so a scan may be cheaper than an index lookup.
3. The cost model compares plans. It asks: is it cheaper to read the table in order, or to jump through an index and then fetch the matching rows one by one?
4. The executor reads pages from disk or from the buffer pool. A buffer pool is the memory cache used to keep recently used pages so the engine does not hit storage every time.
5. Each row is tested against the filter. Rows that do not match are discarded; rows that match are returned to the next step in the plan.
6. If the query needs most columns from the row, a scan can be simpler than using an index and then doing many extra table lookups.

A useful mental model is this: the optimizer is choosing between read a lot in order and do many targeted jumps. Sequential I/O means nearby pages are read together, which is friendly to disks and SSDs. If a row averages about 200 bytes and the page size is 8 KB, one page holds roughly 40 rows; a million-row table can therefore mean tens of thousands of page reads.

When the optimizer chooses a scan

• No useful index: the filtered column is not indexed, so the engine has no shortcut.
• Low selectivity: the query matches a large share of the table, so an index would still touch too many rows.
• Wrapped column: functions like DATE(created_at) or LOWER(email) can hide the raw column from a normal index.
• Implicit conversion: if types do not match cleanly, the engine may be unable to use the index efficiently.
• Stale statistics: if the optimizer thinks the filter is rare when it is actually common, it may choose the wrong plan.

One important term is sargable, which means the predicate can use an index efficiently. A predicate like created_at >= '2026-01-01' is usually sargable, but DATE(created_at) = '2026-01-01' often is not unless you build an expression index. A covering index is another helpful term: it contains every column the query needs, so the engine can answer from the index alone without touching the table.

Table scan vs alternatives

That table shows the main trade-off: a scan is not automatically bad. If you need 30% to 80% of the rows, a scan can be faster than many random index lookups. On the other hand, if you only need a few rows out of millions, a seek is usually much better. In many engines, a scan is also easy to parallelize across multiple workers for very large tables.

Performance and edge cases

Time complexity for the scan itself is roughly O(N), because the engine checks each row or page once. Extra space is usually O(1) for the scan operator itself, aside from normal engine caches and whatever operators come before or after it. On a warm cache, a scan may become CPU-bound instead of disk-bound; on a cold cache, it can be mostly about I/O.

Common edge cases that force scans include LIKE '%term' with a leading wildcard, arithmetic on the column such as amount + 0, and boolean or status columns with very low cardinality, where an index may not help much. The exact plan names and thresholds differ by engine and version, but the idea does not change: the optimizer picks the cheapest path based on statistics, row estimates, and the amount of data it expects to return.

Real-World Example: In an e-commerce checkout service, a new reporting endpoint filtered millions of orders by day using DATE(created_at). That looked harmless, but it forced a scan because the engine could not use the normal index on created_at. The symptom was ugly: the database disk queue went high, p95 latency jumped from under 200 ms to several seconds, and users saw the dashboard spin or checkout requests time out during peak traffic.

The incident showed up in logs as slow queries with plans like Seq Scan or Table Scan, plus CPU that was not even the main bottleneck; storage was. The fix was to rewrite the filter as a date range on the raw column and add the right index. After that, the engine could jump to the day’s rows instead of reading the whole table, and the service recovered immediately.

Follow-up & Tricky Questions:

• How do you tell if a query is doing a table scan? Use EXPLAIN or EXPLAIN ANALYZE and look for labels like Seq Scan, Table Scan, Clustered Index Scan, or ALL. Then compare estimated rows to actual rows to see whether the plan choice was reasonable.
• Is a table scan always bad? No. If the table is small or the query needs a big percentage of the rows, a scan can be faster than many random index lookups.
• Why would the optimizer choose a scan if an index exists? If the predicate is low-selectivity, the statistics are stale, or the index is not covering, the scan can have lower total cost than a seek plus many row fetches.
• How do you reduce scans? Add the right index, rewrite predicates so the column stays searchable, avoid functions on indexed columns, and refresh statistics so the optimizer sees the real data shape.
• What is the difference between table scan and index scan? A table scan reads the table data pages directly, while an index scan walks the index pages. An index scan can still touch a lot of rows, but it may be smaller than the full table and sometimes can answer the query without visiting the table at all.
• If a query uses an index, can it still be a scan? Yes. An index scan reads many or all index pages in order, so it is still a scan; it is just scanning the index instead of the table.
• Does SELECT * cause a table scan? Not by itself. The scan decision comes from the filter and the data distribution; SELECT * mainly affects how much data is returned, not whether the engine can search efficiently.
• Why can a scan be faster than a seek on a big result set? Because scans read pages sequentially, which is friendlier to the storage layer. If the query needs many rows anyway, the random I/O from an index plan can cost more than just reading the table in order.
• Can a function or cast really block an index? Yes. If the engine must compute a new value for every row first, the normal index on the raw column may no longer be usable, so you get a scan unless you add an expression index or rewrite the predicate.

Common Mistakes:

• Thinking every scan is a bug. Correction: scans are sometimes the best plan, especially for small tables or wide filters.
• Adding an index and stopping there. Correction: if the predicate wraps the column in a function or a cast, the index may still not be usable.
• Ignoring statistics. Correction: bad stats can make the optimizer misjudge selectivity and pick the wrong access path.
• Chasing scans without measuring. Correction: always check actual rows, read counts, and latency before changing schema.

Memory Hook: Think of the database as choosing between reading the whole phone book and using the index at the back. If you need one name, use the index; if you need most of the book, just read it cover to cover.

Cheat Sheet:

• Table scan = read all rows or pages in a table.
• It happens when there is no useful index, or when many rows match.
• Functions, casts, and leading wildcards often block index use.
• Scans are O(N) and usually use little extra memory.
• Not all scans are bad; small tables and broad queries often prefer them.
• Use EXPLAIN to confirm the real plan instead of guessing.

Practice Tasks:

• Create a table with a few thousand rows and run EXPLAIN QUERY PLAN before and after adding an index.
• Rewrite a query that uses DATE(column) or LOWER(column) so the column stays searchable.
• Compare a small-table query and a large-table query to see when a scan is actually the cheapest choice.