Re: reindexing

thanks.. the index I was having is gist on a to_tsvector column . version we
have is 8.3

On Mon, Feb 7, 2011 at 7:23 PM, Alex Hunsaker <badalex(at)gmail(dot)com> wrote:

> On Mon, Feb 7, 2011 at 17:12, akp geek <akpgeek(at)gmail(dot)com> wrote:
> > Hi all -
> > I ran query this morning, I got a wrong results. I have run the
> same
> > query in an other environment with same data and I got the result set I
> was
> > expecting.
> > After that I did a re index and on the table I was getting
> incorrect
> > results, the data then came out fine,
> > Do I have to reindex periodically to make sure the data retrieval
> > would be correct?
>
> In general, no. That would be silly. However, if you are using hash
> indexes, per the fine manual
> (http://www.postgresql.org/docs/9.0/interactive/indexes-types.html)
> "Hash index operations are not presently WAL-logged, so hash indexes
> might need to be rebuilt with REINDEX after a database crash. They are
> also not replicated over streaming or file-based replication. For
> these reasons, hash index use is presently discouraged."
>
> REINDEX will also 'fix' a btree index if it somehow got corrupted.
> Depending on the type of corruption, I would expect postgres to
> complain (or segfault) in most cases instead of returning the wrong
> results. Anything interesting in your server logs?
>
> Also you failed to note what version of postgres you are using-- its
> hard to tell if you are hitting a known bug or not.
>