pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re: Index Bloat - how to tell?
Date: Wed, 15 Dec 2010 11:20:38 +1300
Message-ID: <4D07EDB6.7060805@catalyst.net.nz> (raw)
In-Reply-To: <AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com>
References: <EF37296944B47C40ADDCCB7BFD6289FE046CDF1BCD@EMASC201VS01.exchad.jpmchase.net>
	<BD69807DAE0CE44CA00A8338D0FDD08302E9548A03@oma00cexmbx03.corp.westworlds.com>
	<AANLkTim9cSXi9iswOUvKM4iifVUMPzURWxAaXt_OHwRW@mail.gmail.com>

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

view thread (8+ messages)  latest in thread

Message-ID: <4D07EDB6.7060805@catalyst.net.nz>
Permalink:  ../4D07EDB6.7060805@catalyst.net.nz/
Also on:    postgresql.org/message-id/4D07EDB6.7060805@catalyst.net.nz

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mark.kirkwood@catalyst.net.nz
  Subject: Re: Index Bloat - how to tell?
  In-Reply-To: <4D07EDB6.7060805@catalyst.net.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox