On Sun, 2006-11-12 at 13:01 -0500, Robert Treat wrote: > On Friday 10 November 2006 08:53, Simon Riggs wrote: > > On Fri, 2006-11-10 at 12:32 +0100, Zeugswetter Andreas ADI SD wrote: > > > 4. although at first it might seem so I see no advantage for vacuum with > > > overflow > > > > No need to VACUUM the indexes, which is the most expensive part. The > > more indexes you have, the more VACUUM costs, not so with HOT. > > > > This isn't exactly true though right?
The above statement is completely true; please don't say I aim to mislead. I've been clear about the pre-conditions for the optimization. This is a straight-up attempt to improve some important use cases. > Since the more indexes you have, the > more likely it is that your updating an indexed column, which means HOT isn't > going to work for you. Well its not a chance thing is it? It's clear that the pre-conditions could in some circumstances be an annoyance, but that in itself isn't an argument against it. I'm especially keen to hear of an optimisation that would work in all cases for heavy updates. (It was I that originally suggested the fillfactor approach to optimising UPDATEs, but regret that although it applies no matter how many indexes you have its not very effective and even that reduces after the first batch of UPDATEs have happened). > 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? Locating things via coordinates was a use-case that would be non-HOT, are you thinking of something similar? It's important to understand which types of things HOT would optimize/not. HOT probably would change the way you design if you need such a thing. Rather than indexing the co-ordinate you'd end up binning the values so the index value would change less often, so most would be HOT with a few non-HOT UPDATEs. Maybe the same would be true with the timestamp, I'm not sure. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq