SQL 77 🛢️ Table Scans vs Index Scans and Index Seeks - KandZ

⏱️ Estimated read: 23 min | |

Every query that filters rows makes the same fundamental choice: read the whole table and check each row, or use an index to jump directly to the rows that match. The optimizer makes this decision on every query, and the choice determines whether the query takes milliseconds or minutes. Three access methods dominate: the table scan, which reads everything; the index scan, which walks an index and fetches matching rows; and the index seek, which descends directly to a specific value. The names differ between databases — SQL Server uses “scan” and “seek” precisely, while PostgreSQL uses “Seq Scan,” “Index Scan,” and “Index Only Scan” — but the underlying strategies are the same. Understanding when each is appropriate is what makes query performance predictable.

Key point: A table scan is not always bad, and an index scan is not always good. The correct access method depends on how many rows the query returns. When a query returns a large fraction of the table, a table scan is faster. When it returns a small fraction, an index seek is faster. The optimizer’s job is to know where the crossover point is.

Why access methods matter

The cost of reading. A table scan reads every page of the table. If the table has 10,000 pages and each page holds 100 rows, a scan reads 10,000 pages to find the matching rows. The cost is proportional to the table size, not the result size.

The cost of an index scan. An index scan walks the index structure, finds the matching keys, and then fetches the corresponding rows from the table. If the index is well-matched to the filter, the number of matching keys is small, and the fetch is cheap. If the filter matches many rows, the fetch is expensive because the rows are scattered across the table’s pages.

The cost of a seek. An index seek descends the index tree directly to a specific value, then reads the matching rows. The descent is logarithmic, and the fetch is limited to the matching rows. When the filter matches one row, the seek reads a handful of pages. When the filter matches a thousand rows, the seek reads more.

The correlation problem. The cost of fetching rows from the table depends on where the rows are physically stored. If the index key correlates with the physical order, the matching rows are adjacent, and the fetch reads few pages. If the key is random, each row is on a different page, and the fetch reads many pages. This is why the same query can be fast on one table and slow on another.

The selectivity problem. The optimizer estimates how many rows the filter will match. If the estimate is a small fraction of the table, the index seek wins. If the estimate is large, the table scan wins. The crossover point depends on the cost parameters, the correlation, and the width of the rows.

The covering index problem. When the index contains all the columns the query needs, the database can answer the query entirely from the index, without fetching any rows. This is an index-only scan, and it is the fastest access method. It exists because the index and the table are separate structures, and the index is often more compact.

a. Table scans

A table scan reads every row of the table, applies the filter to each, and returns the matches. It is the fallback when no index is usable, and it is the correct choice when the query returns a large fraction of the table.

The PostgreSQL Seq Scan

The Seq Scan operator is the table scan. It reads the table’s pages sequentially and applies the filter to each row.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE total > 100;

Seq Scan on orders  (cost=0.00..45000.00 rows=500000 width=50)
                   (actual time=0.015..420.000 rows=500000 loops=1)
  Filter: (total > 100)
  Rows Removed by Filter: 500000

The scan read a million rows to return 500,000. The Rows Removed by Filter shows how many were discarded. This is the correct plan when half the table matches.

The SQL Server Table Scan

SQL Server uses Table Scan for a heap and Clustered Index Scan for a table with a clustered index. Both read every row.

SET SHOWPLAN_ALL ON;
SELECT * FROM orders WHERE total > 100;

The plan shows a Table Scan or Clustered Index Scan with an estimated row count. The operator reads every row and filters.

The MySQL full table scan

MySQL reports a full table scan in the type column of EXPLAIN.

EXPLAIN SELECT * FROM orders WHERE total > 100;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

The type is ALL, which means a full table scan. The rows is the estimated row count.

When a table scan is correct

A table scan is the correct plan when the filter is not selective enough to justify an index. The exact threshold depends on the cost model, but the general rule is: if more than 10–20% of the table matches, a table scan is usually faster than an index scan.

