Re: index bloat WAS: reindexing pg_shdepend

Tom Lane wrote:
> Joseph S <jks@selectacast.net> writes:
>> ... and when I notice that the tuplesperpage for the indexes is low (or
>> that the indexes are bigger then the tables themselves) I know it is
>> time for a VACUUM FULL and REINDEX on that table.
>
> If you are taking the latter as a blind must-be-wrong condition, you are
> fooling yourself -- it's not true for small tables.

I know it isn't true for small tables.  Tables can have a size of 0 but
the minimum size for an index seems to be two pages.  Indexes can also
rival the size of the table when the table when the index is on all the
columns of the table.  But most of the time having an index bigger than
the table itself mean I need a REINDEX.


>
> Have you checked whether the VACUUM FULL + REINDEX actually makes
> anything smaller?

Yes.  I'm mostly seeing the problem on tables of counts that are updated
frequently by triggers on other tables.  It seems autovacuum can't keep
up with the frequency of updates.  The table size itself can shrink by
50%, but the indexes can shrink by 90%.

I just ran my VACUUM FULL/REINDEX script at 11am.  Last time I ran it
was 930pm last night.  Some before/afters:

BEFORE

pg_catalog    pg_class    table    172,032    19.476
pg_catalog    pg_class_oid_index    index    57,344    58.429
pg_catalog    pg_class_relname_nsp_index    index    180,224    18.591

AFTER

pg_catalog    pg_class    table    90,112    41.3
pg_catalog    pg_class_oid_index    index    32,768    103
pg_catalog    pg_class_relname_nsp_index    index    73,728    59

BEFORE

public    acount    table    434,176    119.302
public    acount_pkey    index    172,032    301.095
public    ad_x_idx    index    638,976    36.551

AFTER

public    acount    table    335,872    155.561
public    acount_pkey    index    163,840    318.9
public    a_x_idx    index    131,072    221.143