Index-only scans and heap block reads

As you may know, the Index-only scans feature has been committed to the PostgreSQL repository and it's available to all the developers.

I'm very curious about such performance feature, and always want to understand what would change the things better and how it works. So, I just tried the index-only scans to determine what would be changed.

The right figure shows PostgreSQL index scans and determining tuple visibilities. It tells us why each index scan needs to access heap tuples, and how the Index-only scans solve the problem.

Because of the PostgreSQL storage design, tuple visibility information is only stored in the tuple header itself. So, to determine an index item visibility, the corresponding heap tuple header needs to be examined even it is an index scan.


The Index-only scans could avoid such heap access with looking up the visibility map if the corresponding heap page does not have any DELETEd tuple(s) or the page has been VACUUM-ed. As a result, accessing heap pages would be reduced dramatically on index scans.

Ok, it's a fun time! :)

I just tried three index scans issued by `SELECT count(*) FROM accounts' against the pgbench database, and counted block reads on both the index (primary key) and the heap (an accounts table itself).

(1) Counting just after building the table.
(2) Counting just after 10% rows deletion.
(3) Counting just after VACUUMing the table.

The second figure shows that large amount of table (heap) block reads issued after 10% rows deletion. It means that deleting some rows in the table would disable a visibility flag for the corresponding (heap) page in the visibility map. So, the index scan needed to read heap tuple headers to examine the tuple visibilities.

However, after the VACUUM, the visibility map was updated (refreshed), and the index scan could determine visibility of the tuples without reading the heap tuple (Because all tuples must be visible after VACUUM!). That's why the table block access disappeared on (3).

As this experiment, the Index-only scans is very interesting and would improve the performance dramatically. So, I'm looking forward to seeing more reports on this feature from the developers. :)