Re: Seqscan in MAX(index_column)

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073