Re: Much Ado About COUNT(*)

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> Ah, right, I missed the connection. Hmm ... that's sort of the inverse
> >> of the "killed tuple" optimization we put in a release or two back,
> >> where an index tuple is marked as definitely dead once it's committed
> >> dead and the deletion is older than all active transactions.
>
> > Yes, it is sort of the reverse, but how do you get around the delete
> > case?
>
> A would-be deleter of a tuple would have to go and clear the "known
> good" bits on all the tuple's index entries before it could commit.
> This would bring the tuple back into the "uncertain status" condition
> where backends would have to visit the heap to find out what's up.
> Eventually the state would become certain again (either dead to
> everyone or live to everyone) and one or the other hint bit could be
> set again.

Right. Do you think the extra overhead of clearing those bits on
update/delete would be worth it?

> The ugly part of this is that clearing the bit is not like setting a
> hint bit, ie it's not okay if we lose that change. Therefore, each
> bit-clearing would have to be WAL-logged. This is a big part of my
> concern about the cost.

Yep, that was my concern too. My feeling is that once you mark the
tuple for expiration (update/delete), you then clear the index bit.
When reading WAL on recovery, you have to clear index bits on rows as
you read expire information from WAL. I don't think it would require
extra WAL information.

The net effect of this idea is that indexes have to look up heap row
status information only for rows that have been recently
created/expired.

If we did have those bits, it would allow us to read data directly from
the index, which is something we don't do now.

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