Re: [GENERAL] tgrm index for word_similarity - Mailing list pgsql-general

From Arthur Zakirov
Subject Re: [GENERAL] tgrm index for word_similarity
Date
Msg-id 20171021120104.GA1563@arthur.localdomain
Whole thread Raw
In response to [GENERAL] tgrm index for word_similarity  ("Igal @ Lucee.org" <igal@lucee.org>)
Responses Re: [GENERAL] tgrm index for word_similarity
List pgsql-general
On Thu, Oct 19, 2017 at 04:54:19PM -0700, Igal @ Lucee.org wrote:
> 
> My query at the moment is:
> 
>     SELECT name, popularity
>     FROM   temp.items3_v
>           ,(values ('some phrase'::text)) consts(input)
>     WHERE  true
>         and word_similarity(input, name) > 0.01  -- be lenient as some names
> are 75 characters long and we want to match even on a few characters of
> input
>     ORDER BY 2, input <<-> name
> 

PostgreSQL doesn't use index scan with functions within WHERE clause. So
you always need to use operators instead. You can try <% operator and
pg_trgm.word_similarity_threshold variable:

=# SET pg_trgm.word_similarity_threshold TO 0.1;
=# SELECT name, popularityFROM temp.items3_v    ,(values ('some phrase'::text)) consts(input)WHERE input <% nameORDER
BY2, input <<-> name;
 

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Uwe
Date:
Subject: Re: [GENERAL] Restoring tables with circular references dumped to separate files
Next
From: doganmeh
Date:
Subject: [GENERAL] Re: Restoring tables with circular references dumped to separatefiles