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 <dcrooke@gmail.com>)
Список pgsql-performance
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 по дате отправления:

Предыдущее
От: Dave Crooke
Дата:
Сообщение: Re: Index Bloat - how to tell?
Следующее
От: AI Rumman
Дата:
Сообщение: only one index is using, why?