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