Re: count(*) performance improvement ideas

Pavan Deolasee wrote:
> On Wed, Mar 12, 2008 at 9:14 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
>
>> If you are talking about automatically doing this for every table - I
>> have an objection that the performance impact seems unwarranted against
>> the gain. We are still talking about every insert or update updating
>> some counter table, with the only mitigating factor being that the
>> trigger would be coded deeper into PostgreSQL theoretically making it
>> cheaper?
>>
>
> No, I am not suggesting that. If you read proposal carefully, its one UPDATE
> per transaction. With HOT, I am hoping that the counter table may be
> completely cached in memory and won't bloat much.
>
> Also, we can always have a GUC (like pgstats) to control the overhead.
>

Fine - once per transaction instead of once per insert. Still, if there
is overhead to this (updating a secondary summary table), does it really
make sense to have it for every table? Most of my tables do not require
count(*) on the whole table (actually - none of them do). For the same
reason as I don't want oid, I don't think I would want "fast count"
capabilities to impact my regular queries. Again, I don't think count(*)
on the whole table is a particularly useful case. count(*) on particular
subsets of the data may be, but of the whole table?

If you can make a secondary summary table to be used for count(*)
optional, great. If using HOT makes the secondary table more efficient,
great.

Cheers,
mark

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