X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 604A7D1D16D for ; Wed, 14 Apr 2004 16:09:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59189-06 for ; Wed, 14 Apr 2004 16:09:02 -0300 (ADT) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by svr1.postgresql.org (Postfix) with ESMTP id 22E95D1D174 for ; Wed, 14 Apr 2004 16:09:00 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 1BDpkq-000PzM-0X; Wed, 14 Apr 2004 20:09:00 +0100 Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id DA96E1675B; Wed, 14 Apr 2004 20:08:59 +0100 (BST) From: Richard Huxton To: Rajesh Kumar Mallah , Postgres Performance Subject: Re: select count(*) very slow on an already vacuumed table. Date: Wed, 14 Apr 2004 20:08:58 +0100 User-Agent: KMail/1.5 References: <407D7A89.60508@trade-india.com> In-Reply-To: <407D7A89.60508@trade-india.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200404142008.58533.dev@archonet.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/164 X-Sequence-Number: 6464 On Wednesday 14 April 2004 18:53, Rajesh Kumar Mallah wrote: > Hi > I have .5 million rows in a table. My problem is select count(*) takes > ages. VACUUM FULL does not help. can anyone please tell me > how to i enhance the performance of the setup. > SELECT count(*) from eyp_rfi; If this is the actual query you're running, and you need a guaranteed accurate result, then you only have one option: write a trigger function to update a table_count table with every insert/delete to eyp_rfi. There is loads of info on this (and why it isn't as simple as you might think) in the archives. First though: 1. Is this the actual query, or just a representation? 2. Do you need an accurate figure or just something "near enough"? -- Richard Huxton Archonet Ltd