Table Scans vs Index Scans in SQL

Introduction

Table scans and index scans are physical access methods used by database engines to retrieve rows. A table scan reads table storage, while an index scan reads entries from an index. A related operation, often called an index seek or targeted index lookup, navigates to a specific key or range instead of traversing the complete structure.

None of these operations is inherently good or bad. A table scan may be the fastest way to return most of a table, and an index scan may be ideal when a narrow index contains every required value. A seek can still read thousands of rows or cause expensive lookups. Correct analysis asks how much total work the chosen path performs for the real query and workload.

What Are Table Scans and Index Scans?

A scan processes a sequence of storage entries and evaluates whether each one belongs in the result. During a table scan, those entries are table rows or pages. During an index scan, they are index keys and any stored row locators or included values. The optimizer chooses among available paths according to estimated cost.

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;

Without a useful department index, the engine may scan the table. With an index, it might scan or navigate through that index. Table size, key distribution, selected columns, cache state, and estimated matching rows all influence the decision.

Why Scan Choices Matter

Data access commonly accounts for much of a query's logical I/O. Reading one million rows to return ten may waste CPU, cache space, storage bandwidth, and time. However, reading nearly every table row through an index and then fetching full rows individually can be more expensive than one orderly table scan.

Understanding scan choices helps interpret execution plans, design indexes, recognize non-SARGable predicates, and avoid unnecessary tuning. The objective is not to eliminate scans. It is to retrieve the required result with acceptable total resource use and predictable behavior.

A Library Analogy

Imagine a library with shelves and a catalog. Inspecting every book on every shelf resembles a table scan. Reading every catalog entry resembles a full index scan. Navigating alphabetically to one title resembles a seek. If a visitor asks for every book, walking the shelves is reasonable. If the request is for one ISBN, organized catalog navigation is usually better.

The analogy also explains lookups. A catalog may identify a shelf location but not contain the full book. Repeatedly moving between catalog and shelves can become expensive when many books match.

Understanding Database Storage

Databases store tables and indexes in pages, blocks, or similar allocation units. A row may live in heap storage, in a clustered structure, or in another engine-specific organization. Secondary index entries commonly contain keys plus a row locator, primary key, or physical pointer. Some indexes also store additional included values.

Engines usually read pages rather than requesting one value at a time from storage. Page size, row width, compression, clustering, fragmentation, cache state, and read-ahead behavior affect how much work a scan performs. Logical table definitions therefore do not tell the complete physical story.

What Is a Table Scan?

A table scan reads table pages or row storage to return all rows or test a predicate. PostgreSQL commonly labels this operation a sequential scan. SQL Server may show a table scan for a heap or a clustered index scan when the clustered structure is the table. MySQL plan output often represents broad access with access type ALL.

Table scans require no supporting secondary index. They are efficient when a table is small, most rows are needed, a useful index does not exist, or the cost of following many row locators would exceed sequential table access.

How a Table Scan Works

The engine identifies the table's relevant allocations, reads their pages, examines visible rows, applies predicates, and passes qualifying rows upward. It may use read-ahead, parallel workers, partition pruning, vectorized processing, or storage-engine filtering. A plan node can therefore represent more sophisticated work than a literal row-by-row loop.

Rows read and rows returned are important. A scan that reads ten million rows and returns five deserves attention; one that reads ten million and returns nine million may be exactly appropriate.

Types of Table Scans

Terminology varies by engine and storage type. Common concepts include sequential scans, heap scans, clustered index scans, full table scans, partition scans, and parallel scans. These labels describe access organization and execution, but they should always be interpreted with actual row flow and runtime metrics.

What Is an Index Scan?

An index scan reads a sequence of index entries, potentially the entire index. It can return values directly when the index covers the query or use row locators to fetch data from the table. Because an index is often narrower than its table, scanning it can require fewer pages even when every key is visited.

How an Index Scan Works

The engine begins at an index location and moves through leaf entries in key order or another supported direction. It evaluates index conditions and residual predicates, returns stored values, and performs table lookups when required columns are missing. An ordered index scan can sometimes satisfy ORDER BY or grouping without a separate sort.

Table Scan vs Index Scan

