CODEWITHSUNDEEP
postgresqlfull-text-searchdatabase-indexes
Arli Chokoev
Arli Chokoev

Reputation: 549

PostgreSQL full-text search drops rows from selection

Full-text search does not show any values by certain lexemes.

Though this code returns true:

SELECT to_tsvector('ispell_russian', description)
       @@ to_tsquery('ispell_russian', 'сам')
FROM callcenter.points_cardsservices
INNER JOIN callcenter.units ON recipientname = name
WHERE units.deptid = 5 
  AND id=366020;

result of the following selection is empty:

SELECT *
FROM callcenter.points_cardsservices
INNER JOIN callcenter.units ON recipientname = name
WHERE units.deptid = 5
  AND id=366020
  AND to_tsvector('ispell_russian', description)
      @@ to_tsquery('ispell_russian', 'сам');

The word I'm searching for used to be in a stopwords list. Can it cause this kind of problem? Also, the to_tsvector('ispell_russian', description) has an index on it.

Upvotes: 0

Views: 38

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246403

You will never find a stopword, because that is filtered out before the search even begins.

But you say it used to be a stopword. If you changed the list of stopwords, you have changed the behavior of the IMMUTABLE (!) function to_tsvector('ispell_russian', $1) which you have used in an index.

If you change the behavior of an indexed immutable function, the index becomes corrupted. You will have to use REINDEX INDEX to rebuild the index.

Upvotes: 1

Related Questions