FTS and tri-grams
Some years ago we implemented FTS. At the time, we also added a column with the tsvector data type. We assumed we needed that for FTS to work.
Later, we added tri-gram support. At the time, we assumed tri-gram complimented FTS.
More recently, we have run into a problem with replicating the tsvector column.
We now wonder if tri-gram alone can achieve a fast full text search result. If so, then we could the tsvector column be dropped?
1. Is FTS required for tri-gram to work?
2. Are these independent of each other?
3. Is tri-gram alone sufficient for a “full text search” feature?
Any thoughts, experiences or other thoughts greatly appreciated.
- Mark Phillips
On Jan 5, 2021, at 13:26, Mark Phillips <mark.phillips@mophilly.com> wrote:
1. Is FTS required for tri-gram to work?
2. Are these independent of each other?
3. Is tri-gram alone sufficient for a “full text search” feature?
The answers are, kind of in order:
2. Yes.
1. No.
3. It depends on what you mean by "full text search."
Trigrams are mostly for fuzzy matching on a single or small number of words. There are things that the tsvector machinery can do that trigrams can't, such as proximity searches between words, prefix and stemmed searches, and things of that type. If you just want fuzzy searching on a small number of words, trigrams are probably fine; for more sophisticated kinds of searching, you want tsvector.
They're completely different sets of functionality in PostgreSQL.
--
-- Christophe Pettus
xof@thebuild.com
On Tue, Jan 5, 2021 at 10:26 PM Mark Phillips
<mark.phillips@mophilly.com> wrote:
We now wonder if tri-gram alone can achieve a fast full text search result. If so, then we could the tsvector column be dropped?
1. Is FTS required for tri-gram to work?
2. Are these independent of each other?
FTS and tri-gram are independent features. You can implement them
independently, but it might depend on your use case.
3. Is tri-gram alone sufficient for a “full text search” feature?
It isn't sufficient if you want exact and/or phrase search.
--
Artur