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
