Does scan always mean reading every row?

No. The operator and access path determine what is visited, and some executions stop early.

Is an index scan always faster?

No. The answer depends on selectivity, layout, caching, and the rest of the query.

Can a scan return fewer rows than it inspects?

Yes. Filters can discard candidates before the operator emits them to its parent.

AI Glossary / Data engineering and architecture / Intermediate

Scan Operation

Definition

A scan operation is a database execution operation that obtains rows or other records from a data source for use by a query. A table scan may visit the table's stored data sequentially, while an index scan uses an index access path. Scans can apply conditions and emit only qualifying rows. The word scan describes part of query execution, not necessarily a complete read of every record or a security inspection.

What it means

A query asks for a result, but the database must obtain the underlying rows somehow. Scan operators perform that retrieval work and feed rows to later operations such as joins, sorting, and aggregation. Understanding the scan helps explain why a query returning a few rows might still inspect a great deal of data. Conversely, returning many rows may make a broad sequential scan a sensible choice, even when an index exists.

How it works

The optimizer compares available access paths using the query conditions, indexes, statistics, and its cost model. A sequential scan visits table data and evaluates applicable filters. An index scan navigates index entries and may fetch corresponding table rows. Some systems offer bitmap scans, index-only scans, parallel scans, and scans over non-table sources. These mechanisms have different costs and visibility requirements. A parent operator can sometimes stop requesting rows early, so the executed scan need not always run to completion.

Three practical examples

Customer lookup: a suitable identifier index may provide a short access path for one customer's record.
Monthly reporting: a report needing most transactions may use a sequential or partition-limited scan because widespread indexed lookups would add overhead.
AI feature preparation: a query scans relevant event records, filters unusable rows, and passes the retained values to aggregation before the feature table is built.

Think of it this way

Think of finding entries in a register by reading pages in sequence or consulting its index first. Both approaches retrieve information, but their relative effort depends on how much is needed. The analogy is limited because database scans also account for caching, concurrent versions, parallel work, storage layout, and optimizer estimates.

Worked example

A toy table has 80,000 event rows, of which 2,000 have Status = 'review'. A sequential scan applying that filter may inspect all 80,000 rows while emitting 2,000. The emitted count does not mean only 2,000 were examined. Suppose an index identifies the same 2,000 candidates, but the query also requires a separate check that retains 600. The access path may still retrieve 2,000 candidates before emitting 600. Whether that is faster depends on page locations, cache state, row width, and other costs. If an execution plan estimates 500 output rows and actually emits 600, that difference concerns estimation accuracy, not automatically wrong query results. These numbers are explanatory assumptions, not benchmark measurements.

Illustrative case study

Fictional example: Meadow Support sees a “Seq Scan” label on its dashboard query and initially assumes the database is malfunctioning. The report intentionally includes almost every support ticket. The analyst compares representative plans and observes the distinction between rows visited and rows returned. The team keeps the evidence with its tuning notes rather than requiring every query to use an index. This invented case illustrates diagnosis; it makes no claim about a measured speed gain.

Common misconceptions and mistakes

Do not treat all scans as full-table scans or all full-table scans as failures. An index can be unsuitable for a condition or costly when many rows qualify. Estimated plan rows commonly describe output, not every row visited. A scan does not guarantee presentation order; request ordering explicitly when needed. Also, estimated cost units are not automatically milliseconds, and a cached scan can still perform substantial CPU work. Read the specific database's operator definitions before comparing labels across products.

Business use

Scan analysis helps teams understand expensive reports, extraction jobs, and AI data preparation. It can reveal missing selective access paths, weak partition pruning, outdated estimates, or simply a workload that legitimately reads most data. Consider the whole plan and user-visible latency, including later joins and result transfer. Adding indexes creates storage and maintenance costs, so the business decision should account for reads and writes together. Recheck representative workloads after a material data-volume or distribution change.

Check your understanding

A sequential scan reports 300 output rows from a table containing 50,000 rows. Has it necessarily read only 300 rows?

Answer: No. It may have inspected the full table and rejected 49,700 rows through its filter. Consult actual plan details rather than interpreting the output count as the amount examined.

Related terms

Predicate (logic)
table scan
sequential scan
index scan
bitmap scan
index-only scan
query execution plan
predicate
selectivity
query optimizer
partition pruning
database statistics

Learn next

compare a broad query and a selective query on the same small test table. Identify their scan nodes, conditions, estimated outputs, and measured behavior. Remember that facilities such as EXPLAIN ANALYZE execute the statement; use a suitable test environment and understand the statement before measuring it.

Sources

Sources: PostgreSQL 18, Using EXPLAIN, https://www.postgresql.org/docs/18/using-explain.html ; PostgreSQL 18, Index-Only Scans and Covering Indexes, https://www.postgresql.org/docs/18/indexes-index-only-scans.html ; PostgreSQL 18, Indexes: Introduction, https://www.postgresql.org/docs/18/indexes-intro.html . Relevant primary documentation passages consulted for scan nodes, filters, output estimates, and index tradeoffs. PostgreSQL names illustrate the general concept; other systems may use different operators and reporting conventions.

Previous: Scaling laws Next: Scanpath