Index Bloat after Reindex

Поиск
Список
Период
Сортировка
От Ames, Danielle
Тема Index Bloat after Reindex
Дата
Msg-id CAJf0CX5=qH3FzW02mz4CMFfC6U=AD8OCo8u4OpTsBRY2uiZO1A@mail.gmail.com
обсуждение исходный текст
Список pgsql-admin
Дерево обсуждения
While dealing with bloat on a table, myschema.mytable, we found that an associated index was also bloated. In order to correct the bloat we ran REINDEX myschema.myindex, expecting the bloat to be near zero after the REINDEX. The bloat on the table was reduced to zero. However, the index bloat remained high. 

What would cause this bloat to remain after a REINDEX? 

I have included the details of the index as well as data types below:

Postgres Version: 9.3.4

CREATE UNIQUE INDEX myindex
ON myschema.mytable USING btree (column1 varchar(255), column2 varchar(255))

Script running for index bloat: https://github.com/pgexperts/pgx_scripts/blob/master/bloat/index_bloat_check.sql (fixed sql syntax from current repo version)

Before output:

  index_name    │                 cmnd         │ bloat_pct │ bloat_mb │ index_mb │ table_mb │ index_scans │
 myindex        │ REINDEX INDEX schema.myindex │        78 │ 9373.117 │ 11997.328│ 9146.984 │   184882287 │

After output:

 index_name     │                     cmnd     │ bloat_pct │ bloat_mb │ index_mb │ table_mb  │ index_scans │
  myindex       │ REINDEX INDEX schema.myindex │        66 │ 5037.234 │ 7662.016 │  9146.984 │   184882287 │

Command run:

REINDEX INDEX schema.myindex



В списке pgsql-admin по дате отправления:

Предыдущее
От: Edgar Delgado
Дата:
Сообщение: Re: Fwd: Export vaccumdb messages
Следующее
От: "Porwal, Utkarsh"
Дата:
Сообщение: PostgresDB - Advise on possible data corruption