Postgres similarity query score is pretty low for search text almost matching

Question

Query :

select similarity( 'GTudH','tud'), similarity ('GTudH', 'gtu'), similarity ('GTudH', 'gdh')

Above is the query result from Postgres, can someone explain why the 1st similarity score is only 0.1 but 3rd one is 0.25

Tested the same with Snowflake, which seems like alright.

Answer 1

You are comparing apples to oranges. The trigram similarity, as the documentation states, is measured by

the number of trigrams they share.

You can easily see that, by that measure, the two strings in the first example have very little in common: they only share one trigram:

select show_trgm( 'GTudH'),show_trgm('tud')

show_trgm	show_trgm
{" g"," gt","dh ",gtu,tud,udh}	{" t"," tu",tud,"ud "}

Your Snowflake example, however, shows the Jaro-Winkler similarity, which uses an entirely different algorithm, so it stands to reason that the result will also be quite different.