Re: Weird indices

Joseph Shraibman <jks(at)selectacast(dot)net> writes:

A caveat on this reply: I've been studying the Postgres internals, but
I have not mastered them.

> I understand that keeping different views for different open
> transactions can be difficult, but after a transaction that updates a
> row is over why isn't the row marked as 'universally visible' for all
> new transactions until another update occurs?

It is. This mark is on the tuple in the heap. When a tuple is
current, and not locked for update, HEAP_XMAX_INVALID is set. After
the tuple is removed, HEAP_XMAX_COMMITTED is set.

> 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?

There is a 1 bit field indicating that a tuple is an old value.
Postgres can also determine whether any transaction can see the
tuple. It does this by storing the transaction ID in the t_xmax
field. If all current transactions are newer than that transaction
ID, then that tuple is no longer visible to any transaction.

In fact, I believe that is what the VACUUM command looks for.

> Maybe this is part of the whole 'vacuum later' vs. 'update now'
> philosophy. If the point of vacuum later is to put off the performance
> hit until later if it is causing these performance hits on queries
> because index scans aren't being used then doesn't that mean 'update
> now' is more likely to pay off in the short run?

I don't follow. A simple VACUUM doesn't update the statistics.
VACUUM ANALYZE has to do more work.

Are you suggesting that the statistics should be updated continuously?
I guess that would be doable, but it would clearly slow down the
database. For some applications, it would be an obviously bad idea.

Ian