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@gmail.com> wrote:
On Mon, Feb 7, 2011 at 17:12, akp geek <akpgeek@gmail.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.