Re: Improving count(*)

On Thu, 2005-11-17 at 16:34 -0500, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > Bearing in mind other RDBMS' approach is to count the number of rows in
> > an index, their cost is probably about the same as scanning table
> > blocks/10 very roughly - so the cost is far from zero for them.
>
> Really? The impression I get is that people who ask for this expect the
> answer to be instantaneous, ie they think the system will maintain a
> running net total for each table. (In a non-MVCC system that isn't
> necessarily an unreasonable thing to do.)

Yeh. I think Informix keeps a running total, IIRC, but certainly Oracle
and various others do not.

People probably have given the impression that count(*) is
instantaneous, but that doesn't mean it actually is - they're just
talking up the problems of pg.

> I really can't get excited about adding this level of complexity and
> overhead to the system just to support COUNT(*)-with-no-WHERE slightly
> better than we do now.

Well, I was pointing out the cross-over with the requirements for a
faster VACUUM also. Taken together, it might be a winner.

> The triggers-and-deltas approach previously proposed seems considerably
> more attractive to me, because (1) it's not invasive and (2) you only
> have to pay the overhead on tables where you want it.

This would need to either be optional whichever way we did it, just as
with the creation of an index. I also think that taking the Oracle path
of adding new features in functions/packages would be a good thing,
rather than over-burdening the parser constantly with new syntax to cope
with.

Did the triggers-and-deltas approach cope with MVCC correctly?

Best Regards, Simon Riggs