0

Could someone explain how Postgres's similarity algorithm works?

Why do these examples return different results?

select similarity('Foo Bar Baz', 'Foo Bar'); -- 0.8
select similarity('Foo Bar Baz', 'Bar Foo'); -- 0.8
select similarity('Foo Bar Baz', 'Foo Baz'); -- 0.8
select similarity('Foo Bar Baz', 'Baz Foo'); -- 0.8
select similarity('Foo Bar Baz', 'Bar Baz'); -- 0.6
select similarity('Foo Bar Baz', 'Baz Bar'); -- 0.6
1

1 Answer 1

1

Bar and Baz differ by only one letter, whereas Foo differs from both Bar and Baz by all 3 letters.

So it makes sense that when checking the similarity of random 2-word permutations, swapping Bar and Baz shouldn't make a big difference, whereas swapping Foo with one or Bar or Baz should make a big difference since Foo doesn't share any common letter with these words.

This is why in your results, the 4 permutations that include Foo have a stronger similarity with Foo Bar Baz than the 2 others that don't include Foo.

It should be noted that pg_trm does not care about word boundaries. As they are lined up, the examples provided in the question could make you believe that a similarity of 0.8 means that one 3-letter word out of 3 is missing, but that's not how it works with trigrams.

This query might help to visualize how strings 1-4 are closer to Foo Bar Baz than strings 5-6:

with l(term) as (values
('Foo Bar Baz')    -- reference
('Foo Bar'),       -- your examples
('Bar Foo'),
('Foo Baz'),
('Baz Foo'),
('Bar Baz'),
('Baz Bar')
)
select term,show_trgm(term) from l;

Result:

    term     |                        show_trgm                        
-------------+---------------------------------------------------------
 Foo Bar Baz | {"  b","  f"," ba"," fo","ar ","az ",bar,baz,foo,"oo "}
 Foo Bar     | {"  b","  f"," ba"," fo","ar ",bar,foo,"oo "}
 Bar Foo     | {"  b","  f"," ba"," fo","ar ",bar,foo,"oo "}
 Foo Baz     | {"  b","  f"," ba"," fo","az ",baz,foo,"oo "}
 Baz Foo     | {"  b","  f"," ba"," fo","az ",baz,foo,"oo "}
 Bar Baz     | {"  b"," ba","ar ","az ",bar,baz}
 Baz Bar     | {"  b"," ba","ar ","az ",bar,baz}

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.