[jhgb]

Does it really count entries in the index? For example, in Firebird, it has to fetch rows because of row versioning (which happens in data pages, not in indices), and since PostgreSQL does versioning, too, I would have assumed that it's subject to the same limitation if it wants to return a correct answer for the current transaction.

[davidrowley]

Index Only Scans are a thing in PostgreSQL, however, they may still need to visit the heap if the visibility map bit for the heap page indicates that the not all tuples on the heap page are visible to all transactions. When a high percentage of pages are marked as "allvisible" then Index Only Scans can give a good boost to performance.

[jhgb]

So this "visibility map" is a little bit like Netfrastructure/Falcon in-memory versioning, then? I see.

[davidrowley]

The visibility map is just 1 bit per page. Vacuum sets these bits to "1" when it sees that all tuples on the page are visible to all transactions. i.e. all tuple xmins are <= the oldest running transaction and none of the tuples have not been marked as deleted by any transaction yet. The visibility map bit will be unset when a new tuple is added to the page or an existing one is "deleted" or more accurately, has the xmax set with the deleting transaction's ID.

The visibility map is stored on-disk as a different fork of the filenode for the table. Two bits are actually stored per page, 1 for visibility and another to mark if the page only contains only frozen tuples. The frozen bit helps reduce the cost of vacuuming the table for transaction wraparound, which is also mentioned in the blog post.

The query planner does not count these bits to determine if it should perform an Index Only Scan vs an Index Scan. An approximate value is stored in pg_class.relallvisible.

[aidos]

Ha, snap. Makes sense that it looks at the dirtiness of the visibility_map while planning.

[aidos]

I believe it can speed it up by using index only scans along with the visibility_map which effectively tells it which entries are “current” in more broad strokes.