From: | "Ames, Danielle" <dames(at)usgs(dot)gov> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Index Bloat after Reindex |
Date: | 2015-09-02 16:20:55 |
Message-ID: | CAJf0CX5=qH3FzW02mz4CMFfC6U=AD8OCo8u4OpTsBRY2uiZO1A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
Dani Ames
Database Administrator | Stinger Ghaffarian Technologies (SGT)
<http://www.sgt-inc.com/>
Contractor to the U.S. Geological Survey (USGS) <http://www.usgs.gov/>
Earth Resources Observation and Science (EROS) Center
<http://eros.usgs.gov/>
dames(at)usgs(dot)gov | Daytime: 605-594-2597
From | Date | Subject | |
---|---|---|---|
Next Message | Porwal, Utkarsh | 2015-09-02 18:04:04 | PostgresDB - Advise on possible data corruption |
Previous Message | Edgar Delgado | 2015-09-02 13:08:30 | Re: Fwd: Export vaccumdb messages |