On 6/20/2016 8:51 AM, David G. Johnston wrote:
incorrect. in fact, an update is performed identically to an INSERT + DELETE(old)
Except for heap-only-tuple optimization, right? We cannot build a HOT chain if the user requests a delete separately since their is no longer an association to trace from the old record.
I suspect this affects free space usage to some degree as well but I agree and believe that the reclaimed space is not forbidden to be used (I wouldn't rely on my word though and haven't tried to find relevant documentation).
yeah, HOT only works on updates that don't modify any indexed fields, and only if there's adequate free space in the same block. If you have a update intensive table thats a candidate for HOT, I've been recommending setting that table's fill factor to 50-70% prior to populating it to leave freespace in every block.
--
john r pierce, recycling bits in santa cruz