Why PostgreSQL full-text search misses "paid" when querying "paying invoices"
A developer building a document Q&A example found that PostgreSQL full-text search failed to match the sentence "Invoices must be paid within thirty days" against the query "paying invoices". The cause is that the English Snowball stemmer only strips suffixes, so "paying" reduces to "pay" while "paid" remains a separate term. Additionally, plainto_tsquery joins terms with AND, so a single unmatched word causes the whole chunk to be rejected. Adding trigram similarity via pg_trgm was examined as a workaround but proved unreliable, since its scores are diluted by longer text and it does not understand verb tenses. The author instead proposes an explicit query using to_tsquery with OR-grouped alternatives, such as 'invoices & (paying | paid)', which correctly retrieves the matching chunk.
This is an AI-generated summary. ShortSingh links to the original source for the complete article.

Discussion (0)
Log in to join the discussion and vote.
Log in