| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Jeff Davis <pgsql(at)j-davis(dot)com> |
| Cc: | "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, Bill Moran <wmoran(at)collaborativefusion(dot)com>, Ezequias Rodrigues da Rocha <ezequias(dot)rocha(at)gmail(dot)com>, "Jim C(dot) Nasby" <jim(at)nasby(dot)net>, Dhaval Shah <dhaval(dot)shah(dot)m(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: How often do I need to reindex tables? |
| Date: | 2007-03-02 21:39:38 |
| Message-ID: | 16824.1172871578@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> Isn't a REINDEX still needed in the case of monotonically increasing
> keys, such as in a sequence or timestamp index? I also delete tuples, so
> that results in a forward-shifting range of keys.
No, that shouldn't be a problem, if you're maintaining a constant key
range width (that is, *all* the old entries get deleted). The only
pattern I'm aware of that causes a problem is if you leave a small
subset of the keys behind, for instance insert every few minutes and
then later delete all but one entry per day. In this situation you may
end up with an index containing as few as one entry per page. We
don't have any mechanism short of REINDEX to collapse nonempty index
pages together, so that way lies bloat. But if you delete all the old
entries then the pages get recycled and there shouldn't be a problem.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Joshua D. Drake | 2007-03-02 21:44:30 | Re: [HACKERS] WITH/RECURSIVE plans |
| Previous Message | Tom Lane | 2007-03-02 21:34:12 | Re: Large aggregate query running out of memory in ExecutorState |