CODEWITHSUNDEEP
sqlpostgresqlfull-text-search
Michael Nielsen
Michael Nielsen

Reputation: 1242

PostgreSQL Full Text Search with substrings

I'm trying to create the fastest way to search millions (80+ mio) of records in a PostgreSQL (version 9.4), over multiple columns.

I would like to try and use standard PostgreSQL, and not Solr etc.

I'm currently testing Full Text Search followed https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/.

It works, but I would like some more flexible way to search.

Currently, if I have a column containing ex. "Volvo" and one containing "Blue" I am able to find the record with the search string "volvo blue", but I would like to also find the record using "volvo blu" as if I used LIKE and "%blu%'.

Is that possible with full text search?

Upvotes: 1

Views: 1535

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246083

The only option to something like this is by using the pg_trgm contrib module.

This enables you to create a GIN or GiST index that indexes all sequences of three characters, which can be used for a search with the similarity operator %.

Two notes:

  1. Using the % operator may return “false positive” results, so be sure to add a second condition (e.g. with LIKE) that eliminates those.

  2. A trigram search works well with longer search strings, but performs badly with short search strings because of the many false positive results.

If that is not good enough for your purposes, you'll have to resort to an third-party solution.

Upvotes: 2

Related Questions