On Sunday 12 November 2006 16:23, Simon Riggs wrote:
> 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.
>
I don't think you were trying to mislead, just my interpretation of the scheme
requires a qualifier for that statement, namely that you are not updating an
indexed column. Your statements above didn't include that qualfier, so I just
wanted to make sure I wasn't overlooking something. Actually I think I was,
for example if your not updating all of the indexes on a table (which isn't
likely) you're going to be better off with HOT, but in any case my apologies
if I worded it badly.
> > 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).
>
I'd be keen to tell you such a plan if I had one, but obviously it isn't an
easy problem to solve. :-)
> > 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).
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
