Thread: REINDEX slow?
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
Edmund Dengler <edmundd@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
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 <edmundd@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 >
Edmund Dengler <edmundd@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
Edmund Dengler <edmundd@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
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes: TL> Edmund Dengler <edmundd@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: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/