Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8BB0F2E0297 for ; Thu, 6 Mar 2008 02:19:12 -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 04992-10 for ; Thu, 6 Mar 2008 02:19:09 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.242]) by postgresql.org (Postfix) with ESMTP id 519892E0250 for ; Thu, 6 Mar 2008 02:19:09 -0400 (AST) Received: by ag-out-0708.google.com with SMTP id 23so2942971agd.5 for ; Wed, 05 Mar 2008 22:19:08 -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=Yw5L2RIFdsMMzLG77qdthtrSqXos43Kz6GSaa52Ui9E=; b=v1K7S24OUR1drRtJifhnuB9mg8+DA+JAxhe3Xtg67HpoX4TaYDIiQ/ifDU8ZN6QNPJlEygOUSfSvIIVokrebcOXtHZICwBMFKwEh+ltlBtruaIgr7QMOaVXZNprGcr5yqx7nnyE5j7ilyYdETVRbhh+7E7lGBQAKl9osxpbZ5b8= 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=D+Y2YCOYRYQF+HdsC4Kjuo6odTK28Z8A97LqBzoLF4DsHe7VuG9eSZvLffpTNY11hy8M4MytBYMuhXk2FFS+Sd3XIR1FnKwleS3Ifr9Fu4UzTbK0dVc1ZoD8U/S8tpTkY0Ca5GdriaOnEwXe2/OTAjoQHjsUo4qbiebdonI/VnA= Received: by 10.100.47.13 with SMTP id u13mr2665424anu.80.1204784348402; Wed, 05 Mar 2008 22:19:08 -0800 (PST) Received: by 10.100.105.20 with HTTP; Wed, 5 Mar 2008 22:19:08 -0800 (PST) Message-ID: Date: Thu, 6 Mar 2008 11:49:08 +0530 From: "sathiya psql" To: "Shoaib Mir" Subject: Re: count * performance issue Cc: "A. Kretschmer" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3565_23403798.1204784348398" References: <20080306060829.GA21084@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/93 X-Sequence-Number: 29047 ------=_Part_3565_23403798.1204784348398 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline buy every time i need to put ANALYZE... this takes the same time as count(*) takes, what is the use ?? On Thu, Mar 6, 2008 at 11:45 AM, Shoaib Mir wrote: > 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_3565_23403798.1204784348398 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline buy every time i need to put ANALYZE...
this takes the same time as count(*) takes, what is the use ??

On Thu, Mar 6, 2008 at 11:45 AM, Shoaib Mir <shoaibmir@gmail.com> wrote:
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_3565_23403798.1204784348398--