Resolving Index Bloat - Mailing list pgsql-admin

Howdy,

Enviroment:

Postgres 8.4.14

Linux

We have a problem with index bloat on a couple of our tables even though we have applied more aggressive autovac/analyze settings in the schema:

ALTER TABLE billingitemrating SET (autovacuum_vacuum_scale_factor=0.001, autovacuum_analyze_scale_factor=0.0001);

ALTER TABLE importitem SET (autovacuum_vacuum_scale_factor=0.001, autovacuum_analyze_scale_factor=0.0001);

pg_stat_all_tables confirms the tables are being auto-vac’d.

This query shows the index bloat:

(postgres@[local]:5432) [smile] > SELECT "relation",

pg_size_pretty(size) as orig_size,

pg_size_pretty(pg_relation_size(C.oid)) new_size,

pg_size_pretty(pg_relation_size(C.oid) - size) as pretty_diff,

round((pg_relation_size(C.oid))::numeric / size * 100.0) || '%' as increase

FROM pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

join tablesizes_20121113_1500 on (relation = nspname || '.' || relname)

left join pg_tablespace t on (c.reltablespace = t.oid)

WHERE nspname NOT IN ('pg_catalog', 'information_schema')

and size <> 0

order by pg_relation_size(C.oid) - size desc limit 20;

                     relation                     | orig_size | new_size | pretty_diff | increase

--------------------------------------------------+-----------+----------+-------------+----------

public.billingitemrating_tariff_idx              | 56 MB     | 210 MB   | 154 MB      | 375%

public.billingitemrating_itemdescription_idx     | 56 MB     | 209 MB   | 153 MB      | 374%

public.billingitemrating_pkey1                   | 50 MB     | 170 MB   | 120 MB      | 339%

public.billingitemrating_psi_idx                 | 50 MB     | 145 MB   | 95 MB       | 289%

public.billingitemrating_bpid_idx                | 45 MB     | 129 MB   | 84 MB       | 289%

vendor.optuswholesalegatewaydataitem             | 1290 MB   | 1329 MB  | 39 MB       | 103%

public.billingitemrating                         | 179 MB    | 213 MB   | 34 MB       | 119%

public.billingitem                               | 274 MB    | 295 MB   | 21 MB       | 108%

public.importitem_pkey                           | 130 MB    | 147 MB   | 17 MB       | 113%

public.importitem                                | 372 MB    | 387 MB   | 15 MB       | 104%

public.importitem_status_ignored_idx             | 182 MB    | 196 MB   | 14 MB       | 108%

public.importitem_importitemgroup_status_ignored | 182 MB    | 196 MB   | 14 MB       | 108%

public.importitem_subscriptionid_idx             | 163 MB    | 176 MB   | 13 MB       | 108%

public.eventbinding                              | 122 MB    | 135 MB   | 13 MB       | 111%

public.idx_importitem_importitemgroup            | 130 MB    | 142 MB   | 13 MB       | 110%

public.idx_importitem_importitemgroup_status     | 130 MB    | 140 MB   | 10 MB       | 108%

public.idx_importitem_status                     | 130 MB    | 140 MB   | 10 MB       | 108%

public.billingitemrating_biid_idx                | 35 MB     | 45 MB    | 10 MB       | 129%

public.billingitemrating_ebid_idx                | 35 MB     | 45 MB    | 10 MB       | 128%

vendor.optuswholesalegatewaycdrdescriminator     | 254 MB    | 263 MB   | 9576 kB     | 104%

(20 rows)

Time: 849.053 ms

(postgres@[local]:5432) [smile] >

Any ideas on how to resolve?

Thank you,

Samuel Stearns