CharacteristicTable scanIndex scan
Primary structure readTable or base row storageIndex leaf entries
Secondary index requiredNoYes
Typical widthFull stored row widthKey, locator, and stored index values
OrderingUsually not guaranteedMay provide useful key order
Extra row lookupNot for table columnsPossible when not covering

Table Scan vs Index Scan vs Index Seek

A table scan traverses table storage. An index scan traverses index storage. A seek or targeted index access navigates to qualifying key positions, then reads a key or range. The boundaries are engine-specific: PostgreSQL uses index scans for both constrained and broad index access, while SQL Server explicitly distinguishes seek and scan operators.

A seek is not automatically cheaper. A low-selectivity range can read most of an index, and each match may require a table lookup. Always inspect estimated and actual rows, rows read, loops, and lookup activity.

Sample Database

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  department_id INTEGER NOT NULL,
  employee_name VARCHAR(120) NOT NULL,
  salary DECIMAL(12, 2) NOT NULL,
  hire_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL
);

CREATE INDEX ix_employees_department
ON employees (department_id);

Assume the table contains one million employees across departments of very different sizes. That skew is important because the best path for a rare department may differ from the best path for the largest department.

Example 1: Full Table Scan

SELECT employee_id, department_id, employee_name,
       salary, hire_date, status
FROM employees;

The statement requests every row and every listed column. A table scan is a natural strategy because an index cannot avoid reading the data. Forcing index access would add complexity without reducing the result.

Example 2: Table Scan with Filtering

SELECT employee_id, employee_name
FROM employees
WHERE salary > 100000;

If salary has no useful index, the table is scanned and each row is tested. Even with an index, a scan may remain cheaper if a large percentage of employees qualifies or if row lookups are needed for most matches.

Example 3: Full Index Scan

SELECT department_id
FROM employees
ORDER BY department_id;

The department index is narrower than the table and already ordered. Scanning it may return the required value in order without table access or an explicit sort.

Example 4: Index Scan with Filtering

An index can be scanned while a residual predicate removes entries. This happens when the index provides useful coverage or order but the condition cannot define a narrow start and end range.

SELECT department_id
FROM employees
WHERE department_id + 0 = 10;

The expression is unnecessary and may prevent direct navigation in some systems. Prefer a simple, type-compatible predicate when it preserves meaning.

Example 5: Targeted Index Access

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;

The index can locate the key value 10 and read its matching range. Whether this wins depends on how many employees belong to that department and whether employee names require additional table access.

Example 6: Index Access for ORDER BY

SELECT employee_id, department_id
FROM employees
ORDER BY department_id, employee_id;

An index beginning with department may reduce sorting, but exact ordering also depends on subsequent keys, row locators, null ordering, collation, and engine rules. Check the plan for an explicit sort rather than assuming order.

Example 7: Index Access for GROUP BY

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

A narrow ordered index can support grouped processing efficiently. Some engines may instead use a hash aggregate over a table scan. Data size, memory, parallelism, and index width determine the winner.

Example 8: Covering Index Scan

CREATE INDEX ix_employees_department_cover
ON employees (department_id, employee_id, employee_name);

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10;

The query can potentially be answered from the index alone. Coverage reduces row lookups but makes the index wider and more expensive to maintain. Engine-specific include columns can store output values without making all of them search keys.

Why an Index Scan Can Be Faster

A narrow index fits more entries per page, so fewer pages may be read. It may contain all requested columns, preserve useful order, or exclude irrelevant rows through a partial or filtered definition. Index compression and cache residency can increase the advantage.

The benefit disappears when many random table lookups follow the scan, the index is nearly as wide as the table, or fragmentation and cache conditions create additional work.

Why a Table Scan Can Be Faster

When most rows are required, reading table pages sequentially avoids bouncing between index and table. The table already contains every selected column, and read-ahead or parallel scanning can use available bandwidth efficiently. Small tables can also be cheaper to scan than to navigate through an index tree.

Index Selectivity

Selectivity describes how narrowly a condition identifies rows. A unique employee ID is highly selective. A status column with values such as active and inactive is often not. Highly selective predicates commonly benefit from targeted access; broad predicates often favor scans.

Distribution matters more than the number of distinct values alone. A status value that occurs in one percent of rows may benefit from an index even when another status matches almost every row.

The Access-Path Tipping Point

