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

From Mark Mielke
Subject Re: count(*) performance improvement ideas
Date
Msg-id 47E0A524.3010406@mark.mielke.cc
Whole thread Raw
In response to Re: count(*) performance improvement ideas  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: count(*) performance improvement ideas
List pgsql-hackers
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>

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: [COMMITTERS] Re: pgsql: Add URLs for : * Speed WAL recovery by allowing more than one
Next
From: KaiGai Kohei
Date:
Subject: Re: [PATCHES] [0/4] Proposal of SE-PostgreSQL patches