I'm a RDBMS newbie and I have a PostgreSQL database of more than 50 millions lines, growing everyday. There is a column 'text', which contains human-generated text of around 20 words. I need to be able to retrieve the texts containing a word or two belonging to a pre-set, almost 'constant', list of words.
I believe that the best to do so is to use full text search, based on my own dictionary and configuration, with a GIN or GiST index. That was for some context.
The pgsql doc states:
GIN doesn't support full index scans.
I don't understand what that means. Does it mean that if I query the word 'hello' it is possible that a line which text contains the word 'hello' could be not returned?
That would not be great, I'd rather be positive that I get exactly and fully what I want.