In response to Tom Lane <tgl@xxxxxxxxxxxxx>: > Bill Moran <wmoran@xxxxxxxxxxxxxxxxxxxxxxx> writes: > > Just an FYI ... I remembered what prompted the cron job. > > > We were seeing significant performance degradation. I never did actual > > measurements, but it was on the order of "Bill, why is restoring taking > > such a long time?" from other systems people. At the time, I poked around > > and tried some stuff here and there and found that reindex restored > > performance. I didn't look at actual size at that time. > > A reindex might improve performance for reasons other than bloat --- to > wit, that a freshly-built index is in perfect physical order, which > tends to get degraded over time by page splits. How important that is > depends on your usage patterns. This goes back to the heart of the original question, which was: "how often do I need to reindex." The answer was "rarely, if ever" and "if you're really worried about this, you can monitor _size_ via these queries ..." I guess I focused too much on size in my response. As you point out, bloat isn't the only indicator that an index would benefit from being rebuilt. > If this is what the story is for your > situation, then what might fix it (in 8.2) is to create the index with > FILLFACTOR 50 or so, so that it's already at the steady state density > and won't need many page splits. Interesting. So a major factor in performance degradation is when the index has to split pages. I read about FILLFACTOR in the docs, but it didn't click as to what use it was until your statement. We're still evaluating 8.2. We've hit a few issues with our application and plpgsql, but I think those are minor. As far as deploying it for our Bacula systems -- I just need to find the time to be sure that it doesn't introduce any problems, but I suspect there's a low chance of that with Bacula. > > Anyway, I'll report back in a few weeks as to what the numbers look like. > > Yeah, please for the moment just watch what happens with the default > behavior. Yup. -- Bill Moran http://www.potentialtech.com
