Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D8EF59F9E92 for ; Wed, 12 Sep 2007 16:27:18 -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 03849-07 for ; Wed, 12 Sep 2007 16:27:16 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169]) by postgresql.org (Postfix) with ESMTP id B89069F9C84 for ; Wed, 12 Sep 2007 16:27:15 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so312550ugf for ; Wed, 12 Sep 2007 12:27:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; 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=UjzqeLfI63Htj0ngLDETKf35hD3ogAsrjqpj/PxioYQ=; b=sm/gWZd4G83QzAhg7jne2/MmFJaysL+hnWrY0Th9/xbNSG7U+ddmvqXhLr3I6Rj6LxQKECsA/iQnl+OoF1d2hjcKqOXVvUUFqd5np5lSG4xJaX8LN5oPH+97L9+x7WmbriElQcbg/k7OX7w42/sZmNHdcw7PceczIrK2Oa8DfT4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=uhg/sYVcK6eORtI1K8g7VhZy9Ot3TU6JQzhJUrpSqT/5mmE6LJyKksYPHeWw2VqoPAvxqDQtLNHvWgVwu7U0N4KCX2VL5lFoCWpVb0SLqZHYv/H2MlWBjyYWhnqE0vlUADAAAKSB9NgKTtuyBj5mzKoBoOui0g1CsZTjL1tv/ro= Received: by 10.66.236.13 with SMTP id j13mr2167552ugh.1189625234422; Wed, 12 Sep 2007 12:27:14 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Wed, 12 Sep 2007 12:27:14 -0700 (PDT) Message-ID: Date: Wed, 12 Sep 2007 14:27:14 -0500 From: "Scott Marlowe" To: "Frank Schoep" Subject: Re: [Again] Postgres performance problem Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <46E636CB.8080008@rentalia.com> <43393.192.121.104.48.1189495883.squirrel@zigo.dhs.org> <46E64891.9080005@rentalia.com> <20070911225242.GG38801@decibel.org> <46E79865.906@rentalia.com> <2ca799770709121156l7ddbb20agf8086e8bfcbc881a@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/231 X-Sequence-Number: 26776 On 9/12/07, Frank Schoep wrote: > On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote: > > On 9/12/07, Mikko Partio wrote: > >> =85 > >> Aren't you mixing up REINDEX and CLUSTER? > > > > =85 > > Either one does what a vacuum full did / does, but generally does > > it better. > > On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE > I'd like to ask if CLUSTER is safe to run on a table that is in > active use. > > After updating my maintenance scripts from a VACUUM FULL (add me to > the list) to CLUSTER (which improves performance a lot) I noticed I > was getting "could not open relation =85" errors in the log while the > scripts ran so I reverted the change. This was on 8.1.9. > > Am I hitting a corner case or is it generally not a good idea to > CLUSTER tables which are being queried? I haven't had problems with > the REINDEX / VACUUM FULL combination while CLUSTER / VACUUM ANALYZE > resulted in errors on the first run. > > Can the "could not open relation=85" error bring down the whole > database server? I'm really interested in using CLUSTER regularly as > it speeds up my system by a factor of two because of more efficient I/O. No, it won't bring it down. Basically the query lost the relation is was operating against because it disappeared when the cluster command runs.