On Sun, 2006-11-12 at 18:31 -0500, Robert Treat wrote: > if your not updating all of the indexes on a table (which isn't > likely) you're going to be better off with HOT
Do you mean *any* rather than all? > (which isn't likely) There is no chance involved, unless the DBA adding indexes is unaware of the HOT optimization; that would be regrettable, but we would aim to make it fairly clear in the docs. IMHO *most* UPDATEs occur on non-indexed fields. I guess any analysis anybody has of the profile of UPDATEs in specific applications would be interesting, especially if those are widely available applications. My own analysis covers TPC-B, TPC-C, TPC_E and the truckin use case, plus my own experience of other applications. If my assumption is badly wrong on that then perhaps HOT would not be useful after all. If we find that the majority of UPDATEs meet the HOT pre-conditions, then I would continue to advocate it. > > > One common use case that seems problematic is the > > > indexed, frequently updated timestamp field. > > > > Not sure of the use case for that? I understand using a timestamp field > > for optimistic locking; why would you index that rather than the PK? > > > > Let's say you are doing system monitoring and you are updating last contact > times fairly regularly. Sometimes you need to look at specific systems (the > pk) and sometimes you need to query based on a time range (the indexed time > field). OK, thanks. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
