[Summary] Chapter#07 "The Internals of PostgreSQL" Heap Only Tuple and Index-Only Scans

Heap Only Tuple(HOT)

Hot reduces the necessity of vacuum processing. HOT effectively use pages of rows and tables when the updated row is stored in the same table page that stores the old row.
HOT reduces the issues of inserting of the index tuples consuming the index page space and vacuum cost of index is high.
PostgreSQL does not insert the corresponding index tuples and sets the HEAP_HOT_UPDATED bit and HEAP_ONLY_TUPLE bit to the t_informask2 field of the old tuple and new tuple.

Pruning of the line pointer

• Find the index tuple that points to the target tuple
• Access the line pointer [1] that is pointed from the getting index tuple.
• Read 'Tuple_1'
• Read 'Tuple_2' via the t_ctid of 'Tuple_1'.

Defragmentation of Dead tuples

Index-Only Scans

Direct use of index key without accessing the corresponding table pages reduce the Input/Output cost, and all the target entries of SELECT statement are included in the index key.

PostgreSQL uses visibility table to avoid dilemma, and when all tuples stored in an page are visible then PostgreSQL uses the key index tuple and does not access table that pointed at from the index tuple to check its visibility.

Reference