In this short article, we will take a look at two features related to index scan. Namely, Heap Only Tuple and Index-Only Scans.
The Heap Only Tuple was initially implemented in Version 8.3 of PostgreSQL and its main purpose was to effectively make use of the pages of both the table and index when the updated table page is stored in the same store as the old row. Heap Only Processing, also known as HOT helps reduce the need of VACUUM processing.
When updating a row without HOT, inserting data into the index tuples takes up the index page space and thus the cost of vacuuming and inserting index tuples are very high. HOT helps reduce the impact of these problems.
When updating a row with HOT, in case the updated row is stored in the same page table that stores the previous row, PostgreSQL will not insert the corresponding index tuple and sets the update bits' value to zero.
Now, let's take a look at Index Only Scans, to reduce the cost of I/O, index-only scans will use the index key directly without having to access the table pages whenever a SELECT statement is called. This method has been provided by all commercial RDBMS systems and PostgreSQL specifically has introduced this feature in the Version 9.2
Top comments (0)