Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id D48982E0180
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  6 Mar 2008 02:15:10 -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 05518-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  6 Mar 2008 02:15:07 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.170])
	by postgresql.org (Postfix) with ESMTP id 64E412E014C
	for <pgsql-performance@postgresql.org>;
	Thu,  6 Mar 2008 02:15:07 -0400 (AST)
Received: by wf-out-1314.google.com with SMTP id 28so2100042wff.28
	for <pgsql-performance@postgresql.org>;
	Wed, 05 Mar 2008 22:15:06 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	bh=8pNcQczrYHnZa8Cu7y1W4pgXXRqkvMpZAc2NG1XYp0o=;
	b=EGjWf+KNXZEhk6xUSTuufBetpSp0m1UIo6v/ME9k/v7yp4koXDFd8HVqXD+HzQlRZXbGaidpqJEugWPZA6Yu55r+5c3Kh/3wf/2nkOIjFRzygBfj6XNyWC00k7gZ61fESQAHy8lg16kqNuBE/hbHT9m6PMgqLb4cRmNb1UVvrV0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=nkx2DvZ/uAUz++hT/i0AZJe49xdIvd342Upsgb8mOV8QvARnweyQWVSlrbNDEurAeBXCmYFodGwq1bzeN/LqEMFQllH9aN+S8+yEZuZrXFwvAgVyVL8llx5HG2shs23yb/ZxbfvW7fudtdLopOiwDPA4r+fj/b9GRUsQpMRvTNw=
Received: by 10.142.240.9 with SMTP id n9mr1102145wfh.79.1204784106452;
	Wed, 05 Mar 2008 22:15:06 -0800 (PST)
Received: by 10.142.231.18 with HTTP; Wed, 5 Mar 2008 22:15:06 -0800 (PST)
Message-ID: <bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com>
Date: Thu, 6 Mar 2008 17:15:06 +1100
From: "Shoaib Mir" <shoaibmir@gmail.com>
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Subject: Re: count * performance issue
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20080306060829.GA21084@a-kretschmer.de>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_36757_16876839.1204784106373"
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<f966c2ee0803052143k4570d771o6c300647d3b987d@mail.gmail.com>
	<20080306060829.GA21084@a-kretschmer.de>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/92
X-Sequence-Number: 29046

------=_Part_36757_16876839.1204784106373
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:>

> > am having a table with nearly 50 lakh records,
> >
> > it has more than 15 columns, i want to count how many records are there,
> it is
> > taking nearly 17 seconds to do that...
> >
> > i know that to get a approximate count we can use
> >          SELECT reltuples FROM pg_class where relname = TABLENAME;
> >
> > but this give approximate count, and i require exact count...
>
> 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.
>
>
>
Or do something like:

ANALYZE tablename;
select reltuple from pg_class where relname = 'tablename';

That will also return the total number of rows in a table and I guess might
be much faster then doing a count(*) but yes if trigger can be an option
that can be the easiest way to do it and fastest too.

-- 
Shoaib Mir
Fujitsu Australia Software Technology
shoaibm[@]fast.fujitsu.com.au

------=_Part_36757_16876839.1204784106373
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Thu, Mar 6, 2008 at 5:08 PM, A. Kretschmer &lt;<a href="mailto:andreas.kretschmer@schollglas.com">andreas.kretschmer@schollglas.com</a>&gt; wrote:&gt;<br><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;">

&gt; am having a table with nearly 50 lakh records,<br>
&gt;<br>
&gt; it has more than 15 columns, i want to count how many records are there, it is<br>
&gt; taking nearly 17 seconds to do that...<br>
&gt;<br>
&gt; i know that to get a approximate count we can use<br>
&gt; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT reltuples FROM pg_class where relname = TABLENAME;<br>
&gt;<br>
&gt; but this give approximate count, and i require exact count...<br>
<br>
There aren&#39;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>
<br>
<br></blockquote></div><br>Or do something like:<br><br>ANALYZE tablename;<br>select reltuple from pg_class where relname = &#39;tablename&#39;;<br><br>That will also return the total number of rows in a table and I guess might be much faster then doing a count(*) but yes if trigger can be an option that can be the easiest way to do it and fastest too.<br clear="all">
<br>-- <br>Shoaib Mir<br>Fujitsu Australia Software Technology<br>shoaibm[@]fast.fujitsu.com.au

------=_Part_36757_16876839.1204784106373--