Re: Weird indices

Err I wan't complaing about count(*) per se, I was just using that as a
simple example of something that should be done with an index. Because
if the index doesn't have to worry about rows that aren't current then
you don't even have to go into the heap because the index alone should
have enough information to do that. If it doesn't have to worry about
rows that aren't visible.

Tom Lane wrote:
>
> 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.
>
Ugh, pdf format. I'll have to remember to look at them next time I'm on
a windows box.

> regards, tom lane

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com