From: | Decibel! <decibel(at)decibel(dot)org> |
---|---|
To: | Bill Moran <wmoran(at)collaborativefusion(dot)com> |
Cc: | Steven Flatt <steven(dot)flatt(at)gmail(dot)com>, Vivek Khera <vivek(at)khera(dot)org>, Pgsql performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: When/if to Reindex |
Date: | 2007-08-08 20:35:54 |
Message-ID: | 20070808203554.GP20424@nasby.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Aug 08, 2007 at 03:27:57PM -0400, Bill Moran wrote:
> I've had similar experience. One thing you didn't mention that I've noticed
> is that VACUUM FULL often bloats indexes. I've made it SOP that
> after application upgrades (which usually includes lots of ALTER TABLES and
> other massive schema and data changes) I VACUUM FULL and REINDEX (in that
> order).
You'd be better off with a CLUSTER in that case. It'll be faster, and
you'll ensure that the table has optimal ordering.
> Lots of ALTER TABLEs seem to bloat the database size considerably, beyond
> what normal VACUUM seems to fix. A FULL seems to fix that, but it appears
> to bloat the indexes, thus a REINDEX helps.
Hrm, are you sure that's still true? I just did an ALTER TABLE ... TYPE
and it created a new file, meaning no bloating.
> I would expect that setting fillfactor to 100 will encourage indexs to bloat
> faster, and would only be recommended if you didn't expect the index contents
> to change?
Yes.
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
From | Date | Subject | |
---|---|---|---|
Next Message | Steinar H. Gunderson | 2007-08-08 20:36:12 | Re: How to ENABLE SQL capturing??? |
Previous Message | smiley2211 | 2007-08-08 20:02:24 | How to ENABLE SQL capturing??? |