2

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.

Thanks,

Arthur

Improve this question

2 Answers 2

Reset to default
2

a word or two belonging to a pre-set, almost 'constant', list of words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?

Improve this answer
3
  • Thanks for your answer. However, how would I "scan" the input texts? Wouldn't the best be to use the pgsql full-text-search feature (tsearch)? And it would feel more natural to use the index solution that's coming with it. Otherwise, I guess I'd have to create a TRIGGER that would parse all incoming texts and compare them to my list of words, which I'd probably do less well than tsearch. Besides, would there be a performance impact (many-to-many vs. gin index)? And finally, I'd still like to know what "full index scan" mean, as I'm considering using this for something else. Thks again
    – Arthur
    Sep 12, 2011 at 15:07
  • you can still use full-text-search for the scan or perhaps regular expressions would suffice? Yes a trigger, or (far) preferably route the inserts through a stored procedure that also inserts the links. many-to-many would be very fast for queries but your choice will I think depend on your exact requirements (I don't know much about fts or GIN - hopefully someone else will chip in)
    – Jack Douglas
    Sep 12, 2011 at 15:16
  • Well I've got 500+ different words in my list, so a regex, or several regex, would be clumsy, I guess. I think I'm going to stick with the full-text-search capabilities of pgsql, it seems quite powerful and we're probably going to use it for other stuffs anyway. Thanks for your answers. Btw, I think I have the answer to my question, I'll write it later.
    – Arthur
    Sep 13, 2011 at 10:37
2

I think I understood it so here it is: Documentation says on GIN indexes:

It is an index structure storing a set of (key, posting list) pairs, where a "posting list" is a set of rows in which the key occurs. Each indexed value can contain many keys, so the same row ID can appear in multiple posting lists.

While an indexed value can contain many keys, it is possible that you may "add" a value that has no key (in my case: adding a text which does not contain any of my words, so the (tsvector) value I'm adding is just empty). In that case the GIN index won't create a key corresponding to "empty". So my line is just not added in the index.

As the documentation states a bit further:

In such a case the indexed value will be unrepresented in the index. It is therefore impossible for GIN to guarantee that a scan of the index can find every row in the table.

It was actually pretty clear. If you scan the whole index (I don't know how you would do that), it is not guaranteed that you'll find every row that's in the table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.