Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 1281137C681 for ; Mon, 12 Nov 2007 12:31:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 41595-04 for ; Mon, 12 Nov 2007 12:31:50 -0400 (AST) Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.231]) by postgresql.org (Postfix) with ESMTP id A6EA837B9F7 for ; Mon, 12 Nov 2007 12:31:49 -0400 (AST) Received: by hu-out-0506.google.com with SMTP id 22so2534783hug for ; Mon, 12 Nov 2007 08:31:47 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; bh=/DstPnPgPfFPtFeh1vG/O5OsYWAkNLevodqjNpnTFik=; b=VWQ2sVqAYydCDJt3U9nQRBvQFXgVaeUNbiKK7g7Zu5F9D4bIxolxTVrEytrIASKRYmjA9USD8/dYGIDyESHsXccdNdQ26esaajPLu/jiki7ikVxsSTBkCtOfCexhFCfe0Ag/raXlma4DklzYmq1efII8jAHKL87rJdcMq7pqrcs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=fP7+nj2pNwA/gCMOEYcrTc6pnjxJf54aQoMa48GyJnZom2Fv71Uqb7oavcFS6kfbtEbvQbDkuH4Qk8xszIH2lXeQAWWmfZJVen3DfB3i3xMe1ggKFNWDsA0aEHh3ltY7dvvCKzFMbEQkJxzrIIOJ/Z/KgQ67CiTFDBOdxJWFEyA= Received: by 10.67.19.11 with SMTP id w11mr511739ugi.1194885107115; Mon, 12 Nov 2007 08:31:47 -0800 (PST) Received: from ?192.168.1.33? ( [82.25.187.136]) by mx.google.com with ESMTPS id e5sm4391234ugf.2007.11.12.08.31.43 (version=SSLv3 cipher=RC4-MD5); Mon, 12 Nov 2007 08:31:45 -0800 (PST) Message-ID: <47387FED.8090408@enterprisedb.com> Date: Mon, 12 Nov 2007 16:31:41 +0000 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.12 (X11/20070730) MIME-Version: 1.0 To: Rafael Martinez CC: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Need to run CLUSTER to keep performance References: <4732E6B4.1030907@usit.uio.no> <4732EF07.3060807@enterprisedb.com> <47332200.9050705@usit.uio.no> <24775.1194535980@sss.pgh.pa.us> <4738735B.6030408@usit.uio.no> In-Reply-To: <4738735B.6030408@usit.uio.no> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/180 X-Sequence-Number: 27595 Rafael Martinez wrote: > DETAIL: 83623 dead row versions cannot be removed yet. Looks like you have a long-running transaction in the background, so VACUUM can't remove all dead tuples. I didn't see that in the vacuum verbose outputs you sent earlier. Is there any backends in "Idle in transaction" state, if you run ps? In 8.1, CLUSTER will remove those tuples anyway, but it's actually not correct. If the long-running transaction decides to do a select on hosts-table later on, it will see an empty table because of that. That's been fixed in 8.3, but it also means that CLUSTER might no longer help you on 8.3. VACUUM FULL is safe in that sense in 8.1 as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com