Full text search randomly not working for short prefixes?

From: cen <imbacen(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Full text search randomly not working for short prefixes?
Date: 2016-12-02 15:21:58
Message-ID: 7f2f3977-1166-1cd7-63ec-c639c6f13d45@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Something funny going on with my full text search.. and I have no idea what.
I have a receiver called "Ana", this is her tsv column:

'38651000000':4 'aceventura(at)mailinator(dot)com':3B 'ana':1A 'novak':2A

This queries do not find her:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('a:*'));
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('an:*'));

This does:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('ana:*'));

Now to an even more interesting part: I have 3 people with last name
"Novak" and one with name "Nov"

This query finds all 4:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('n:*'));

This finds NONE:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('no:*'));

This finds all 4 again:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('nov:*'));

..and this finds all with the last name only:
SELECT * FROM receivers r WHERE r.tsv @@ to_tsquery(unaccent('nova:*'));

These are the TSV columns of last name:
"'38651000000':4 'janez':1A 'janeznovak(at)mailinator(dot)com':3B 'novak':2A"
"'38651000000':4 'aceventura(at)mailinator(dot)com':3B 'ana':1A 'novak':2A"
"'38651000000':4 'novak':2A 'tine':1A 'tnovak(at)mailinator(dot)com':3B"
"'21415000000':4 'alen(dot)nova(at)gmailer(dot)com':3B 'allan':1A 'novak':2A"

And the first name:
"'38651604724':6 'brez':3A 'list':4A 'nov':1A
'novreceiver101(at)mailinator(dot)com':5B 'receiv':2A"

What is going on here?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message rakeshkumar464 2016-12-02 15:26:20 Determining replication lag
Previous Message Tom Lane 2016-12-02 15:20:31 Re: ARRAY_LENGTH() function behavior with empty array