Re: Index Bloat - how to tell?

От: Mark Kirkwood
Тема: Re: Index Bloat - how to tell?
Дата: ,
Msg-id: 4D07EDB6.7060805@catalyst.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Index Bloat - how to tell?  (Dave Crooke)
Список: pgsql-performance

Скрыть дерево обсуждения

Index Bloat - how to tell?  (John W Strange, )
 Re: Index Bloat - how to tell?  ("Plugge, Joe R.", )
  Re: Index Bloat - how to tell?  (Mladen Gogala, )
  Re: Index Bloat - how to tell?  (Dave Crooke, )
   Re: Index Bloat - how to tell?  (Mark Kirkwood, )
   Re: Index Bloat - how to tell?  (Mladen Gogala, )
    Re: Index Bloat - how to tell?  (Robert Haas, )
     Re: Index Bloat - how to tell?  (Mladen Gogala, )

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

В списке pgsql-performance по дате сообщения:

От: Mark Kirkwood
Дата:
Сообщение: Re: Index Bloat - how to tell?
От: AI Rumman
Дата:
Сообщение: only one index is using, why?