Обсуждение: 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/