From: | Mark Mielke <mark(at)mark(dot)mielke(dot)cc> |
---|---|
To: | Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: count(*) performance improvement ideas |
Date: | 2008-03-12 17:10:50 |
Message-ID: | 47D80E9A.8020401@mark.mielke.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Pavan Deolasee wrote:
> On Wed, Mar 12, 2008 at 9:53 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)cc> wrote:
>
>>
>> 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?
>>
>>
>
> ISTM that you are complaining because we never had an *fast* count(*)
> and adding that now comes at a cost. Had it been there from day one with
> the same overhead as we are talking about now, nobody would have
> complained :-)
>
Quite possibly - but, similarly, if PostgreSQL was generally slower due
to such things, I might not have chosen PostgreSQL as my favoured db.
:-) I think there is a reason PostgreSQL doesn't come with a fast
count(*), and it's that the developers leading up to this point shared
the opinion that this feature was not critical.
I somewhat disagree about my personal reaction, though. I complain about
many things, even configurable things, such as LISTEN/NOTIFY. I
occasionally wish I had such a function, but given it's implementation,
I would choose to use a non-PostgreSQL mechanism for implementation if
available. Luckily, it's also "only suffer the performance cost if you
CHOOSE to use it." Seems like a historically valuable trend.
> Anyways, your point is taken and it would be great if can make it configurable,
> if not table level then at least globally
If table level, I might even try it out. If global level, I will not be
trying it out. I'm only one person - but perhaps this will help point
you in a direction favourable to many?
If you are REALLY feeling clever, the queries that I would find it to
benefit the MOST on, would include WHERE conditions. Just like indexes
with WHERE conditions. If you get to this point, I think you are
reaching something that will have far more universal benefit to existing
applications. CREATE COUNT INDEX blah ON blah WHERE ... I would use this
in many places.
Cheers,
mark
--
Mark Mielke <mark(at)mielke(dot)cc>
From | Date | Subject | |
---|---|---|---|
Next Message | Justin | 2008-03-12 17:11:05 | transaction support in pl/slql |
Previous Message | Alvaro Herrera | 2008-03-12 17:08:18 | Re: Re: TODO-list on wiki (was: TODO update about SQLSTATE to PGconn) |