What is the difference between Seq Scan, Index Scan and Bitmap Heap Scan in a PostgreSQL plan?
Seq Scan reads the whole table, Index Scan follows the index row by row, and Bitmap Heap Scan collects matching row locations from the index first, then reads table pages in physical order. The planner picks by estimated selectivity and cost.
Explain it simply
You need 30 books from a library of 10,000.
A Seq Scan means walking every shelf. Cheap per book, but you see everything. An Index Scan means looking up each book in the catalogue and fetching it one at a time. Great for one or two books, wasteful jumping around for hundreds. A Bitmap scan means writing down all the shelf locations first, sorting them, then collecting books shelf by shelf in one trip.
So the choice depends on how many rows match: very few leads to Index Scan, a moderate number to Bitmap, a large fraction (often above roughly 5 to 10 percent) to Seq Scan.
Example
EXPLAIN SELECT * FROM orders WHERE id = 7;
-- Index Scan using orders_pkey on orders
EXPLAIN SELECT * FROM orders WHERE status = 'shipped'; -- moderate match
-- Bitmap Heap Scan on orders
-- -> Bitmap Index Scan on orders_status_idx
EXPLAIN SELECT * FROM orders WHERE total > 0; -- nearly all rows
-- Seq Scan on ordersWhat to say in the interview
"Seq Scan reads everything, Index Scan fetches matching rows one by one, Bitmap scan batches the fetches in table order. The planner chooses based on how many rows it expects to match. A Seq Scan is not a bug when most rows qualify."
Common mistakes
- Forcing an index when a Seq Scan is genuinely cheaper.