Thread: index question..
Do indexs need to be dropped/recreated or reindex'd or something to keep them current? Travis
On 13 Nov 2002 at 5:56, Williams, Travis L, NPONS wrote: > Do indexs need to be dropped/recreated or reindex'd or something to keep them current? AFAIU, indexes will keep themselves current but dropping and recreating them wokrs like vacuum on indexes. IIRC vacuum does not recover deleted index space. Correct me if I am wring.. Bye Shridhar -- Cold, adj.: When the politicians walk around with their hands in their own pockets.
On Wed, 13 Nov 2002, Williams, Travis L, NPONS wrote: > Do indexs need to be dropped/recreated or reindex'd or something to keep them current? They'll stay current, but you may find that the vacuum doesn't reclaim all of the empty space in the index in which case you may want to reindex for space and performance reasons.
On Wed, 13 Nov 2002, Shridhar Daithankar wrote: > On 13 Nov 2002 at 5:56, Williams, Travis L, NPONS wrote: > > > Do indexs need to be dropped/recreated or reindex'd or something to keep them current? > > AFAIU, indexes will keep themselves current but dropping and recreating them > wokrs like vacuum on indexes. IIRC vacuum does not recover deleted index space. > > Correct me if I am wring.. Very close. The problem is that deleted index space should be recovered but sometimes isn't when there are massive amounts of updates at once or between vacuums. There is some generally held belief that increasing the FSM settings in postgresql.conf can help this situation.
On Wed, 13 Nov 2002, Williams, Travis L, NPONS wrote: > Do indexs need to be dropped/recreated or reindex'd or something to keep them current? Indexes are updated on the fly. But sometimes they lose track of deleted index entries and to regain space and improve performance, reindexing may be necessary for certain circumstances.
I thought reindexing was only to be used on corrupt tables.. will vacumming fix the problem? Travis -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Wednesday, November 13, 2002 11:22 AM To: Williams, Travis L, NPONS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] index question.. On Wed, 13 Nov 2002, Williams, Travis L, NPONS wrote: > Do indexs need to be dropped/recreated or reindex'd or something to keep them current? Indexes are updated on the fly. But sometimes they lose track of deleted index entries and to regain space and improve performance, reindexing may be necessary for certain circumstances.
On Wed, 13 Nov 2002, Williams, Travis L, NPONS wrote: > I thought reindexing was only to be used on corrupt tables.. will > vacumming fix the problem? No, unfortunately, for certain classes of problems such as 'update table set field2=field2+1' kind of things, where huge amounts of a table are updated at once, it appears that the deleted space in an index may not be reclaimed, and the only way to get it back is to drop and recreate the index. Reindex is just one of the easier ways to drop and recreate and index. You could always do it in SQL as well. Note that pg_indexes contains all the information needed to recreate an index (i.e. it has the exact sql used to create an index stored away) so you could theoretically use that table to recreate your indexes as well.
"Williams, Travis L, NPONS" <tlw@att.com> writes:
> I thought reindexing was only to be used on corrupt tables.. will
> vacumming fix the problem?
Scott's being unnecessarily vague about the index bloat problem.
The issue is quite simple and easily understood: vacuuming does delete
dead index entries, but it does not collapse out entire unused pages in
indexes. So (a) an index can never get smaller, even if you delete many
entries; (b) if the range of index entries changes over time, the index
will grow. For instance, if you're indexing a timestamp column, the
right end of the btree will constantly get expanded as the maximum
column value increases. But there's no mechanism to make the portion of
the index that covers your original oldest timestamp go away, even if
the entry itself has gone away.
Reindexing fixes this by constructing a whole new index from scratch.
We'd like plain vacuum to remove empty pages too, but doing so without
locking out concurrent accesses to the index is a tricky problem.
Perhaps it will get fixed in 7.4 ...
regards, tom lane