-- Half the table matches. Table scan.
SELECT * FROM orders WHERE total > 100;

-- One row matches. Index seek.
SELECT * FROM orders WHERE order_id = 42;

The sequential read advantage

A table scan reads pages sequentially, which is fast on both spinning disks and SSDs. An index scan reads pages in index order, which is random with respect to the table, and random reads are slower. When the table scan reads 10,000 pages sequentially and the index scan reads 5,000 pages randomly, the table scan can be faster despite reading more data.

The parallelism

On large tables, the database can parallelize a table scan across multiple workers. Each worker reads a portion of the table, and the results are merged. Parallel scans are especially effective on analytical queries that aggregate large amounts of data.

SET max_parallel_workers_per_gather = 4;

EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders;

Finalize Aggregate  (cost=... rows=1)
  ->  Gather  (cost=... rows=4)
        Workers Planned: 4
        ->  Partial Aggregate  (cost=... rows=1)
              ->  Parallel Seq Scan on orders  (cost=... rows=...)

The Parallel Seq Scan reads the table with four workers. The Gather merges the partial results.

The table-scan pitfalls

A table scan is a problem only when the query is selective and the scan is unnecessary. The Rows Removed by Filter line is the diagnostic: when it is much larger than the rows returned, the scan is doing wasted work.

Seq Scan on orders  (actual rows=1 loops=1)
  Filter: (order_ref = 'ORD-2026-00042')
  Rows Removed by Filter: 999999

The scan read a million rows to return one. An index on order_ref would have made this a seek.

b. Index scans and seeks

An index scan walks the index and fetches the matching rows from the table. An index seek descends the index to a specific value. The distinction matters because the seek is cheaper when the index can be used for a direct lookup.

The PostgreSQL Index Scan

The Index Scan operator walks the index and fetches matching rows from the heap.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Index Scan using idx_orders_customer on orders
  (cost=0.43..8.45 rows=20 width=50)
  (actual time=0.020..0.035 rows=20 loops=1)
  Index Cond: (customer_id = 42)

The Index Cond shows the condition the index was used for. The Index Scan walks the index tree to the matching entries and fetches the rows.

The SQL Server Index Seek

SQL Server distinguishes between Index Seek and Index Scan. A seek uses the index’s sorted structure to jump to a specific value. A scan reads the entire index.

SET SHOWPLAN_ALL ON;
SELECT * FROM orders WHERE customer_id = 42;

The plan shows an Index Seek for the predicate. The seek is the operator that reads only the matching index entries.

The index seek conditions

An index seek is possible when the query’s filter matches the index’s leading column and the operator is =, <, <=, >, >=, BETWEEN, or IN. The seek descends the tree to the first matching entry and walks the leaf chain until the range is exhausted.

-- Seek on the index (customer_id, order_date).
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';
SELECT * FROM orders WHERE customer_id IN (42, 99, 137);

The first query seeks on customer_id. The second seeks on customer_id and walks the order_date range within it. The third performs three seeks and merges the results.

The index scan versus the index seek

The two are distinguished by how the index is used. A seek uses the index’s structure to jump to a specific location. A scan reads the index from beginning to end. The database reports the difference because the costs differ.

-- Seek: descends to customer 42.
SELECT * FROM orders WHERE customer_id = 42;

-- Scan: reads every index entry.
SELECT * FROM orders WHERE customer_id IS NOT NULL;

The second query cannot seek because the condition is not a range or equality on a specific value. The database scans the index and filters.

The index-only scan

When the index contains every column the query needs, the database can answer the query without fetching rows. This is the fastest access method.

CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, total);

EXPLAIN ANALYZE
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

Index Only Scan using idx_orders_covering on orders
  (cost=0.43..12.45 rows=20 width=...)
  (actual time=0.020..0.045 rows=20 loops=1)
  Index Cond: (customer_id = 42)
  Heap Fetches: 0

The Heap Fetches: 0 confirms that no table pages were read. The entire result came from the index.

The SQL Server covering index

SQL Server uses the term “covering index” for the same concept. The INCLUDE clause adds columns to the index’s leaf level without making them part of the seek key.

CREATE NONCLUSTERED INDEX idx_orders_covering
ON orders(customer_id)
INCLUDE (order_date, total);

The index seeks on customer_id and returns order_date and total from the leaf level. No key lookup is performed.

The range seek

A range seek uses the index’s sorted order to find a contiguous range of values. It descends to the first value in the range and walks the leaf chain until the range ends.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

Index Scan using idx_orders_date on orders
  Index Cond: ((order_date >= '2026-01-01') AND (order_date <= '2026-01-31'))

The Index Cond shows the range. The operator descends to the first matching entry and walks the leaf chain.

The non-leading column problem

A composite index can only be used for a seek on its leading columns. A query that filters on a non-leading column cannot seek.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Seek: leading column.
SELECT * FROM orders WHERE customer_id = 42;

-- No seek: non-leading column.
SELECT * FROM orders WHERE order_date = '2026-01-15';
-- The index may be scanned, but not sought.

The second query requires a separate index on order_date to seek.

c. Choosing between the access methods

The choice between a table scan, an index scan, and an index seek depends on the selectivity, the correlation, and the columns the query needs.

The selectivity threshold

The optimizer uses the estimated selectivity to choose. If the filter is estimated to match a small fraction of the table, the seek wins. If it matches a large fraction, the scan wins.

Selectivity	Typical Plan
< 1%	Index seek
1% – 10%	Index scan or bitmap scan
10% – 25%	Depends on cost model
> 25%	Sequential scan

The exact thresholds depend on the cost parameters and the correlation. On SSDs with random_page_cost = 1.1, the crossover is higher, and the optimizer prefers index scans more often.

The correlation effect

The correlation between the index key and the physical order determines the cost of fetching rows. High correlation means the matching rows are adjacent, and the fetch is cheap. Low correlation means each row is on a different page, and the fetch is expensive.

SELECT correlation FROM pg_stats
WHERE tablename = 'orders' AND attname = 'order_date';

A correlation near 1.0 means the index on order_date is efficient even for large result sets. A correlation near 0.0 means the index is efficient only for very selective filters.

The covering index

When the index contains all the columns the query needs, the index-only scan replaces the index scan. The index-only scan does not fetch rows, so the correlation does not matter.

-- Without covering index: index scan + heap fetch.
SELECT customer_id, order_date FROM orders WHERE customer_id = 42;

-- With covering index: index-only scan.
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

The covering index is the fastest access method when the query’s columns are known in advance.

The bitmap scan

The bitmap heap scan is a hybrid. It builds a bitmap of the matching pages from one or more indexes, sorts the bitmap by page address, and reads the heap in physical order. It reduces the random I/O of an index scan while still using the index for selectivity.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id IN (42, 99, 137, 256, 789);

Bitmap Heap Scan on orders  (cost=... rows=...)
  Recheck Cond: (customer_id = ANY ('{42,99,137,256,789}'))
  Heap Blocks: exact=1240
  ->  Bitmap Index Scan on idx_orders_customer
        Index Cond: (customer_id = ANY ('{42,99,137,256,789}'))

The Bitmap Index Scan produces a set of matching row locations. The Bitmap Heap Scan reads the heap in page order. The bitmap scan is often the best choice for moderate selectivity.

The forced scan

When the optimizer chooses an index seek but the table scan would be faster, the developer can force the scan. In PostgreSQL, this is done by disabling the index scan.

SET enable_indexscan = off;
SET enable_bitmapscan = off;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE total > 100;

RESET enable_indexscan;
RESET enable_bitmapscan;

The SET commands should be used for testing, not for production. The correct fix is to update the statistics or the cost parameters so the optimizer makes the right choice on its own.

The forced seek

