Re: count(*) performance improvement ideas - Mailing list pgsql-hackers

Pavan Deolasee wrote: <blockquote cite="mid:2e78013d0803120959r515667ecl4edb3bd3afbfbdfe@mail.gmail.com"
type="cite"><prewrap="">On Wed, Mar 12, 2008 at 9:53 PM, Mark Mielke <a class="moz-txt-link-rfc2396E"
href="mailto:mark@mark.mielke.cc"><mark@mark.mielke.cc></a>wrote: </pre><blockquote type="cite"><pre wrap="">
 
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?
   </pre></blockquote><pre wrap="">
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 :-) </pre></blockquote><br /> Quite possibly - but, similarly, if PostgreSQL was generally slower due to
suchthings, I might not have chosen PostgreSQL as my favoured db. :-) I think there is a reason PostgreSQL doesn't come
witha fast count(*), and it's that the developers leading up to this point shared the opinion that this feature was not
critical.<br/><br /> I somewhat disagree about my personal reaction, though. I complain about many things, even
configurablethings, such as LISTEN/NOTIFY. I occasionally wish I had such a function, but given it's implementation, I
wouldchoose to use a non-PostgreSQL mechanism for implementation if available. Luckily, it's also "only suffer the
performancecost if you CHOOSE to use it." Seems like a historically valuable trend.<br /><br /><blockquote
cite="mid:2e78013d0803120959r515667ecl4edb3bd3afbfbdfe@mail.gmail.com"type="cite"><pre wrap="">Anyways, your point is
takenand it would be great if can make it configurable,
 
if not table level then at least globally</pre></blockquote><br /> 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
tomany?<br /><br /> If you are REALLY feeling clever, the queries that I would find it to benefit the MOST on, would
includeWHERE conditions. Just like indexes with WHERE conditions. If you get to this point, I think you are reaching
somethingthat will have far more universal benefit to existing applications. CREATE COUNT INDEX blah ON blah WHERE ...
Iwould use this in many places.<br /><br /> Cheers,<br /> mark<br /><br /><pre class="moz-signature" cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>