Функция не соответствует описанию
Связанная тема в списке рассылки pgsql-bugs.
Алгоритм подобия подстрок описан автор сравнивает массивы триграмм строки запроса и текста. Проблема в том, что массив триграмм оптимизируется (удаляются повторяющиеся триграммы) и теряет информацию об отдельных словах текста.
Запрос иллюстрирует проблему:
with data(t) as (
values
('message'),
('message s'),
('message sag'),
('message sag sag'),
('message sag sage')
)
select
t as "text",
show_trgm(t) as "text trigrams",
show_trgm('sage') as "string trigrams",
cardinality(array_intersect(show_trgm(t), show_trgm('sage'))) as "common trgms"
from data;
text | text trigrams | string trigrams | common trgms
------------------+-----------------------------------------------------------+-----------------------------+--------------
message | {" m"," me",age,ess,"ge ",mes,sag,ssa} | {" s"," sa",age,"ge ",sag} | 3
message s | {" m"," s"," me"," s ",age,ess,"ge ",mes,sag,ssa} | {" s"," sa",age,"ge ",sag} | 4
message sag | {" m"," s"," me"," sa","ag ",age,ess,"ge ",mes,sag,ssa} | {" s"," sa",age,"ge ",sag} | 5
message sag sag | {" m"," s"," me"," sa","ag ",age,ess,"ge ",mes,sag,ssa} | {" s"," sa",age,"ge ",sag} | 5
message sag sage | {" m"," s"," me"," sa","ag ",age,ess,"ge ",mes,sag,ssa} | {" s"," sa",age,"ge ",sag} | 5
(5 rows)
Массивы триграмм в последних трех строках одинаковы и содержат все триграммы строки запроса.
Очевидно, что реализация не соответствует описанию функции (описание было изменено в более поздних выпусках документации):
Возвращает число, указывающее, насколько первая строка похожа на наиболее похожее слово второй строки. Функция ищет во второй строке наиболее похожее слово, а не наиболее похожую подстроку.
Моя функция, используемая в приведенном выше запросе:
create or replace function public.array_intersect(anyarray, anyarray)
returns anyarray language sql immutable
as $$
select case
when $1 is null then $2
else
array(
select unnest($1)
intersect
select unnest($2)
)
end;
$$;
Обходной путь
Вы можете легко написать свою собственную функцию, чтобы получить более ожидаемые результаты:
create or replace function my_word_similarity(text, text)
returns real language sql immutable as $$
select max(similarity($1, word))
from regexp_split_to_table($2, '[^[:alnum:]]') word
$$;
Сравнивать:
with data(t) as (
values
('message'),
('message s'),
('message sag'),
('message sag sag'),
('message sag sage')
)
select t, word_similarity('sage', t), my_word_similarity('sage', t)
from data;
t | word_similarity | my_word_similarity
------------------+-----------------+--------------------
message | 0.6 | 0.3
message s | 0.8 | 0.3
message sag | 1 | 0.5
message sag sag | 1 | 0.5
message sag sage | 1 | 1
(5 rows)
Новая функция в Postgres 11+
В Postgres 11+ появилась новая функция strict_word_similarity()
что дает результаты, ожидаемые автором вопроса:
with data(t) as (
values
('message'),
('message s'),
('message sag'),
('message sag sag'),
('message sag sage')
)
select t, word_similarity('sage', t), strict_word_similarity('sage', t)
from data;
t | word_similarity | strict_word_similarity
------------------+-----------------+------------------------
message | 0.6 | 0.3
message s | 0.8 | 0.36363637
message sag | 1 | 0.5
message sag sag | 1 | 0.5
message sag sage | 1 | 1
(5 rows)
person
klin
schedule
27.10.2017
sage
это подстрокаmessage test sag
, если не ошибаюсь. - person Tim Biegeleisen   schedule 27.10.2017word_similarity('sage', 'message sag sag')
? - person YowE3K   schedule 27.10.2017