Conversely, when the optimizer chooses a table scan but an index seek would be faster, the developer can force the index.

SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

RESET enable_seqscan;

The enable_seqscan = off setting makes the sequential scan’s cost artificially high, which forces the optimizer to consider other plans. If the index is usable, the optimizer will choose it. If the index is not usable, the optimizer will still choose the scan because there is no alternative.

The access method comparison

Access Method	Reads	Best When
Table scan	Every table page	Large result, low correlation
Index scan	Index pages + matching table pages	Moderate selectivity, high correlation
Index seek	Index path + specific table pages	Very selective filter
Index-only scan	Index pages only	All columns in the index
Bitmap scan	Index pages + table pages in order	Moderate selectivity, many rows

Complete Example Session

-- ============================================
-- PART 1: THE TABLE SCAN
-- ============================================

EXPLAIN ANALYZE
SELECT * FROM orders WHERE total > 100;

-- Seq Scan on orders  (cost=0.00..45000.00 rows=500000 width=50)
--                    (actual time=0.015..420.000 rows=500000 loops=1)
--   Filter: (total > 100)
--   Rows Removed by Filter: 500000

-- Half the table matches.
-- The table scan is correct.

-- ============================================
-- PART 2: THE UNNECESSARY TABLE SCAN
-- ============================================

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_ref = 'ORD-2026-00042';

-- Seq Scan on orders  (cost=0.00..45000.00 rows=1 width=50)
--                    (actual time=0.015..850.000 rows=1 loops=1)
--   Filter: (order_ref = 'ORD-2026-00042')
--   Rows Removed by Filter: 999999

-- One row matches, but the scan reads a million.
-- No index exists on order_ref.

-- ============================================
-- PART 3: THE INDEX SEEK
-- ============================================

CREATE INDEX idx_orders_ref ON orders(order_ref);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_ref = 'ORD-2026-00042';

-- Index Scan using idx_orders_ref on orders
--   (cost=0.43..8.45 rows=1 width=50)
--   (actual time=0.025..0.026 rows=1 loops=1)
--   Index Cond: (order_ref = 'ORD-2026-00042')

-- The seek descends to the specific value.
-- 0.026 ms instead of 850 ms.

-- ============================================
-- PART 4: THE INDEX SCAN ON A RANGE
-- ============================================

CREATE INDEX idx_orders_date ON orders(order_date);

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

-- Index Scan using idx_orders_date on orders
--   Index Cond: ((order_date >= '2026-01-01') AND
--                (order_date <= '2026-01-31'))

-- The seek descends to the first match.
-- The leaf chain is walked until the range ends.

-- ============================================
-- PART 5: THE COVERING INDEX
-- ============================================

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);

EXPLAIN ANALYZE
SELECT customer_id, order_date FROM orders WHERE customer_id = 42;

-- Index Only Scan using idx_orders_cust_date on orders
--   Index Cond: (customer_id = 42)
--   Heap Fetches: 0

-- The index contains both columns.
-- The heap is never touched.

-- ============================================
-- PART 6: THE BITMAP SCAN
-- ============================================

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id IN (42, 99, 137, 256, 789);

-- Bitmap Heap Scan on orders  (cost=... rows=...)
--   Recheck Cond: (customer_id = ANY ('{42,99,137,256,789}'))
--   Heap Blocks: exact=1240
--   ->  Bitmap Index Scan on idx_orders_customer
--         Index Cond: (customer_id = ANY ('{42,99,137,256,789}'))

-- The bitmap scan reads the heap in page order.
-- The random I/O of a regular index scan is reduced.

-- ============================================
-- PART 7: THE NON-LEADING COLUMN
-- ============================================

-- Index on (customer_id, order_date).
EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date = '2026-01-15';

-- Seq Scan on orders  (cost=... rows=...)
--   Filter: (order_date = '2026-01-15')

-- The index cannot seek on order_date alone.
-- A separate index on order_date is needed.

