0

I am using a gin_trgm_ops index on a varchar(255) column. I am having trouble sorting the results.

For example if I look up similarity(name, 'samsung ssd') as similarity I get varying results.

                name                   | similarity
---------------------------------------+------------
 Samsung Kiintolevy 1 Tb Evo Ssd       |   0.354839
 Samsung Kiintolevy 500 Gb 860 Evo Ssd |   0.297297
 Samsung Kiintolevy 250 Gb 860 Evo Ssd |   0.297297

Furthermore some texts which does not even have the word ssd in it seem to have higher similarity.

                name                 | similarity
-------------------------------------+------------
 Wave Samsung S9                     |   0.444444
 Samsung Galaxy A3                   |   0.380952
 Samsung Ue48j5605                   |   0.380952
 Samsung S7 Edge 3D                  |   0.380952
 Samsung Galaxy A3                   |   0.380952
 Samsung 1Tb 850 Evo Ssd 2.5" Sata   |   0.366667
 Samsung Kiintolevy 1 Tb Evo Ssd     |   0.354839
 Samsung 500Gb 850 Evo Ssd 2.5" Sata |   0.354839
 Samsung 250Gb 850 Evo Ssd 2.5" Sata |   0.354839
 Samsung Rb29hsr2dww                 |   0.347826

Obviously I would prefer all rows which include words samsung and ssd to be on first rows. Is there a simple way to sort the results to have the desired result?

Thanks!

  • If you want things that contain both words, you should be doing a containment search, not a similarity search. If you just want the sorting to be "more intuitive", you will have to explain the basis behind your intuition more clearly, a handful of examples just isn't enough to go on. – jjanes Mar 13 '19 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.