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? regards, tom lane
