Re: Weird indices

Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> Maybe I'm not making myself understood. Another way of asking the same
> thing:
> Say there is a transaction that is looking at a non-current version of a
> row. 'non-current' could be the value it was at the start of the
> transaction (and was updated by another transaction) or was updated by
> this transaction but not committed yet. When this transaction is over
> is it really that hard to get rid of the refrence to the old version of
> the row? There should be a 1 bit field 'is old value and isn't being
> used by any transaction'. Is that really hard?

Sure, it's easy to do that sort of bookkeeping ... on a per-row basis.
And we do. What's not so easy (an index helps not at all) is to
summarize N per-row status values into a single count(*) statistic that
you can maintain in a way significantly cheaper than just scanning the
rows when you need the count(*) value. Especially when the per-row
status values interact with the state values of the observing process
to determine what it should think count(*) really is.

The issue is not really "could we make count(*) fast"? Yeah, we
probably could, if that were the only measure of performance we cared
about. The real issue is "can we do it at a price we're willing to pay,
considering the costs of slowdown of insert/update/delete operations,
extra storage space, and extra system complexity?" So far the answer's
been "no".

You might want to look at the manual's discussion of MVCC and at the
Postgres internals talks that were given at OSDN (see slides at
http://www.postgresql.org/osdn/index.html) to learn more about how
things work.

regards, tom lane