In the previous part we learnt about VACUUM processing that helps with cleaning the dead tuples. Heap Only Tuple (HOT) was introduced in version 8.3 to reduce the need for VACUUM processing. It optimises the usage of index and table pages. Index-Only Scans were introduced in 9.2 to reduce the I/O costs by directly using the index key with the table pages when all entries of SELECT are available in the index. In this part lets explore these two features.
Heap Only Tuple (HOT)
Instead of inserting the new tuple in the table in case it needs to be inserted in the same table where the old one exits, HOT takes a different approach. It uses two bits HEAP_HOT_UPDATED bit and the HEAP_ONLY_TUPLE bit to manage the states. They both occur in old and new tuples respectively in the t_informask2 field. It is important to note that HOT is not available in some cases such as when the key value of the index tuple is updated.
Index-Only Scans
When a select query is being executed using the index, Postgres checks the visibility of the tuples using the visibility map and if all the tuples on the page are visible, ii directly uses index tuple's key without using the table page. Otherwise it has to read the page for visibility. This reduced the I/O overhead and costs.
Top comments (0)