CREATE INDEX idx_orders_date_only ON orders(order_date);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE order_date = '2026-01-15';

-- Index Scan using idx_orders_date_only on orders
--   Index Cond: (order_date = '2026-01-15')

-- ============================================
-- PART 8: THE FORCED INDEX
-- ============================================

SET enable_seqscan = off;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

RESET enable_seqscan;

-- The optimizer is forced to use an index.
-- Useful for testing. Reset afterward.

-- ============================================
-- PART 9: THE CORRELATION EFFECT
-- ============================================

SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders'
ORDER BY correlation DESC;

-- order_date    | 1.0   (append-only table)
-- order_id      | 1.0   (sequence)
-- customer_id   | 0.02  (random)
-- total         | 0.01  (random)

-- High correlation makes index scans efficient.
-- Low correlation makes them expensive.

-- ============================================
-- PART 10: THE ACCESS METHOD DECISION
-- ============================================

-- Very selective: index seek.
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 42;

-- Moderate selectivity: bitmap scan or index scan.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Large result: sequential scan.
EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 100;

-- All columns in the index: index-only scan.
EXPLAIN ANALYZE
SELECT customer_id, order_date FROM orders WHERE customer_id = 42;

The ten parts showed a table scan, an unnecessary table scan, an index seek, a range scan, a covering index, a bitmap scan, a non-leading column, a forced index, the correlation effect, and the access method decision.

Quick Reference

Access Methods

Method	PostgreSQL	SQL Server	MySQL
Table scan	`Seq Scan`	`Table Scan`	`type: ALL`
Index scan	`Index Scan`	`Index Scan`	`type: index`
Index seek	`Index Scan` (with `Index Cond`)	`Index Seek`	`type: ref`
Covering	`Index Only Scan`	`Index Seek` + `INCLUDE`	`type: index`
Bitmap	`Bitmap Heap Scan`	`Bitmap`	—

Selectivity and Access Method

Selectivity	Access Method
< 1%	Index seek
1% – 10%	Index scan or bitmap scan
10% – 25%	Depends on cost model
> 25%	Table scan

Leftmost Prefix Rule

Query Filter	Index (a, b, c) Can Seek?
a = ?	Yes
a = ? AND b = ?	Yes
a = ? AND b = ? AND c = ?	Yes
b = ?	No
c = ?	No
a = ? AND c = ?	Partial (seek on a)

Force and Reset

Command	Effect
`SET enable_seqscan = off`	Discourage table scans
`SET enable_indexscan = off`	Discourage index scans
`SET enable_bitmapscan = off`	Discourage bitmap scans
`RESET enable_seqscan`	Restore defaults

Best Practices

✅ Do This:

-- Read the plan before tuning.
EXPLAIN ANALYZE SELECT ...;  -- ✅

-- Add indexes for selective filters.
CREATE INDEX idx ON orders(order_ref);  -- ✅

-- Use covering indexes for hot queries.
CREATE INDEX idx ON orders(customer_id) INCLUDE (order_date);  -- ✅

-- Check the correlation statistic.
SELECT correlation FROM pg_stats WHERE ...;  -- ✅

-- Update statistics after bulk changes.
ANALYZE orders;  -- ✅

-- Use a bitmap scan for moderate selectivity.
-- The optimizer chooses it automatically.  -- ✅

❌ Don’t Do This:

-- Don't assume a table scan is always bad.
SELECT * FROM orders WHERE total > 100;  -- ✅ Correct plan

-- Don't force an index seek when the scan is faster.
SET enable_seqscan = off;  -- ❌ Use only for testing

-- Don't create an index for every filter.
-- Each index taxes writes.  -- ❌

-- Don't ignore correlation.
-- Low correlation makes index scans expensive.  -- ❌

-- Don't forget the covering index.
-- An index-only scan is the fastest access.  -- ❌

-- Don't add an index without checking the plan after.
EXPLAIN ANALYZE ...;  -- ✅ Always verify

