Re: Much Ado About COUNT(*)

On Wed, Jan 19, 2005 at 10:16:38AM -0600, Bruno Wolff III wrote:
> On Wed, Jan 19, 2005 at 14:59:17 -0000,
> Mark Cave-Ayland <m(dot)cave-ayland(at)webbased(dot)co(dot)uk> wrote:
>
> > So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to
> > know the current number of person records. How much quicker would a COUNT(*)
> > be if visibility were included in the indices as opposed to a "hacked"
> > approach like this?
>
> You are only going to get a constant factor speed up unless the space savings
> allows much better use of cache. You probably want to look at using
> triggers to maintain counts in another table.

I'd try using a "start value" and a differences list. So the
differences list would be initially empty and the start value would be
0. On insert or delete, you create a new difference (with +1 or
whatever). Periodically, the differences would be added to the start
value and the records deleted. Thus the time to calculate the total
count is much smaller, and it follows MVCC rules. Of course there are
lots of minor details not mentioned here.

Not sure if I'd model this with a single table or two.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"I would rather have GNU than GNOT." (ccchips, lwn.net/Articles/37595/)