Re: count(*) performance improvement ideas

Pavan Deolasee wrote: <blockquote cite="mid:2e78013d0803120852h11a1022fw952900d925405294@mail.gmail.com"
type="cite"><prewrap="">On Wed, Mar 12, 2008 at 9:14 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=""> If
youare talking about automatically doing this for every table - Ihave an objection that the performance impact seems
unwarrantedagainstthe gain. We are still talking about every insert or update updatingsome counter table, with the only
mitigatingfactor being that thetrigger would be coded deeper into PostgreSQL theoretically making itcheaper?
</pre></blockquote><prewrap="">
 
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. </pre></blockquote><br /> Fine - once per
transactioninstead of once per insert. Still, if there is overhead to this (updating a secondary summary table), does
itreally 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
impactmy regular queries. Again, I don't think count(*) on the whole table is a particularly useful case. count(*) on
particularsubsets of the data may be, but of the whole table?<br /><br /> If you can make a secondary summary table to
beused for count(*) optional, great. If using HOT makes the secondary table more efficient, great.<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>