index question..
Williams, Travis L, NPONS
em current?
Travis
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)
scott.marlowe
> 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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Shridhar Daithankar
> 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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Tom Lane
> 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
scott.marlowe
> 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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Williams, Travis L, NPONS
vacumming fix the problem?
Travis
-----Original Message-----
From: scott.marlowe [mailto:scott....@ihs.com]
Sent: Wednesday, November 13, 2002 11:22 AM
To: Williams, Travis L, NPONS
Cc: pgsql-...@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=20
index entries and to regain space and improve performance, reindexing
may=20