Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1798E9F9A60 for ; Thu, 13 Sep 2007 04:21:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 81059-10 for ; Thu, 13 Sep 2007 04:21:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.189]) by postgresql.org (Postfix) with ESMTP id 501239F9609 for ; Thu, 13 Sep 2007 04:21:05 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id l15so284391rvb for ; Thu, 13 Sep 2007 00:21:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:subject:from:to:cc:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding; bh=RuHRUVuXDs2QJGhvWDOkGcxzHhp5SMKY2ngLzIPRZ+0=; b=cO6+fFLa1ceBSNmj0nRcsjTHyKnLvz03QD2wUUNl5bVoXAu12G449trSmWg8vnTF+DZaZeBwUfFI90wxbbXzD7JHyoWtLVl/iW+JZlKbLvjA/zw+st+uEALUTIlM5G3tCK+dKUXQo2qLWTY/cCC8hjB/r8C2ff4CfDNPatEfnvI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:subject:from:to:cc:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding; b=LjePcPh2AZe8FeMP8O2ryFQWGPhFtbsb9r/lR2RsKyku5Nb9MHUcAQKvoEmcSIIHqtY7ZsUSohZg6jDHuqcve47b8w+GNyUAqlIXYtSu7YzN5GDXdidyWw7wr8ZzPPNzHg3piWJiGgVOQYXgj71xXGswbM+2AG011NZkDYBb+uA= Received: by 10.141.197.18 with SMTP id z18mr80041rvp.1189668063944; Thu, 13 Sep 2007 00:21:03 -0700 (PDT) Received: from ?202.146.86.89? ( [202.146.86.89]) by mx.google.com with ESMTPS id f34sm11779816rvb.2007.09.13.00.20.54 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 13 Sep 2007 00:21:03 -0700 (PDT) Subject: Re: [Again] Postgres performance problem From: El-Lotso To: Greg Smith Cc: pgsql-performance@postgresql.org In-Reply-To: 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> Content-Type: text/plain Date: Thu, 13 Sep 2007 15:20:13 +0800 Message-Id: <1189668013.20660.32.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.10.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/239 X-Sequence-Number: 26784 X-No-Archive: yes On Thu, 2007-09-13 at 01:58 -0400, Greg Smith wrote: > On Wed, 12 Sep 2007, Scott Marlowe wrote: > > > I'm getting more and more motivated to rewrite the vacuum docs. I think > > a rewrite from the ground up might be best... I keep seeing people > > doing vacuum full on this list and I'm thinking it's as much because of > > the way the docs represent vacuum full as anything. > > I agree you shouldn't start thinking in terms of how to fix the existing > documentation. I'd suggest instead writing a tutorial leading someone > through what they need to know about their tables first and then going > into how vacuum works based on that data. I'm new to PG and it's true that I am confused. As it stands this is a newbie's understanding of the various terms. cluster -> rewrites a table according to index order so that IO is ordered/sequential reindex -> basically, rewrites the indexes adding new records/fixes up old deleted records vacuum -> does cleaning vacuum analyse -> clean and update statistics (i run this mostly) autovacuum - does vacuum analyse automatically per default setup or some or cost based parameter vacuum full -> I also do this frequently (test DB only) as a means to retrieve back used spaces due to MVCC. (trying lots of different methods of query/add new index/make concatenated join/unique keys and then deleting them if it's not useful) > > As an example, people throw around terms like "index bloat" and "dead > tuples" when talking about vacuuming. I honestly have only the vaguest idea what these 2 mean. (i only grasped recently that tuples = records/rows) > The tutorial I'd like to see > somebody write would start by explaining those terms and showing how to > measure them--preferably with a good and bad example to contrast. The way > these terms are thrown around right now, I don't expect newcomers to > understand either the documentation or the advice people are giving them; > I think it's shooting over their heads and what's needed are some > walkthroughs. Another example I'd like to see thrown in there is what it > looks like when you don't have enough FSM slots. actually, an additional item I would like is to understand explain analyse. The current docs written by tom only shows explain and not explain analyse and I'm getting confuse as to the rows=xxx vs actual rows=yyy where on some of my queries can be very far apart 1 vs 500x ratio on some problematic query[1]. And googling doesn't give much doc on the explain. (the only other useful doc I've seen is a presentation given from oscon 2003) [1](See my other post)