Thread: index question..

index question..

From
"Williams, Travis L, NPONS"
Date:
Do indexs need to be dropped/recreated or reindex'd or something to keep them current?

Travis

Re: index question..

From
"Shridhar Daithankar"
Date:
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.


Re: index question..

From
Stephan Szabo
Date:
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.


Re: index question..

From
"scott.marlowe"
Date:
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.


Re: index question..

From
"scott.marlowe"
Date:
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.


Re: index question..

From
"Williams, Travis L, NPONS"
Date:
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.


Re: index question..

From
"scott.marlowe"
Date:
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.


Re: index question..

From
Tom Lane
Date:
"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