|
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 |
