Resolving Index Bloat

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(at)[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(at)[local]:5432) [smile] >

Any ideas on how to resolve?

Thank you,

Samuel Stearns