Common Pitfalls

Pitfall	Why It Happens	Fix
Table scan on selective filter	Missing index	Add the index
Index scan on large result	Misestimated selectivity	Update statistics
Seek not possible	Non-leading column	Add a separate index
Slow index scan	Low correlation	Use a table scan or bitmap
Sort after index scan	Index does not provide order	Add the sort column to the index
Covering index not used	Column not in the index	Add the column with INCLUDE
Bitmap scan slower	Too many matching pages	Use a table scan

Real-World Examples

1. Table Scan

EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 100;

2. Index Seek

CREATE INDEX idx ON orders(order_ref);
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_ref = 'ORD-001';

3. Range Scan

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

4. Covering Index

CREATE INDEX idx ON orders(customer_id, order_date);
EXPLAIN ANALYZE SELECT customer_id, order_date FROM orders WHERE customer_id = 42;

5. Bitmap Scan

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id IN (42, 99, 137);

6. Check Correlation

SELECT correlation FROM pg_stats WHERE tablename = 'orders' AND attname = 'customer_id';

7. Force Index

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
RESET enable_seqscan;

8. Non-Leading Column

CREATE INDEX idx ON orders(order_date);
EXPLAIN ANALYZE SELECT * FROM orders WHERE order_date = '2026-01-15';

9. Parallel Scan

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders;

10. Heap Fetches

EXPLAIN ANALYZE SELECT customer_id FROM orders WHERE customer_id = 42;
-- Heap Fetches: 0 → index-only scan

Visual

Three Access Methods

┌────────────────────────────────────────────────────────────┐
│  TABLE SCAN                                                │
│                                                            │
│  ┌────┬────┬────┬────┬────┬────┬────┬────┐               │
│  │ P1 │ P2 │ P3 │ P4 │ P5 │ P6 │ P7 │ P8 │               │
│  └────┴────┴────┴────┴────┴────┴────┴────┘               │
│  ─────────────────────────────────────────▶               │
│  Read every page. Check every row.                         │
│  Cost proportional to table size.                          │
│                                                            │
│  ──────────────────────────────────────────────────────    │
│                                                            │
│  INDEX SCAN                                                │
│                                                            │
│  Index: [a][b][c][d][e][f][g][h]                           │
│            │     │                                         │
│            v     v                                         │
│  Heap:  [P3]  [P7]                                         │
│  Walk index, fetch matching rows from scattered pages.     │
│  Cost proportional to matching rows + random I/O.          │
│                                                            │
│  ──────────────────────────────────────────────────────    │
│                                                            │
│  INDEX SEEK                                                │
│                                                            │
│  Index: [a][b][c][d][e][f][g][h]                           │
│                        ▲                                   │
│                        │ descend to 'e'                    │
│                        │                                   │
│  Heap:              [P5]                                   │
│  Jump directly to the value. Fetch one row.                │
│  Cost logarithmic in index size.                           │
└────────────────────────────────────────────────────────────┘

The Selectivity Decision

┌────────────────────────────────────────────────────────────┐
│  SELECTIVITY AND ACCESS METHOD                             │
│                                                            │
│  Rows returned as fraction of table:                       │
│                                                            │
│  0%                 10%                50%             100%│
│  │                   │                  │                │ │
│  │  Index Seek       │  Index/Bitmap    │  Table Scan    │ │
│  │◀─────────────────▶│◀────────────────▶│◀──────────────▶│ │
│  │                   │                  │                │ │
│                                                            │
│  The crossover points depend on:                           │
│    - random_page_cost vs seq_page_cost                     │
│    - correlation                                           │
│    - row width                                             │
│    - available indexes                                     │
│                                                            │
│  On SSDs: crossover shifts right (more index usage).       │
│  On HDDs: crossover shifts left (more table scans).        │
└────────────────────────────────────────────────────────────┘

The Bitmap Scan

