Re: Suggestion for optimization

Tom Lane wrote:
> 
> Doug McNaught <doug@wireboard.com> writes:
> > "Dann Corbit" <DCorbit@connx.com> writes:
> >> It would be nice if total table cardinality could be maintained live.
> 
> > How would this work with MVCC?
> 
> It wouldn't.  That's why it's not there.  Under MVCC, table cardinality
> is in the eye of the beholder...

This is true, absolutely, but keeping a running total of the number of records
should not change this fact. It may even make it more accurate.

If count() comes back immediately with *a* number, that number was only
accurate at the time of the transaction. If count() does a full table scan, it
still only comes back with something accurate to the time of the transaction,
but it could be more likely less accurate on a busy/large table because many
more things may have changed during the time used by a full table scan.

The issue of a busy table shouldn't make a difference either. If we aready
accept that count() returns the known count at the beginning time of the
transaction, and not the count() at the end of a tansaction (MVCC), then taking
a count() from a counter which is updated when delete/inserts are performed
just as accurate, or at least just as subject to inaccuracies.