As the estimated number of qualifying rows increases, the cost of repeated index-to-table lookups can exceed the cost of scanning the table once. The point where the optimizer switches strategies is often called a tipping point. It is not a fixed percentage because page density, row width, cache, lookup cost, and engine estimates differ.

Statistics, Skew, and Parameter-Sensitive Scans

The optimizer normally chooses an access path before it knows the exact runtime work, so it relies on statistics to estimate selectivity. Statistics may summarize value frequencies, ranges, distinct counts, nulls, and correlations in engine-specific ways. When they are stale or too coarse for a skewed distribution, the optimizer can underestimate or overestimate the number of matching rows and choose an access path that looks surprising during execution.

Parameter values make this especially visible. A query for a department containing twenty employees may benefit from targeted index access, while the same statement for a department containing half the company may be cheaper as a table scan. Reusing one cached plan for both values can favor one case and penalize the other. Capture the compiled and runtime parameter context where the database exposes it, and test both rare and common values instead of tuning only the first example.

Updating statistics can help when the stored distribution no longer represents the table, but it should not become a ritual response to every scan. Confirm the estimation problem, understand the platform's automatic maintenance, and measure the result. If stable correlations between columns matter, engine-supported multicolumn or extended statistics may provide better estimates. If workload shapes genuinely require different strategies, evaluate supported parameter-sensitive planning, statement design, or carefully scoped recompilation options before forcing a single access path.

Logical Reads

A logical read accesses a page through the database buffer system. It may be served from memory or trigger storage I/O. Comparing logical reads helps reveal underlying work even when repeated tests are cached. A narrower covering index often wins by reducing the number of pages touched.

Physical Reads

A physical read fetches data from storage because it is not available in cache. Physical reads depend on cache warmth and concurrent activity, so a single cold-versus-warm comparison can mislead. Record both logical work and elapsed behavior under representative conditions.

Sequential and Random I/O

Large scans can benefit from read-ahead and locality. Repeated lookups may access pages in a less predictable pattern. Modern SSDs reduce the mechanical penalty of random access, but they do not eliminate I/O volume, controller limits, cache effects, or CPU spent processing extra requests.

Table Width

Wide rows mean fewer rows per page and more pages for the same row count. Large variable-length values may be stored separately, depending on the engine. Selecting only needed columns can reduce transfer and sometimes make a narrow index sufficient, but it does not automatically change base storage width.

Index Width

Every key, locator, and included value contributes to index width. Wider indexes consume more pages, cache, storage, and write effort. A covering index should therefore serve an important, repeated workload rather than become a copy of the entire table.

Clustered Index Scans

In SQL Server, the clustered index leaf level stores the table rows. A clustered index scan is therefore a scan of the table in clustered-key organization, not necessarily a scan of a small secondary index. It can be correct for broad queries and still provide useful order.

Heap Table Scans

A heap has no clustered key ordering. A table scan reads its data pages, while nonclustered indexes use row identifiers to locate heap records in SQL Server. Heaps can suit particular staging and loading patterns, but forwarded rows and random RID lookups can complicate read performance.

PostgreSQL Sequential and Index Scans

PostgreSQL plans may show Seq Scan, Index Scan, Index Only Scan, and bitmap operations. An index scan can use index conditions to identify tuples and then visit heap pages. The planner compares page cost, CPU, selectivity, correlation, and cache assumptions when selecting a path.

PostgreSQL Index-Only Scans

An index-only scan can return values from the index when the index physically stores every required column. PostgreSQL must also determine whether heap tuples are visible to the current transaction. Its visibility map can avoid many heap visits; if pages are not marked all-visible, heap fetches may still occur.

PostgreSQL Bitmap Scans

A bitmap index scan builds a bitmap of possible heap locations, and a bitmap heap scan visits the relevant table pages in a more organized way. This strategy can bridge the gap between a selective index scan and a full sequential scan. It can also combine multiple indexes with bitmap operations.

MySQL Table and Index Access

MySQL's traditional EXPLAIN output uses the type column. ALL represents a full table scan, index represents a full index scan, and values such as range, ref, eq_ref, and const describe more targeted access patterns. Read these with key, rows, filtered, and Extra.

SQL Server Table, Clustered, and Index Scans

