Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id A5E062E0284
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  6 Mar 2008 02:29:00 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 08461-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  6 Mar 2008 02:28:57 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from ironport2-out.pppoe.ca (ironport2-out.pppoe.ca
	[206.248.154.182])
	by postgresql.org (Postfix) with ESMTP id A0D0B2E027C
	for <pgsql-performance@postgresql.org>;
	Thu,  6 Mar 2008 02:28:57 -0400 (AST)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: Ah4FADcez0fO+Igp/2dsb2JhbACCOjSKVJ4SgQE
X-IronPort-AV: E=Sophos;i="4.25,454,1199682000"; d="scan'208,217";a="15646769"
Received: from smtp.pppoe.ca ([65.39.196.238])
	by ironport2-out.pppoe.ca with ESMTP; 06 Mar 2008 01:28:55 -0500
Received: from [206.248.136.41] ([206.248.136.41])
	by smtp.pppoe.ca (Internet Mail Server v1.0) with ESMTP id MJI55956;
	Thu, 06 Mar 2008 01:28:56 -0500
Message-ID: <47CF8EA6.8070503@mark.mielke.cc>
Date: Thu, 06 Mar 2008 01:26:46 -0500
From: Mark Mielke <mark@mark.mielke.cc>
User-Agent: Thunderbird 2.0.0.12 (X11/20080226)
MIME-Version: 1.0
To: Shoaib Mir <shoaibmir@gmail.com>
CC: "A. Kretschmer" <andreas.kretschmer@schollglas.com>,
	pgsql-performance@postgresql.org
Subject: Re: count * performance issue
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>	
	<f966c2ee0803052143k4570d771o6c300647d3b987d@mail.gmail.com>	
	<20080306060829.GA21084@a-kretschmer.de>
	<bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com>
In-Reply-To: <bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------050908040209060106060701"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/95
X-Sequence-Number: 29049

This is a multi-part message in MIME format.
--------------050908040209060106060701
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 
>
>     There aren't a general solution. If you realy need the exact count of
>     tuples than you can play with a TRIGGER and increase/decrease the
>     tuple-count for this table in an extra table.
>

Of course, this means accepting the cost of obtaining update locks on 
the count table.

The original poster should understand that they can either get a fast 
estimated count, or they can get a slow accurate count (either slow in 
terms of select using count(*) or slow in terms of updates using 
triggers and locking).

Other systems have their own issues. An index scan may be faster than a 
table scan for databases that can accurately determine counts using only 
the index, but it's still a relatively slow operation, and people don't 
normally need an accurate count for records in the range of 100,000+? :-)

Cheers,
mark

-- 
Mark Mielke <mark@mielke.cc>


--------------050908040209060106060701
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
&nbsp; <br>
<blockquote
 cite="mid:bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com"
 type="cite">
  <div class="gmail_quote">
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">There
aren't a general solution. If you realy need the exact count of<br>
tuples than you can play with a TRIGGER and increase/decrease the<br>
tuple-count for this table in an extra table.<br>
  </blockquote>
  </div>
</blockquote>
<br>
Of course, this means accepting the cost of obtaining update locks on
the count table.<br>
<br>
The original poster should understand that they can either get a fast
estimated count, or they can get a slow accurate count (either slow in
terms of select using count(*) or slow in terms of updates using
triggers and locking).<br>
<br>
Other systems have their own issues. An index scan may be faster than a
table scan for databases that can accurately determine counts using
only the index, but it's still a relatively slow operation, and people
don't normally need an accurate count for records in the range of
100,000+? :-)<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">&lt;mark@mielke.cc&gt;</a>
</pre>
</body>
</html>

--------------050908040209060106060701--