Re: How often do I need to reindex tables?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bill Moran <wmoran(at)collaborativefusion(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How often do I need to reindex tables?
Date: 2007-02-28 20:40:58
Message-ID: 7432.1172695258@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Bill Moran <wmoran(at)collaborativefusion(dot)com> writes:
> In response to Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2007-02-28 21:24:09 Re: How often do I need to reindex tables?
Previous Message George Nychis 2007-02-28 20:19:26 giving a user permission to kill their processes only