I would like to get results by query this:

   SELECT id, subject
   FROM mailboxes
   WHERE tsv @@ plainto_tsquery('avail')
   ) AS t1

This works and return rows with tsv containing 'Available'. But if I use 'avai' (truncating '...lable') it does not find anything.

Do all queries have to be in dictionary? Can't we just query such letters? I have a table that contains e-mail bodies (content) and I would like to make queries fast as it grows rapidly. Currently, I am using:

... WHERE content ~* 'avail'

1 Answer 1


Do all queries have to be in dictionary?

No. Because only word stems (according to the used text search configuration) are in the index to begin with. But more importantly:

No. Because, on top of that Full Text Search is also capable of prefix matching:

This would work:

SELECT id, subject
FROM   mailboxes
WHERE  tsv @@ to_tsquery('simple', 'avail:*')

Note 3 things:

  1. Use to_tsquery(), not plainto_tsquery(), in this case because (quoting the manual):

    ... plainto_tsquery will not recognize tsquery operators, weight labels, or prefix-match labels in its input

  2. Use the 'simple' text search configuration to generate the tsquery since you obviously want to take the word 'avail' as is and not apply stemming.

  3. Append :* to make it a prefix search, i.e find all lexemes starting with 'avail'.

Important: This is a prefix search on lexemes (word stems) in the document. A regular expression match without wildcards (content ~* 'avail') is not exactly the same! The latter is not left-anchored (to the start of lexemes) and would also find 'FOOavail' etc.

It's unclear whether you want the behavior outlined in your query or the equivalent of the added regular expression. Trigram indexes using the additional module (pg_trgm) are the right tool for that. There are many related questions on dba.SE, try a search.



     ('Zend has no framework')
   , ('Zend Framework')
   ) sub(t), to_tsvector(t) AS tsv
WHERE  tsv @@ to_tsquery('zend <-> fram:*');
 id |       t        |          tsv
  2 | Zend Framework | 'framework':2 'zend':1

Related answer (chapter "Different approach to optimize search"):


Since you mentioned emails, be aware that the text search parser identifies emails and does not split them into separate words / lexemes. Consider:

SELECT ts_debug('english', '[email protected]')
(email,"Email address",[email protected],{simple},simple,{[email protected]})

I would replace the separators @ and . in your emails with space (' ') to index contained words.

Also, since you are dealing with names in emails, not with English (or some other language) words, I would use the 'simple' text search configuration to disable stemming and other language features:

Build the ts_vector column with:

SELECT to_tsvector('simple', translate('[email protected]', '@.', '  ')) AS tsv;
  • I'm deleting my answer for this because either way because I'm clearly wrong for the first time ever and I'd rather not be reminded of it. I have two questions for you 1) where is :* documented, and 2) shouldn't a mention to build to_tsvector('simple'..) come hand-in-hand with instructions that future querying of that tsv will require the 'simple' configuration too to tsquery? I think you should clarify the ramifications of disabling stemming on a tsvector/tsquery. Dec 13, 2016 at 3:53
  • @EvanCarroll: Using the 'simple' configuration is not required. It just avoids stemming (like 'rats' to 'rat') which may or may not be desirable. Not desirable for the given example. Manual: I've added links above... Dec 13, 2016 at 4:35
  • 6
    @EvanCarroll: Aside: Thinking you're wrong the first time ever, would be the second time. And that would be wrong, recursively. ;) Dec 13, 2016 at 4:42
  • 2
    @ErwinBrandstetter, Wow, your way just gave me a full speed search. Before your way it tooks 0.380ms to get result. After your way it took 0.079 ms.
    – xangr
    Dec 13, 2016 at 6:17
  • 1
    @xangr: No, FTS only offers prefix matching for lexemes. For anything more, look to pg_trgm. FTS is faster (with a smaller index). You can even combine both indexes ... Dec 14, 2016 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.