SQL Server distinguishes table scans on heaps, clustered index scans on clustered tables, nonclustered index scans, and index seeks. Actual plans can expose actual rows, rows read, executions, lookups, residual predicates, memory, and warnings. Estimated operator percentages are cost-model outputs, not measured time.

Identifying Table Scans in Plans

Look for engine-specific nodes such as Table Scan, Clustered Index Scan, Seq Scan, or full table access. Then record object size, estimated and actual rows, rows removed by filters, predicates, partitions, loops, and reads. A label without this context cannot establish a problem.

Identifying Index Scans in Plans

Confirm which index was selected, how much of it was read, whether an index condition limited the range, whether the query was covered, and whether table lookups followed. Also check whether order from the index removed a sort or supported streaming aggregation.

Comparing Scan Performance

Use the same SQL semantics, representative parameters, similar cache conditions, and sufficient repetitions. Capture elapsed time, CPU, logical and physical reads, rows read, rows returned, lookup counts, memory, waits, and concurrency effects. Do not compare plans from different datasets or server loads as if access method alone caused the difference.

Performance Comparison Example

-- Broad result may favor a table scan
SELECT employee_id, employee_name, salary
FROM employees
WHERE status = 'ACTIVE';

-- Selective result may favor targeted index access
SELECT employee_id, employee_name, salary
FROM employees
WHERE employee_id = 101;

The first query can return most employees; the second should return at most one. Their different access paths reflect different information needs rather than an optimizer inconsistency.

When to Prefer a Table Scan

A table scan is often suitable when the query needs most rows or columns, the table is small, a reporting operation processes a broad period, an index would require excessive lookups, or sequential throughput is valuable. It can also be preferable during batch processing where predictable broad access is expected.

When to Prefer an Index Scan

An index scan is attractive when the index is much narrower than the table, covers the query, provides required order, supports grouping, or contains only a useful subset of rows. It may also be the most efficient way to count or inspect keys without reading wide table data.

When to Prefer Targeted Index Access

A seek or constrained index scan fits selective equality conditions, bounded ranges, point lookups, and joins where a small outer input searches an indexed inner side. Key order, type compatibility, and predicate form must permit navigation.

How Index Design Affects Scans

Composite key order determines which prefixes can be navigated efficiently. Included columns influence coverage. Partial or filtered indexes reduce size for stable subsets. Clustering affects locality. Compression changes page density. Good design starts from actual filters, joins, ordering, grouping, and selected columns.

Why the Optimizer Chooses a Table Scan

Common reasons include no applicable index, a broad predicate, a small table, stale or misleading statistics, non-SARGable expressions, implicit conversions, parameter values with many matches, and a query requesting many columns. Sometimes the scan is simply the lowest-cost valid choice.

Why the Optimizer Chooses an Index Scan

The index may cover the query, be narrower than the table, provide order, or avoid reading excluded rows. The predicate may not define a narrow range but the index can still reduce work. Full index access is not evidence that the index failed.

Why an Index May Not Be Used

An index may be unhelpful because its leading key does not match the predicate, types differ, a function wraps the column, a leading wildcard prevents normal range navigation, statistics predict many matches, or lookups cost too much. Index visibility, collation, null semantics, and optimizer settings can also matter.

Common Analysis Mistakes

  • Calling every table scan a performance defect.
  • Assuming a seek always reads only one row.
  • Ignoring rows read, rows returned, and loop counts.
  • Adding indexes without measuring write and storage overhead.
  • Comparing estimated cost percentages as actual elapsed time.
  • Testing only one parameter value in a skewed dataset.
  • Forcing access paths before checking statistics and predicates.
  • Ignoring lookup work after an apparently efficient index path.
  • Using SELECT * when only a few columns are required.
  • Judging a plan without frequency, concurrency, or business context.

Best Practices

Start with measured workload evidence. Keep statistics representative, write type-compatible SARGable predicates, retrieve necessary columns only, and design the smallest effective index set. Compare plans and runtime metrics before and after one targeted change.

Test selective and common values, broad and narrow ranges, warm and cold cache where relevant, and realistic concurrency. An optimization is successful only when results remain correct and overall workload cost improves.

Real-World E-Commerce Product Catalog

A category page may return thousands of products, while a SKU lookup returns one. The category query can favor an index range or even broad access; the SKU lookup should use targeted unique access. Search, sorting, inventory status, and selected image data change coverage and width requirements.

