Re: Much Ado About COUNT(*)

Added to TODO based on this discusion:

---------------------------------------------------------------------------

* Speed up COUNT(*)

We could use a fixed row count and a +/- count to follow MVCC
visibility rules, or a single cached value could be used and
invalidated if anyone modifies the table. Another idea is to <--
get a count directly from a unique index, but for this to be
faster than a sequential scan it must avoid access to the heap
to obtain tuple visibility information.

* Allow data to be pulled directly from indexes

Currently indexes do not have enough tuple tuple visibility
information to allow data to be pulled from the index without
also accessing the heap. One way to allow this is to set a bit
to index tuples to indicate if a tuple is currently visible to
all transactions when the first valid heap lookup happens. This
bit would have to be cleared when a heap tuple is expired.

---------------------------------------------------------------------------

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.
>
> 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.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>

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