Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BEF4C2E0060 for ; Tue, 25 Mar 2008 06:35:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 89732-10 for ; Tue, 25 Mar 2008 06:34:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [202.61.230.242]) by postgresql.org (Postfix) with ESMTP id 072CC2E007D for ; Tue, 25 Mar 2008 06:34:38 -0300 (ADT) Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by www.postnewspapers.com.au (Postfix) with ESMTP id E35B7D55C9; Tue, 25 Mar 2008 18:34:33 +0900 (WST) Received: from [10.0.0.53] (wallace.localnet [10.0.0.53]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 23BA81E0434; Tue, 25 Mar 2008 18:34:35 +0900 (WST) Message-ID: <47E8C72B.2010105@postnewspapers.com.au> Date: Tue, 25 Mar 2008 18:34:35 +0900 From: Craig Ringer User-Agent: Icedove 1.5.0.12 (X11/20070607) MIME-Version: 1.0 To: depesz@depesz.com Cc: sathiya psql , pgsql-performance@postgresql.org Subject: Re: postgresql is slow with larger table even it is in RAM References: <20080325090823.GA5431@depesz.com> In-Reply-To: <20080325090823.GA5431@depesz.com> X-Enigmail-Version: 0.94.2.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/461 X-Sequence-Number: 29415 hubert depesz lubaczewski wrote: > On Tue, Mar 25, 2008 at 02:05:20PM +0530, sathiya psql wrote: >> Any Idea on this ??? > > yes. dont use count(*). > > if you want whole-table row count, use triggers to store the count. > > it will be slow. regeardless of whether it's in ram or on hdd. In other words, if you're having performance problems please provide EXPLAIN ANALYZE output from a more useful query that does real work, rather than something like count(*). COUNT(*) can be slow due to some MVCC limitations; it's been discussed frequently here so you should search the archives for information. -- Craig Ringer