Re: count(*) performance improvement ideas

Tom Lane wrote: <blockquote cite="mid:20927.1205895835@sss.pgh.pa.us" type="cite"><pre wrap="">"Stephen Denne" <a
class="moz-txt-link-rfc2396E"href="mailto:Stephen.Denne@datamail.co.nz"><Stephen.Denne@datamail.co.nz></a>
writes:</pre><blockquote type="cite"><pre wrap="">What I was asking about with those questions, is if a single row is
insertedin transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a
singlerow version get written, or 1001, 1000 of which are removable?   </pre></blockquote><pre wrap="">
 
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. </pre></blockquote><br /> This returns to the question of
whethercount 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.<br /><br /> What is the direction here? Is it count of the whole table
only?(<-- not interesting to me)<br /><br /> Or count of more practical real life examples, which I completely agree
withGreg, that this gets into the materialized view realm, and becomes very interesting.<br /><br /> 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.<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>