Re: Resolving Index Bloat
От | Samuel Stearns |
---|---|
Тема | Re: Resolving Index Bloat |
Дата | |
Msg-id | CBAC86BE623FDB4E8B6225471691724291E1BE91@EXCHMBX-ADL6-01.staff.internode.com.au обсуждение исходный текст |
Ответ на | Re: Resolving Index Bloat (Samuel Stearns <SStearns@internode.com.au>) |
Список | pgsql-admin |
Er, that should read 'November', not 'October'. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Samuel Stearns Sent: Tuesday, 20 November 2012 11:26 AM To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Resolving Index Bloat Thanks, Tom. The database was dumped/restored on Monday, 12 October and the autovac settings applied right after. The query showing thebloat was issued on Monday, 19 October so a time period of 1 week elapsed since baseline. Data types in the format <index_name>- <data_type> as follows: billingitemrating_tariff_idx - integer billingitemrating_itemdescription_idx - integer billingitemrating_pkey1 - bigint billingitemrating_psi_idx - integer billingitemrating_bpid_idx - integer importitem_pkey - integer importitem_status_ignored_idx - multicolumn (integer, text) importitem_subscriptionid_idx - text idx_importitem_importitemgroup - integer idx_importitem_importitemgroup_status - multicolumn (integer, integer) idx_importitem_status - integer billingitemrating_biid_idx - integer billingitemrating_ebid_idx - integer I'll have a look at pgstattuple Sam -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Tuesday, 20 November 2012 10:50 AM To: Samuel Stearns Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Resolving Index Bloat Samuel Stearns <SStearns@internode.com.au> writes: > We have a problem with index bloat on a couple of our tables even though we have applied more aggressive autovac/analyzesettings in the schema: Hard to tell much about this without knowing the baseline condition or what's happened since the baseline. It looks likeyour tables have grown circa 2X (eg billingitemrating), but is that due to new data or heavy update activity? If the baseline condition is freshly-built-or-REINDEXed indexes, a fair amount of "bloat" is to be expected. The traditionalrule of thumb about btree indexes is that the steady-state load factor is about two-thirds full. By default,PG builds indexes tightly packed --- so just allowing the index to reach steady state will incur 50% "bloat" on average. It's usually counterproductive to try to maintain a fill factor better than that, unless the table receivesonly minimal insert/update traffic. (Indeed, usually the better policy for a heavy-update table is to create theindexes with 66% fillfactor to begin with.) Your indexes on billingitemrating seem to have expanded a bit more than what would be expected from the combination of thesefactors, but I'm not sure they're enormously out of line. You could delve a bit deeper by using contrib/pgstattupleto measure the actual dead space in both the tables and the indexes. Also, it'd be useful to know thedata types of the columns being indexed. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
В списке pgsql-admin по дате отправления: