perf: Translation Memory and Tolgee AI Translator suggestion performance (tolgee#2459)

JanCizmar · web-flow · commit fee2751c6c8e · 2024-09-11T13:59:18.000+02:00
diff --git a/backend/data/src/main/kotlin/io/tolgee/service/translation/TranslationMemoryService.kt b/backend/data/src/main/kotlin/io/tolgee/service/translation/TranslationMemoryService.kt
@@ -83,9 +83,15 @@ class TranslationMemoryService(
                   target.text <> '' and
                   target.text is not null
             join key targetKey on target.key_id = targetKey.id    
-            where baseTranslation.language_id = p.base_language_id and
-              (cast(:key as bigint) is null or targetKey.id <> :key) and targetKey.is_plural = :isPlural
-              and baseTranslation.text % :baseTranslationText
+            """ +
+
+          // we use the case when syntax to force postgres to evaluate all the other conditions first,
+          // the similarity condition is slow even it uses index, and it tends to be evaluated first since
+          // huge underestimation
+          """
+            where case when (baseTranslation.language_id = p.base_language_id and
+              (cast(:key as bigint) is null or targetKey.id <> :key) and targetKey.is_plural = :isPlural)
+              then baseTranslation.text % :baseTranslationText end
         ) select base.*, count(*) over() 
         from base
         order by base.similarity desc
