Re: count(*) performance improvement ideas

Tom Lane wrote:
> "Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz> writes:
>
>> What I was asking about with those questions, is if a single row is inserted in transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a single row version get written, or 1001, 1000 of which are removable?
>>
>
> Umm ... AFAICS there is no need for an UPDATE to touch the count table
> at all. You'd only need ON INSERT and ON DELETE triggers.
>

This returns to the question of whether count of the whole table is
useful, or whether count of a GROUP BY or WHERE is useful. If GROUP BY
or WHERE is useful, then trigger on UPDATE becomes necessary.

What is the direction here? Is it count of the whole table only? (<--
not interesting to me)

Or count of more practical real life examples, which I completely agree
with Greg, that this gets into the materialized view realm, and becomes
very interesting.

In my current db project, I never count all of the rows in a table.
However, I do use count(*) with GROUP BY and WHERE.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>