In response to Tom Lane <tgl@xxxxxxxxxxxxx>: > Bill Moran <wmoran@xxxxxxxxxxxxxxxxxxxxxxx> writes: > > In response to Tom Lane <tgl@xxxxxxxxxxxxx>: > >> Can you describe the usage pattern of that index? I'm curious why it > >> doesn't maintain reasonably static size. How often is the underlying > >> table vacuumed? > > ... > > There are 21 jobs, each ranging in size from 2000 - 5000 files. Each job > > runs twice a day. So you're looking at about 60,000 new rows at midnight > > and 60,000 new rows at noon each day. With the purge cycle, about the > > same number of rows are being deleted as are being added, so the table > > size stays pretty constant. > > ... > > Note that the index under discussion is the only one in this database that > > shows significant bloat. > > Yeah, and there's no obvious reason in what you say why this one should > bloat either. Can you say anything about the distribution of the index > columns --- are you working with a fairly static set of filenameids, or > does that change over time? How about the pathids? How does the > combination of filenameid x pathid behave? > > A bit of quick arithmetic says that the minimum possible size of that > index (at 100% fill factor) would be about 20K pages. What you showed > us was that it had expanded to 40-some K pages, or a bit under 50% fill > factor. This is low but not totally out of line; the traditional rule > of thumb is that the steady state fill factor will be about 2/3rds for a > heavily updated btree. If you leave it go, does it continue to get > larger, or stay around 40K? 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. Anyway, I'll report back in a few weeks as to what the numbers look like. -- Bill Moran Collaborative Fusion Inc.
