CodePark/ questions / Query plans & optimisation
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Query plans & optimisation

Why is this index-only scan still visiting the table for almost every row?

Index Only Scan using orders_customer_total_idx on orders
  (actual time=0.040..95.100 rows=50000 loops=1)
  Index Cond: (customer_id = 42)
  Heap Fetches: 49120
The visibility map does not mark those pages all-visible, usually because they changed recently and have not been vacuumed

The visibility map does not mark those pages as all-visible. Index entries carry no visibility information, so PostgreSQL cannot tell from the index alone whether a row is visible to the current transaction. It checks the visibility map (a small file that keeps an "all-visible" bit for each table page). It skips the heap (the table itself) only for pages marked all-visible. VACUUM sets those bits. So on a recently modified table, most rows still need a heap fetch.

"The index does not contain all the selected columns" is wrong. In that case, the planner would not have chosen an index-only scan at all.