┌────────────────────────────────────────────────────────────┐
│  BITMAP HEAP SCAN                                          │
│                                                            │
│  STEP 1: Bitmap Index Scan                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Index lookup for customer_id IN (42, 99, 137)    │     │
│  │  Produces a set of matching row locations.         │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  STEP 2: Sort by page address                              │
│  ┌──────────────────────────────────────────────────┐     │
│  │  P3, P7, P12, P18, P42, P57, P89, ...             │     │
│  │  Sorted in physical page order.                    │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  STEP 3: Bitmap Heap Scan                                  │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Read the heap in page order.                     │     │
│  │  One pass, sequential-like.                       │     │
│  │  Random I/O is reduced.                           │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  Best for moderate selectivity with multiple keys.         │
└────────────────────────────────────────────────────────────┘

The Leftmost Prefix Rule

┌────────────────────────────────────────────────────────────┐
│  COMPOSITE INDEX (customer_id, order_date, total)          │
│                                                            │
│  QUERY                            SEEK?                    │
│  ──────────────────────────────────────                    │
│  customer_id = 42                 YES (leftmost)           │
│  customer_id = 42 AND             YES (prefix)             │
│    order_date > '...'                                      │
│  customer_id = 42 AND             YES (full key)           │
│    order_date = '...' AND total > 10                       │
│  order_date = '...'               NO (gap)                 │
│  total > 10                       NO (gap)                 │
│  order_date = '...' AND           NO (gap)                 │
│    total > 10                                              │
│                                                            │
│  The index is sorted by customer_id first.                 │
│  Without a value for customer_id, the seek cannot start.   │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
Table scan	Read every row, filter each
Index scan	Walk the index, fetch rows
Index seek	Descend to a specific value
Index-only scan	Answer from the index, no heap
Bitmap scan	Build a bitmap, read heap in order
Selectivity	Fraction of table matched
Correlation	Order between index and heap
Leftmost prefix	Composite index seek rule
`enable_seqscan`	Discourage table scans
`Heap Fetches`	Rows fetched from the heap in an index-only scan

Key takeaways:

The access method is chosen by the optimizer based on cost. A table scan is not always bad, and an index seek is not always good. The correct method depends on how many rows the query returns.
A table scan is correct for large results. When more than roughly 10–25% of the table matches, the sequential read of a table scan is faster than the random reads of an index scan.
An index seek is correct for selective filters. When a filter matches a small fraction of the table, the seek descends directly to the matching rows and reads only a few pages.
The leftmost prefix rule limits seeks on composite indexes. A composite index can only seek on its leading columns. A query that filters on a non-leading column cannot use the index for a seek and must scan or use a separate index.
The correlation statistic determines the cost of the heap fetch. High correlation means the matching rows are physically adjacent, and the fetch is cheap. Low correlation means each row is on a different page, and the fetch is expensive.
A covering index enables an index-only scan. When the index contains every column the query needs, the heap is never touched. The Heap Fetches: 0 line confirms it.
The bitmap scan reduces random I/O for moderate selectivity. It builds a bitmap of matching pages, sorts it, and reads the heap in page order. It is often the best choice between a seek and a scan.
The optimizer’s choice can be wrong. When the estimate is off, the plan is off. The fix is to update the statistics with ANALYZE, not to force the plan with enable_* settings. The settings are for testing.

Remember: Every query that filters rows chooses between reading everything and jumping to the matches. The table scan reads every page and is correct for large results. The index seek descends to a specific value and is correct for selective filters. The index scan walks the index and fetches rows, and its cost depends on the correlation between the index key and the physical order. The index-only scan answers from the index alone and is the fastest access method when the columns are available. The optimizer chooses based on statistics, and the statistics are approximations. Reading the plan, comparing the estimated rows to the actual rows, and updating the statistics when they diverge is what makes query performance predictable. The choice between the access methods is not a matter of preference; it is a matter of selectivity, correlation, and the columns the query needs.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!