Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 93E772E3342 for ; Thu, 3 Jan 2008 22:31:19 -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 85543-03 for ; Thu, 3 Jan 2008 22:31:16 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.177]) by postgresql.org (Postfix) with ESMTP id D5C1D2E3219 for ; Thu, 3 Jan 2008 22:31:14 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so10395363waf.5 for ; Thu, 03 Jan 2008 18:31:13 -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:content-transfer-encoding:content-disposition:references; bh=oyGJyprZmt+6mJ3fy7WaG+f8e85SiBWD1bw5eiM1r4Y=; b=DaMcy/uiFxwH5uD51fpdPO1XKaLasGzX8AvRCEc6DOAunflD0u78QCEXmV6hjg+0MnlZaEXmIfGjLn4cNoqWcMRXf1nAzDNCz26Jo4nAffK1Xg7JaWaX13+4lvTDMRUwigsoT6A18Lkr1xVH12oge+nKW654XmTLsjLuzab9hes= 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:content-transfer-encoding:content-disposition:references; b=NfNArwWfhLbrXU8JGddEr2bwzpmsCK5uq238VOg10+ZzCE7K8dvKuqwolLAdILcTF9iJxc0QAG9byQPqLZXreUmuxqAKom4LZ2X26slPdRKE0rsnKl8S11wGEQM0i0E343VlX8+CHMJrCBMQnvwUTOGwPsro2Qv7kgaeNIBXJVU= Received: by 10.115.58.1 with SMTP id l1mr15880669wak.110.1199413873201; Thu, 03 Jan 2008 18:31:13 -0800 (PST) Received: by 10.114.131.4 with HTTP; Thu, 3 Jan 2008 18:31:13 -0800 (PST) Message-ID: Date: Thu, 3 Jan 2008 20:31:13 -0600 From: "Scott Marlowe" To: "Brian Modra" Subject: Re: Vacuum taking an age Cc: pgsql-admin@postgresql.org In-Reply-To: <5a9699850801030448k126a5028m85ddb2153e95184f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <5a9699850801030448k126a5028m85ddb2153e95184f@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/47 X-Sequence-Number: 27821 On Jan 3, 2008 6:48 AM, Brian Modra wrote: > Hi, > > > I have a pretty "live" table: rows being inserted and updated more > than once 1 per second, though far, far more inserts than updates. > > There are currently over 3 million rows. > > It has not been vacuumed for months. How many rows per second? 1? all of them? Kinda makes a difference. If it was 1 a second updated for 3 months that's about 7million dead rows. If it was all 3million, then that's 7million * 3million dead rows, also know as a whole bunch of rows. Either way, you probably have a table so terribly bloated that a regular vacuum will not help you in terms of speeding it up. Regular vacuums are like brushing your teeth three times a day. If you've forgotten for three months, brushing them once isn't likely to fix all the cavities you've got. Same thing here. You'll either need a vacuum full or a cluster. Cluster is often faster. Or you can try selecting everything into a temp table, truncating the real table, and inserting the data back in. Truncation will remove all rows, dead or otherwise. The advantage is that it's often faster to truncate / reload than it is to vacuum full. If you have indexes, you might want to drop them while re-inserting and then recreated them.