[check_postgres] index bloat reported by check_postgres even after CLUSTER and REINDEX

[Aleksey Tsalolikhin]

Hi,

  First of all, thanks for a great tool, we use check_postgres.pl for
monitoring our databases, and it's fantastic.

  check_postgres (2.19.0) reports bloat on an index.    I can't get
out of the WARNING state.  I've run CLUSTER on the table, and REINDEX
of the index, and then check_postgres still reports bloat on the
index.  Could it be a false positive?

  check_postgres.pl says:
POSTGRES_BLOAT WARNING: ... index mytable00_pkey rows:? pages:760522
shouldbe:605928 (1.3X) wasted bytes:1266434048 (1 GB)

The following query fmcgeough shared in #postgresql shows the index is
larger than the table:

     nspname      |        table_name        |
index_name                 | index_ratio | index_size | table_size |
index_size_raw
------------------+--------------------------+--------------------------------------------+-------------+------------+------------+----------------
 public           | mytable00                | mytable00_pkey
                   |        1.03 | 5942 MB    | 5751 MB    |
6230384640


with indexBloat as
(
SELECT nspname,
       c.relname as table_name,
       i.relname as index_name,
       ROUND(100 * pg_relation_size(indexrelid) /
pg_relation_size(indrelid)) / 100 AS index_ratio,
       pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
       pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
       pg_relation_size(indexrelid) as index_size_raw
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace N ON (n.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND i.relkind = 'i'
AND c.relkind = 'r'
AND pg_relation_size(indrelid) > 0
)
SELECT *
 FROM indexBloat
WHERE index_ratio > 1 and index_size_raw > 35000
ORDER BY index_ratio desc;


Here is my schema:

               Table "public.mytable00"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 column1        | character varying           | not null
 column2   | timestamp without time zone | not null
 column3    | bigint                      | not null
 column4     | character varying           | not null
 column5     | character varying           | not null
 column6    | bigint                      | not null
 column7 | character varying           | not null
Indexes:
    "mytable00_pkey" PRIMARY KEY, btree (column1, "column2", column3,
column4, column5, column6, column7) CLUSTER
    "mytable00_timestamp_index" btree ("timestamp")