Thread: FTS and tri-grams

FTS and tri-grams

From
Mark Phillips
Date:
Some years ago we implemented FTS. At the time, we also added a column with the tsvector data type. We assumed we
neededthat 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


Re: FTS and tri-grams

From
Christophe Pettus
Date:

> 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
machinerycan do that trigrams can't, such as proximity searches between words, prefix and stemmed searches, and things
ofthat type.  If you just want fuzzy searching on a small number of words, trigrams are probably fine; for more
sophisticatedkinds of searching, you want tsvector. 

They're completely different sets of functionality in PostgreSQL.

--
-- Christophe Pettus
   xof@thebuild.com




Re: FTS and tri-grams

From
Artur Zakirov
Date:
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
bedropped? 
>
> 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