Re: Much Ado About COUNT(*)

> -----Original Message-----
> From: Jeff Davis [mailto:jdavis-pgsql(at)empires(dot)org]
> Sent: 19 January 2005 21:33
> To: Alvaro Herrera
> Cc: Mark Cave-Ayland; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Much Ado About COUNT(*)
>
>
>
> To fill in some details I think what he's saying is this:
>
> => create table foo(...);
> => create table foo_count(num int);
> => insert into foo_count values(0);
> => create table foo_change(num int);
>
> then create a trigger "after delete on foo" that does "insert
> into foo_change values(-1)" and a trigger "after insert on
> foo" that inserts a +1 into foo_change.
>
> Periodically, do:
> => begin;
> => set transaction isolation level serializable;
> => update foo_count set num=num+(select sum(num) from
> foo_change); => delete from foo_change; => commit; => VACUUM;
>
> And then any time you need the correct count(*) value, do
> instead: => select sum(num) from (select num from foo_count
> union select num from foo_change);
>
> And that should work. I haven't tested this exact example, so
> I may have overlooked something.
>
> Hope that helps. That way, you don't have huge waste from the
> second table, and also triggers maintain it for you and you
> don't need to think about it.
>
> Regards,
> Jeff Davis

Hi Jeff,

Thanks for the information. I seem to remember something similar to this
being discussed last year in a similar thread. My only real issue I can see
with this approach is that the trigger is fired for every row, and it is
likely that the database I am planning will have large inserts of several
hundred thousand records. Normally the impact of these is minimised by
inserting the entire set in one transaction. Is there any way that your
trigger can be modified to fire once per transaction with the number of
modified rows as a parameter?

Many thanks,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk