X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D5FD49FD003 for ; Mon, 28 Aug 2006 12:56:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 01444-07 for ; Mon, 28 Aug 2006 15:56:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx.mall.cz (mall--l3sw2-2948.tgnet.cz [82.100.0.98]) by postgresql.org (Postfix) with ESMTP id DE84C9FCFFA for ; Mon, 28 Aug 2006 12:56:10 -0300 (ADT) Received: from localhost (asterix [127.0.0.1]) by mx.mall.cz (Postfix) with ESMTP id 5C3D413F61E; Mon, 28 Aug 2006 17:56:09 +0200 (CEST) Received: from mx.mall.cz ([127.0.0.1]) by localhost (asterix [127.0.0.1]) (amavisd-new, port 10025) with ESMTP id 04207-01; Mon, 28 Aug 2006 17:56:06 +0200 (CEST) Message-ID: <44F31218.6040703@mall.cz> Date: Mon, 28 Aug 2006 17:56:08 +0200 From: Michal Taborsky - Internet Mall Organization: Internet Mall, a.s. User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) MIME-Version: 1.0 To: Markus Schaber Cc: pgsql-performance@postgresql.org Subject: Re: Identifying bloated tables References: <44F30022.1000009@mall.cz> <44F31088.5040602@logix-tt.com> In-Reply-To: <44F31088.5040602@logix-tt.com> Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at mall.cz X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.134 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/444 X-Sequence-Number: 20629 Markus Schaber napsal(a): > Hi, Michal, > > Michal Taborsky - Internet Mall wrote: > >> When using this view, you are interested in tables, which have the >> "bloat" column higher that say 2.0 (in freshly dump/restored/analyzed >> database they should all be around 1.0). > > I just noticed some columns in pg_catalog with a bloat value <1 and a > negative "wasted space" - is this due to the pseudo nature of them? It is more likely due to the fact, that these numbers are just estimates, based on collected table statistics, so for small or non-standard tables the statistical error is greater that the actual value. You are usually not interested in tables, which have wasted space of 1000kB or -1000kB. Also the database must be ANALYZEd properly for these numbers to carry any significance. -- Michal Táborský IT operations chief Internet Mall, a.s. Internet Mall - obchody, které si oblíbíte