I'm a little bit confused with the whole concept of PostgreSQL, full text search and Trigram. In my full text search queries, I'm using tsvectors, like so:
SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'cat, bat, rat');
The problem is, this method doesn't account for misspelling. Then I started to read about Trigram and
Looking through other examples, it seems like trigram is used or vectors are used, but never both. So my questions are: Are they ever used together? If so, how? Does trigram replace full text? Are trigrams more accurate? And how are trigrams on performance?
They serve very different purposes.
Consider the following examples:
SELECT 'cat' % 'cats'; --true
The above returns true because
'cat' is quite similar to
'cats' (as dictated by the pg_trgm limit).
SELECT 'there is a cat with a dog' % 'cats'; --false
The above returns
% is looking for similarity between the two entire strings, not looking for the word
cats within the string.
SELECT to_tsvector('there is a cat with a dog') @@ to_tsquery('cats'); --true
true because tsvector transformed the string into a list of stemmed words and ignored a bunch of common words (stop words - like 'is' & 'a')... then searched for the stemmed version of
It sounds like you want to use trigrams to auto-correct your
ts_query but that is not really possible (not in any efficient way anyway). They do not really know a word is misspelt, just how similar it might be to another word. They could be used to search a table of words to try and find similar words, allowing you to implement a "did you mean..." type feature, but this word require maintaining a separate table containing all the words used in your
If you have some commonly misspelt words/phrases that you want the text-index to match you might want to look at Synonym Dictorionaries