Two edge cases in PostgreSQL full-text search
Tags: postgres
Published: 2020-01-09 17:30:38

We widely use PostgreSQL full-text search in our projects. It is fast, reliable, and doesn't add any additional technical complexity. But sometimes it may not work as you expect it too.

Usually we add a `ts_vector` column right in our data tables. Let's see how it has it is done.

<table class="table table-striped table-bordered">
<thead>
<tr>
<th>title</th>
<th>tsv</th>
</tr>
</thead>
<tbody>
<tr>
<td>Handyman</td>
<td>‘handyman’:1</td>
</tr>
<tr>
<td>Heating &amp; Cooling</td>
<td>‘cool’:2 ‘heat’:1</td>
</tr>
<tr>
<td>Painting</td>
<td>‘paint’:1</td>
</tr>
<tr>
<td>Roofing</td>
<td>‘roof’:1</td>
</tr>
</tbody>
</table>

Column `tsv` is generated with `to_tsvector` function from a `title` column with a trigger function.

``` sql
CREATE FUNCTION skills_tsv_update_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
    to_tsvector('pg_catalog.english', COALESCE(new.title, ''))
  return new;
end
$$ LANGUAGE plpgsql;
```

Then we can query this table using `to_tsquery` fuction.

``` sql
SELECT * FROM skills
WHERE
    tsv @@ to_tsquery('pg_catalog.english', 'roof')

-- 1 record found
```

A record with `Roofing` has been found, and this makes sense. But what should be found for `handy` query? You can imagine that it will be a `Handyman` skill and you are wrong.  Before explaining why this happens, let's try another query.

``` sql
SELECT * FROM skills
WHERE
    tsv @@ to_tsquery('pg_catalog.english', 'roofing')

-- no records found
```

No records were found, even if we have a record with `Roofing` title in our table.

The `to_tsvector` function internally calls a parser which breaks the document text into tokens and assigns a type to each token. For each token, a list of dictionaries is consulted, where the list can vary depending on the token type. The first dictionary that recognizes the token emits one or more normalized lexemes to represent the token. For example, `roofing` became `roof`. Some words are recognized as stop words, like `&` in our example. It is possible to have many different configurations in the same database, and predefined configurations are available for various languages. In our example we used the default configuration english for the English language.

`to_tsquery` function parses user input and converts it to `tsquery` data type. It uses the same dictionaries as `to_tsvector` function. Let's try to understand what happens in two queries that were listed above.

The result of `to_tsquery('pg_catalog.english', 'handy')` will be `handi` because of pluralization rules for words ending on `y`. The query listed below doesn't return any records because `'handyman':1` vector does not overlap with `handi` query.

``` sql
SELECT * FROM skills
WHERE
    tsv @@ to_tsquery('pg_catalog.english', 'handy')

-- no records found
```

How we can cover this situation? As you remember you can change a configuration that is used to parse user input. Let's try a `simple` configuration.

``` sql
select to_tsquery('pg_catalog.simple', 'handy')

-- 'handy'
```

Looks like this is what we need, let's add this to our query.

``` sql
SELECT * FROM skills
WHERE
    tsv @@ to_tsquery('pg_catalog.english', 'handy')
    OR
    tsv @@ to_tsquery('pg_catalog.simple', 'handy')    
    
-- 1 record found
```

A problem with `Roofing` and `roof` can be solved in the same way. Let's change the function that is used to populate `tsv` column.

``` sql
CREATE FUNCTION skills_tsv_update_trigger() RETURNS trigger AS $$
begin
  new.tsv :=
    to_tsvector('pg_catalog.english', COALESCE(new.title, '')) ||
    to_tsvector('pg_catalog.simple', COALESCE(new.title, ''))
  return new;
end
$$ LANGUAGE plpgsql;
```

<table class="table table-striped table-bordered">
<thead>
<tr>
<th>title</th>
<th>tsv</th>
</tr>
</thead>
<tbody>
<tr>
<td>Handyman</td>
<td>‘handyman’:1,2</td>
</tr>
<tr>
<td>Heating &amp; Cooling</td>
<td>‘cool’:2 ‘cooling’:4 ‘heat’:1 ‘heating’:3</td>
</tr>
<tr>
<td>Painting</td>
<td>‘paint’:1 ‘painting’:2</td>
</tr>
<tr>
<td>Roofing</td>
<td>‘roof’:1 ‘roofing’:2</td>
</tr>
</tbody>
</table>

Now vector has more data and query below works as expected.

``` sql
SELECT * FROM skills
WHERE
    tsv @@ to_tsquery('pg_catalog.english', 'roofing')

-- 1 record found
```