Index Scan

An Index Scan descends a B-tree (or other index) to locate matching entries, and for each match fetches the corresponding row from the table heap. Rows come back in index order.

When an index covers the query's conditions and the planner expects relatively few matching rows, or when it needs the rows in the index's sort order (e.g. for an ORDER BY).

Usually excellent for selective lookups. It can become slow if it matches many rows, because each match costs a separate (often random) heap fetch — past a few percent of the table, a Bitmap Heap Scan or Seq Scan is cheaper.

How it works under the hood

An Index Scan descends the index (a B-tree, by default) to the first matching entry, then walks the leaf level following the requested order. Each index entry points at a heap location, so for every match the executor also reads the table page to fetch the full row and check MVCC visibility.

That second step — the heap fetch — is the cost that matters. When matches are few, the fetches are few. When matches are many and scattered, you pay one (often random) page read per row, which is why a highly-matching Index Scan can lose to a Bitmap Heap Scan or even a plain Seq Scan.

Reading the numbers

Index Scan using orders_customer_idx on orders
  (cost=0.43..38.20 rows=12 width=64) (actual time=0.03..0.09 rows=11 loops=1)
  Index Cond: (customer_id = 42)
  Buffers: shared hit=14

• Index Cond is what the index satisfied directly — good. Anything that shows up as a separate Filter was not served by the index and was checked row by row.
• Low rows and low Buffers is the signature of a healthy lookup.
• If you see a big Filter with many Rows Removed by Filter, the index found the rows but the predicate wasn't fully covered — consider a composite index.

Why the planner sometimes refuses your index

• Stale statistics — run ANALYZE; the planner may think the table is tiny or the value common.
• Non-sargable predicates — WHERE date_trunc('day', ts) = $1 can't use an index on ts; index the expression or rewrite as a range.
• Type mismatches — comparing a bigint column to a numeric literal can defeat the index.
• Low selectivity — if the value matches a large fraction of rows, a scan is genuinely cheaper and the planner is right.

Index order and ORDER BY

An Index Scan returns rows in index order, so it can satisfy an ORDER BY for free — no Sort node. Combined with LIMIT, that makes "latest N" queries almost instant, if the index's column order and direction match the ORDER BY.

• Bad row estimate — wrong row counts can make this the wrong choice

Paste a plan into the analyzer →

random_page_costeffective_cache_size

How we tune these →

Why does PostgreSQL ignore my index?

Common reasons: stale statistics, a condition that isn't sargable (a function or type cast on the column), the query matching too many rows (a scan is cheaper), or the index column order not matching the predicate.

• Index Only Scan
• Bitmap Heap Scan
• Sequential Scan