Real-World Banking Transaction History

An account-and-date index can support bounded history access and stable ordering. Very active accounts may cross the tipping point for broad date ranges. Security and tenant predicates must remain intact, and hot accounts should be included in parameter testing.

Real-World Employee Management

Department filters, active status, salary bands, and names create different selectivity patterns. One index rarely serves every combination. Analyze the most valuable workflows and avoid constructing a wide index for every optional search field.

Real-World Software Testing Dashboard

A dashboard reading most recent test results may benefit from a narrow covering index. A nightly trend report aggregating months of runs may prefer broad scans and parallel aggregation. Trying to force the same access path on both workloads can hurt one of them.

Real-World Tutorial Listing

A published-tutorial listing commonly filters status and orders by publication time. A partial or composite index may avoid reading drafts and sorting all content. Deep offset pagination still causes growing work, so keyset pagination should be evaluated separately from the scan operator.

Interview Questions and Answers

Is a table scan always slow?

No. It can be optimal for small tables, broad results, and queries that require most columns or rows.

What is the difference between an index scan and a seek?

A scan traverses a sequence of index entries, potentially all of them. A seek navigates to a key or bounded range, although terminology differs by engine.

What is index selectivity?

It describes how narrowly a key or predicate identifies rows. More selective conditions generally make targeted index access more attractive.

Why might an optimizer ignore an index?

The index may not match the predicate, may require too many lookups, or may cost more than scanning based on row estimates and table size.

What is a covering index?

It stores all values needed by a query, allowing the engine to answer it without fetching additional table columns.

How should scan performance be compared?

Use representative parameters and compare reads, CPU, elapsed time, row flow, lookups, memory, waits, and concurrency under similar conditions.

Practical Exercises

  1. Create the employee table and load skewed department data.
  2. Capture a plan for an unindexed salary predicate.
  3. Add a salary index and compare narrow and broad ranges.
  4. Compare selecting one indexed column with selecting every column.
  5. Observe lookup behavior for a non-covering department index.
  6. Create a covering candidate and measure read and write effects.
  7. Use an indexed expression and then rewrite it as a direct predicate.
  8. Test ordering with and without a compatible composite index.
  9. Compare a rare status value with a common status value.
  10. Record estimated rows, actual rows, reads, and runtime for each case.

Quick Reference

Access methodOften appropriate forInvestigate when
Table scanSmall tables or broad resultsHuge input produces very few rows repeatedly
Index scanNarrow, covered, ordered accessMost of a wide index is read plus many lookups
Targeted index accessSelective keys and rangesMany matches or high repeated lookup cost
Index-only accessCovered queries with visibility supportUnexpected heap fetches remain high
Bitmap accessModerate selectivity or combined indexesBitmap becomes lossy or reads broad heap areas

Step-by-Step Scan Analysis Workflow

  1. Identify a frequent or expensive query with a reproducible problem.
  2. Save its SQL text, parameters, result requirements, and baseline metrics.
  3. Capture an execution plan in a safe environment.
  4. Identify table, index, bitmap, and lookup operations.
  5. Compare estimated rows, actual rows, rows read, and loops.
  6. Check object size, table width, index width, and predicate selectivity.
  7. Review SARGability, data types, key order, coverage, and statistics.
  8. Determine whether order, grouping, or coverage justifies the chosen scan.
  9. Form one targeted and reversible improvement hypothesis.
  10. Retest the plan, reads, CPU, elapsed time, waits, and correctness.
  11. Test alternate parameter distributions and realistic concurrency.
  12. Evaluate storage, write cost, maintenance, and other affected queries.
  13. Deploy safely, monitor production behavior, and document the evidence.

Key Takeaway

Table scans, index scans, and targeted index access are tools chosen from the physical options available to the optimizer. Their quality depends on the requested columns, number and distribution of matching rows, storage width, index coverage, ordering, cache, and lookup cost. Operator names are clues, not conclusions.

Effective analysis follows row flow and measures total work. Keep a scan when it is the cheapest way to satisfy a broad request, improve access when a large object is repeatedly read for a tiny result, and validate every change across representative parameters and workload conditions. The goal is efficient, predictable retrieval, not index usage for its own sake.