Re: Frequent Update Project: Design Overview ofHOTUpdates

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