Re: Index Bloat - how to tell?

[Mark Kirkwood <mark.kirkwood@xxxxxxxxxxxxxxx>]

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