By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,562 Members | 963 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,562 IT Pros & Developers. It's quick & easy.

REINDEX slow?

P: n/a
Howdy all!

Quick question regarding REINDEX. I have a large table with 1077455 rows.
I have an index on the table with a WHERE clause that limits the rows to
around 10-50. When I REINDEX this index, it takes on the order of a minute
to do the reindex. Question: Does a REINDEX use the old version to build
the new version, or is it the semantic equivalent to "drop index; build
new index". If the first, any thoughts on why it is taking so long to
reindex? If the second, any particular reason not to use the current
(though possibly bloated) index to reduce work (essentially, all we are
doing is recompacting the same info as the original index with a
rebalance)?

Regards!
Ed

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies

P: n/a
Edmund Dengler <ed*****@eSentire.com> writes:
Question: Does a REINDEX use the old version to build
the new version, or is it the semantic equivalent to "drop index; build
new index".
The latter.
If the second, any particular reason


REINDEX is customarily used to recover from a corrupted-index situation.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

P: n/a
Edmund Dengler <ed*****@eSentire.com> writes:
Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?


You tell me --- are you still seeing bloat problems with 7.4?
If so, can you provide more details about the index schema and
the table's update patterns?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3

P: n/a
Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?

Basically, I just re-indexed a whole bunch of databases on one system, and
now queries are much faster (though the REINDEX itself took about an hour
and a half, and this was a specific index * 15 tables * 20 databases). If
bloat is still an issue, and REINDEX is still a recommended way to improve
performce (which at least a number of articles was suggesting, is this
still the case?), then is it possible to have a REINDEX version that can
utilize the old index if the index is not corrupted? Or would this be too
much work? I would prefer to keep the amounts of locks happening on tables
to a minimum.

Alternatively, if I created a second index, and then dropped the first,
would this be faster (though I would suppose that an ANALYZE would need to
be done to recognize the utility of the new index, thereby negating any
speed improvements)?

Regards!
Ed

On Sun, 11 Apr 2004, Tom Lane wrote:
Edmund Dengler <ed*****@eSentire.com> writes:
Question: Does a REINDEX use the old version to build
the new version, or is it the semantic equivalent to "drop index; build
new index".


The latter.
If the second, any particular reason


REINDEX is customarily used to recover from a corrupted-index situation.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

P: n/a

Edmund Dengler <ed*****@eSentire.com> writes:
Alternatively, if I created a second index, and then dropped the first,
would this be faster (though I would suppose that an ANALYZE would need to
be done to recognize the utility of the new index, thereby negating any
speed improvements)?


Currently analyze doesn't check what indexes exist, it analyzes all the
columns of the table anyways. However I think this won't be true in 7.5 when
there are expression indexes, and there has been talk of changing this in
future to take into account indexes and foreign keys.

I don't think it would be any faster but it might avoid downtime. Reindex
seems to block any use of the index until the reindex completes, while doing
this two-step would avoid blocking queries. I haven't tried it myself though
so I'm not sure what gotchas might arise.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5

P: n/a
>>>>> "TL" == Tom Lane <tg*@sss.pgh.pa.us> writes:

TL> Edmund Dengler <ed*****@eSentire.com> writes:
Searching the web, I find lots of references to index bloat, as
well as recent discussions concerning index page recovery (such
items as reclaiming empty B+-tree pages and such). What is the
current state re bloat?


TL> You tell me --- are you still seeing bloat problems with 7.4?
TL> If so, can you provide more details about the index schema and
TL> the table's update patterns?

I had *awful* bloat with 7.2. When I upgraded to 7.4, I noticed less
of it, but it was still there. Then I discovered two programs that
were sleeping for long times inside of a transaction. Restructuring
those to open transactions as needed fixed it all.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.