Re: reindexing

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.