Re: Seqscan in MAX(index_column)

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> You could doubtless maintain a fairly good approximate total this
> >> way, and that would be highly useful for some applications ...
> >> but it isn't COUNT(*).
> 
> > With MVCC allowing multiple rows with only one visible, I thought the
> > INSERT/DELETE system would work --- once the delete becomes visible, the
> > change becomes visible.
> 
> Oh, you're imagining the cache as being a row in an ordinary table?
> I doubt that could work.  Multiple transactions trying to update these
> rows would suffer from contention and deadlock problems, wouldn't they?

Oh, they would, woudn't they.  I was thinking of the counter UPDATE as a
DELETE and an INSERT.  In fact, when we do UPDATE col SET col = col + 1,
we lock the row only so we know the count.  Instead, could we insert
+/-1 records into the cache table that were visible only to the running
transaction, and on commit (ON COMMIT TRIGGER) adjust the cached
aggregate counts without requiring locks?

I know I am just shooting out ideas, but it might give someone else a
better idea.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073