Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 41DF01337C09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 14 Dec 2010 18:28:29 -0400 (AST)
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 26244-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 14 Dec 2010 22:28:21 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.catalyst.net.nz (bertrand.catalyst.net.nz [202.78.240.40])
	by mail.postgresql.org (Postfix) with ESMTP id A144C1337E97
	for <pgsql-performance@postgresql.org>;
	Tue, 14 Dec 2010 18:20:42 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by mail.catalyst.net.nz (Postfix) with ESMTP id 1832B31F8E
	for <pgsql-performance@postgresql.org>;
	Wed, 15 Dec 2010 11:20:40 +1300 (NZDT)
X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz
Received: from mail.catalyst.net.nz ([127.0.0.1])
	by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id XbywcenTfwF6 for <pgsql-performance@postgresql.org>;
	Wed, 15 Dec 2010 11:20:39 +1300 (NZDT)
Received: from [IPv6:2404:130:0:1000:222:15ff:fe5c:2471] (unknown
	[IPv6:2404:130:0:1000:222:15ff:fe5c:2471])
	(Authenticated sender: mark.kirkwood)
	by mail.catalyst.net.nz (Postfix) with ESMTPSA id 7FA9A320F5
	for <pgsql-performance@postgresql.org>;
	Wed, 15 Dec 2010 11:20:38 +1300 (NZDT)
Message-ID: <4D07EDB6.7060805@catalyst.net.nz>
Date: Wed, 15 Dec 2010 11:20:38 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.13) Gecko/20101208 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Index Bloat - how to tell?
References: 
 <EF37296944B47C40ADDCCB7BFD6289FE046CDF1BCD@EMASC201VS01.exchad.jpmchase.net>
	<BD69807DAE0CE44CA00A8338D0FDD08302E9548A03@oma00cexmbx03.corp.westworlds.com>
	<AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com>
In-Reply-To: <AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------040306090906060803080001"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201012/178
X-Sequence-Number: 41630

This is a multi-part message in MIME format.
--------------040306090906060803080001
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 15/12/10 09:12, Dave Crooke wrote:
> There is a plugin called pgstattuple which can be quite informative 
> .... however, it actually does a full scan of the table / index files, 
> which may be a bit invasive depending on your environment and load.
>
> http://www.postgresql.org/docs/current/static/pgstattuple.html
>
> It's in the contrib (at least for 8.4), and so you have to import its 
> functions into your schema using the script in the contrib directory.
>

If you are using 8.4 or later, try the Freespacemap module:

http://www.postgresql.org/docs/current/static/pgfreespacemap.html

I tend to run this query:

         SELECT oid::regclass,
                pg_relation_size(oid)/(1024*1024) AS mb,
                sum(free)/(1024*1024) AS free_mb
         FROM
             (SELECT oid, (pg_freespace(oid)).avail AS free
              FROM pg_class) AS a
         GROUP BY a.oid ORDER BY free_mb DESC;


to show up potentially troublesome amounts of bloat.

regards

Mark

--------------040306090906060803080001
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
    <title></title>
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 15/12/10 09:12, Dave Crooke wrote:
    <blockquote
      cite="mid:AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html; charset=UTF-8">
      There is a plugin called pgstattuple which can be quite
      informative .... however, it actually does a full scan of the
      table / index files, which may be a bit invasive depending on your
      environment and load.<br>
      <br>
      <a moz-do-not-send="true"
        href="http://www.postgresql.org/docs/current/static/pgstattuple.html">http://www.postgresql.org/docs/current/static/pgstattuple.html</a><br>
      <br>
      It's in the contrib (at least for 8.4), and so you have to import
      its functions into your schema using the script in the contrib
      directory. <br>
      <br>
    </blockquote>
    <br>
    If you are using 8.4 or later, try the Freespacemap module:<br>
    <br>
    <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/pgfreespacemap.html">http://www.postgresql.org/docs/current/static/pgfreespacemap.html</a><br>
    <br>
    I tend to run this query:<br>
    <br>
    <pre class="PROGRAMLISTING">        SELECT oid::regclass, 
               pg_relation_size(oid)/(1024*1024) AS mb,
               sum(free)/(1024*1024) AS free_mb 
        FROM 
            (SELECT oid, (pg_freespace(oid)).avail AS free 
             FROM pg_class) AS a 
        GROUP BY a.oid ORDER BY free_mb DESC;

</pre>
    to show up potentially troublesome amounts of bloat. <br>
    <br>
    regards<br>
    <br>
    Mark<br>
  </body>
</html>

--------------040306090906060803080001--