Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id EBE73B5DC13
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Sep 2011 09:43:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 62954-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Sep 2011 12:43:11 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2
Received: from kolab.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205])
	by mail.postgresql.org (Postfix) with ESMTP id 5B862B5DC1E
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Sep 2011 09:43:10 -0300 (ADT)
Received: from localhost (kolab.op.lan [127.0.0.1])
	by kolab.mnc.ch (Postfix) with ESMTP id E86446D26A
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Sep 2011 14:43:08 +0200 (CEST)
X-Virus-Scanned: by amavisd-new at mnc.ch
Received: from kolab.mnc.ch ([127.0.0.1])
	by localhost (kolab.mnc.ch [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id dAfMgHcDmeUy for <pgsql-performance@postgresql.org>;
	Wed, 21 Sep 2011 14:43:08 +0200 (CEST)
Received: from localhost (kolab.op.lan [127.0.0.1])
	by kolab.mnc.ch (Postfix) with ESMTP id 2CC7D6DDA0
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Sep 2011 14:43:08 +0200 (CEST)
Received: from gc.localdomain (62-2-77-204.static.cablecom.ch [62.2.77.204])
	by kolab.mnc.ch (Postfix) with ESMTP id 0A45B6D26A
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Sep 2011 14:43:08 +0200 (CEST)
Received: by gc.localdomain (Postfix, from userid 500)
	id B5B78184246; Wed, 21 Sep 2011 14:43:07 +0200 (CEST)
To: pgsql-performance@postgresql.org
Subject: Show_database_bloat reliability? [was: Re: REINDEX not working for
	wastedspace]
References: 
 <CAGoODpcBxGLyF=bY3_5opO4YckgJqFGwBxveU_cYsrXBbN4NgQ@mail.gmail.com>
From: Guillaume Cottenceau <gc@mnc.ch>
X-Hashcash: 
 1:21:110921:pgsql-performance@postgresql.org::Lu+bOrnCnURXQGXr:000000000000000000000000000009aCq
Date: Wed, 21 Sep 2011 14:43:07 +0200
In-Reply-To: 
 <CAGoODpcBxGLyF=bY3_5opO4YckgJqFGwBxveU_cYsrXBbN4NgQ@mail.gmail.com>
	(AI Rumman's message of "Wed\, 21 Sep 2011 13\:01\:01 +0600")
Message-ID: <m37h5283s4.fsf@mnc.ch>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201109/313
X-Sequence-Number: 45089

AI Rumman <rummandba 'at' gmail.com> writes:

> Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got the

Is this stuff to show database bloat reliable? After a VACUUM
FULL of the table reported as the top responsible of bloat,
performing the same request again still gives the same result
(still that table is the top responsible of bloat):

 current_database | schemaname |                tablename                | tbloat | wastedbytes |                        iname                         | ibloat | wastedibytes 
------------------+------------+-----------------------------------------+--------+-------------+------------------------------------------------------+--------+--------------
  test            | public     | requests                                |    1.1 |    14565376 | requests_pkey                                        |    0.4 |            0
  test            | public     | requests                                |    1.1 |    14565376 | idx_whatever                                         |    0.8 |            0
  test            | public     | requests                                |    1.1 |    14565376 | idx_whatever2                                        |    0.6 |            0
...

A few investigations show that when tbloat is close to 1.0 then
it seems not reliable, otherwise it seems useful.

pg 8.4.7

-- 
Guillaume Cottenceau