Searching text, starring, flagging and joins
The way you join is the problem.

Okay now that's out the way let me explain. These left joins you're doing are bloating your result set:

LEFT JOIN favorite_translations FT ON T.translation_id = FT.translation_id



  The LEFT JOIN keyword returns all rows from the left table (table1),
  with the matching rows in the right table (table2). The result is NULL
  in the right side when there is no match. - (w3schools)


what this means is that for two favorites on the same post you get two results into your set. This bloats your result set significantly as soon as you get more and more flags and favorites.

There is a rather simple fix for that.

Since in the end you only want to know about wether the user favorited / flagged or not, the "correct" way to go about this is a subselect to the count, something like this:

((SELECT COUNT(*) FROM favorite_translations 
         WHERE user_id = $1 AND translation_id = T.translation_id) &gt; 0) AS favorited


This also eliminates the less than nice-looking Case-Statement you got there, removes two joins and nets us a smaller execution plan, that even prevents multiple results for the same translation pair.

So this is the new execution plan on depesz