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 5F1CED1DB96 for ; Thu, 15 Apr 2004 14:44:33 -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 37464-04 for ; Thu, 15 Apr 2004 14:44:36 -0300 (ADT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id 17CA6D1D8AE for ; Thu, 15 Apr 2004 14:44:31 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1) id 1BEAuh-000Mo9-0U; Thu, 15 Apr 2004 18:44:36 +0100 Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 0B9D516BFB; Thu, 15 Apr 2004 18:44:35 +0100 (BST) From: Richard Huxton To: Rajesh Kumar Mallah , Bill Moran Subject: Re: [ SOLVED ] select count(*) very slow on an already Date: Thu, 15 Apr 2004 18:44:33 +0100 User-Agent: KMail/1.5 Cc: Postgres Performance References: <407D7A89.60508@trade-india.com> <407E8FF6.4050601@potentialtech.com> <407EB605.70502@trade-india.com> In-Reply-To: <407EB605.70502@trade-india.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200404151844.33672.dev@archonet.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/183 X-Sequence-Number: 6483 On Thursday 15 April 2004 17:19, Rajesh Kumar Mallah wrote: > Bill Moran wrote: > > Rajesh Kumar Mallah wrote: > >> Hi, > >> > >> The problem was solved by reloading the Table. > >> the query now takes only 3 seconds. But that is > >> not a solution. > > > > If dropping/recreating the table improves things, then we can reasonably > > assume that the table is pretty active with updates/inserts. Correct? > > Yes the table results from an import process and under goes lots > of inserts and updates , but thats before the vacuum full operation. > the table is not accessed during vacuum. What i want to know is > is there any wat to automate the dumping and reload of a table > individually. will the below be safe and effective: Shouldn't be necessary assuming you vacuum (not full) regularly. However, looking back at your original posting, the vacuum output doesn't seem to show any rows that need removing. # VACUUM full verbose eyp_rfi; INFO: vacuuming "public.eyp_rfi" INFO: "eyp_rfi": found 0 removable, 505960 nonremovable row versions in 71987 pages DETAIL: 0 dead row versions cannot be removed yet. Since your select count(*) showed 505960 rows, I can't see how dropping/replacing could make a difference on a sequential scan. Since we're not using any indexes I don't see how it could be related to that. > begin work; > create table new_tab AS select * from tab; > truncate table tab; > insert into tab select * from new_tab; > drop table new_tab; > commit; > analyze tab; > > i havenot tried it but plan to do so. > but i feel insert would take ages to update > the indexes if any. It will have to update them, which will take time. > BTW > > is there any way to disable checks and triggers on > a table temporarily while loading data (is updating > reltriggers in pg_class safe?) You can take a look at pg_restore and copy how it does it. -- Richard Huxton Archonet Ltd