Why an index-only scan suddenly scans hundreds of millions of rows
PostgreSQL reader Richard Hennessy is chasing a wildly unstable execution plan tied to a single `Index Only Scan` on a large table named `tikondat`. Depending on the run, the same scan returns anything from 2,673 to 13080 estimated rows—but actual row counts swing from 9,972 up to 261,612,559, all while only 29,915 rows genuinely satisfy the query's condition.
Hennessy's real puzzle is why that last scan balloons to 261 million returned rows and drags in a heavy volume of heap fetches. He wonders whether switching on the `INDEX_CLEANUP ON` option would stabilise performance, given the table receives continuous updates throughout the day.
The issue almost certainly links to visible row versions: with frequent changes, an index-only scan must confirm each index entry against the visibility map before deciding whether the row is live. When the map is stale or unhelpful, PostgreSQL falls back to fetching the heap page for verification—explaining the massive row counts and heap fetches.
Readers who have faced a similar problem may want to weigh in below.
