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
