Re: pg_trgm word_similarity inconsistencies or bug

On Tue, Oct 31, 2017 at 4:02 PM, Jan Przemysław Wójcik <
jan(dot)przemyslaw(dot)wojcik(at)gmail(dot)com> wrote:

> 2017-10-30 19:08 GMT+01:00 Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>:
> >
> > On Sat, Oct 28, 2017 at 11:22 AM, Arthur Zakirov <
> a(dot)zakirov(at)postgrespro(dot)ru> wrote:
> >>
> >> On Fri, Oct 27, 2017 at 06:48:08PM +0000, Cristiano Coelho wrote:
> >> > Hello all, this is related to postgres 9.6 (9.6.4) and a good
> description can be found here https://stackoverflow.com/
> questions/46966360/postgres-word-similarity-not-comparing-words
> >> >
> >> > But in summary, word_similarity doesn’t seem to do exactly what the
> docs say, since it will match trigrams from multiple words rather tan doing
> a word by word comparison.
> >> >
> >> > Below is a table with output and expected output, thanks to kiln from
> stackoverflow to provide it.
> >> >
> >>
> >> Interesting. An klin's answer from stackoverflow.com is right.
> >>
> >> The initial example can be reduced to the next:
> >>
> >> =# select word_similarity('sage', 'age sag');
> >> word_similarity
> >> -----------------
> >> 1
> >>
> >> It computes maximum similarity using closest trigrams not considering
> order of
> >> 'sage' trigrams. It determines that all
> >> trigrams from 'sage' match trigrams from 'age sag'.
> >>
> >> Initial order of 'age sag' trigrams:
> >> ' a', ' ag', 'age', 'ge ', ' s', ' sa', 'sag', 'ag '
> >> ^ ^
> >> |from |to
> >> Sorted 'sage' trigrams (all of them occured within 'age sag' trigrams
> >> continuously):
> >> ' s', ' sa', 'age', 'ge ', 'sag'
> >>
> >> Maybe the problem should be solved by considering 'sage' trigrams
> >> initial order.
> >
> >
> > We searching for continuous extent of second string trigrams (in
> original orders) which has best similarity with first string trigrams.
> > Possible solution could be forcing this extent boundaries to be at word
> boundaries. However, it would become less convenient to search for *part*
> of word. And we already have users adopt this feature.
> > So, I see following solution:
> > 1) Define GUC variable which specifies whether word_similarity() should
> force extent boundaries to be at word boundaries,
> > 2) Document both cases of word_similarity() behavior.
>
> Look at the example:
>
> with data(word, string) as (
> values
> ('sage', 'message'),
> ('sage', 'message s'),
> ('sage', 'message sa')
> )
>
> select
> similarity(word, string),
> word_similarity (word, string)
> from data;
>
> similarity | word_similarity
> ------------+-----------------
> 0.3 | 0.6
> 0.363636 | 0.8
> 0.454545 | 1
> (3 rows)
>
> When searching for a part of a word I would expect that the word
> similarity is the same in all three rows. It's really strange that the
> context of the second word (sa) makes the similarity equal to 1.
>
> From a user's point of view it's also hard to understand why there is
> such a big difference between similarity() and word_similarity(),
> especially when comparing just two words (the first row).
>

Probably word_similarity() is not a good name for this function. Initially
it was called substring_similarity() which now seems like better name for
that.

I do not think the current function has any practical use.
>

It's hard for me to agree or disagree with you. There is no technical
problem to force word_similarity() to search for extent boundaries within
word boundaries. However, we already have customers using this function
(and they are likely satisfied with its currency behavior). It's important
for me that our fix wouldn't affect them. I asked them to join this
discussion. I hope that together we'll find a consensus.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company