On Dec 20, 2007 3:44 AM, Mike C <[EMAIL PROTECTED]> wrote: > Hi, > > Can someone clarify HOT updates for me (and perhaps put more detail > into the docs?). Is this statement correct: the HOT technique is used > on *any* table so long as no indexed column is affected. >
Its partially correct. HOT is used on system and user tables. "No index column change" is a necessary but not sufficient condition for HOT update. There must be enough free space in the same block where the old tuple exists. Though we hope that the system will stabilize in terms of availability of free space in the blocks, it might be worthy to leave free space of at least one tuple size by using appropriate fill factor at the table creation time. > create table T (A int, B int); > create index TB on T (B); > insert into T (A,B) Values (1,2); > > So if I do an update that is identical to the existing row, nothing changes? > update T set A=1, B=2 where A=1; > HOT update *is not* update-in-place. So every update, HOT or COLD, would generate a new version of the row. The power of HOT comes when the index column is not changed. This allows us to skip index inserts for the new version (thus preventing index bloats). Its also far easier to vacuum the dead HOT tuples without running VACUUM or VACUUM FULL. This gives us the ability to prevent heap bloats. > If I change the non-indexed field, A, then HOT applies and no new tuple > needed? > update T set A=2, B=2 where A=1; > HOT applies, but new tuple is needed as described above. > If I change the indexed field, B, then HOT doesn't apply and a new > tuple is needed? > > update T set A=2,B=3 where A=2; Right. > > Actually, what actually happens when you get an update with redundant > information, e.g. > > update T set A=2,B=4 where A=2; > > The value of A hasn't changed, does postgres still write the value? > Yes. Every update generates a new version of the row. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
