When a secondary index page is read, if the max transaction ID on the page is less than the max transaction ID for which all transactions are visible to the reading transaction (low-water mark, up_limit_id), then the page can be used as is and the page read is index-only. If this condition is not true, then for any entry read from this page the record is read from the clustered index page to determine whether the index entry is visibile. In that case the secondary index read is not index only. Index only matters because when things are not index only there can be an additional random disk read to the clustered index for each entry read from the secondary index.

The max transaction ID for which all transactions are visible to the reading transaction is described as the low-water mark and assigned to the up_limit_field in the read view (read_view_struct). This is the max transaction ID for which there are no unresolved transactions when the reading transaction starts. If there is a long-open transaction when the reading transaction starts, then up_limit_id will be less than the transaction ID of the long-open transaction.

I began to read the code for this today as I want to add a counter for the number of secondary index page reads that are and are not index only. If you want to read the code too the function lock_sec_rec_cons_read_sees determines whether all entries on a secondary index page are definitely visible to a transaction (read view).

If you are interested in this topic, I recommend these books:

• PostgreSQL 9.0 High Performance
• High Performance MySQL