Searching text, starring, flagging and joins

Question

I have an interesting performance concern that I would like to address before it becomes a serious issue. I created a SQL Fiddle to demonstrate the query, and the explain statement can be seen on Depesz.

I have a collection phrases in different languages, and 'translations' that link phrase ids together (source and destination). The phrases can be searched by keyword in a particular source language, and find translations of that phrase in the destination language. The translations can also be starred and flagged, and the user id and translation id are held in respective tables for this functionality.

If a keyword and source language matches, those phrase ids are joined on the translations table; then, that set is joined on new_phrases again where the destination language matches. That set is also joined on favorite_translations and translation_flags, and if the user id matches in those sets, favorited and flagged are true, otherwise false.

SELECT P1.phrase_id as src_phrase_id,
        P1.author as src_author,
        P1.language as src_language,
        P1.text as src_text,
        P2.phrase_id as dst_phrase_id,
        P2.author as dst_author,
        P2.language as dst_language,
        P2.text as dst_text,
        P1.translation_id,
        P1.community_rating,
        CASE WHEN P1.favoriter_id = $1 THEN true
            ELSE false
            END
        as favorited,
        CASE WHEN P1.flagger_id = $1 THEN true
            ELSE false
            END
        as flagged
    FROM (
        SELECT P.phrase_id, P.author, P.language, P.text,
               T.translation_id, T.destination_id, T.community_rating, 
               FT.user_id as favoriter_id, TF.user_id as flagger_id
        FROM new_phrases P
        INNER JOIN translations T ON P.phrase_id = T.source_id
        LEFT JOIN favorite_translations FT ON T.translation_id = FT.translation_id
        LEFT JOIN translation_flags TF ON T.translation_id = TF.translation_id
        WHERE P.tsv_text @@ plainto_tsquery($2)
        AND P.language = $3
        AND T.destination_language = $4
        ORDER BY T.community_rating DESC
        OFFSET $5
        LIMIT 25
        ) P1, new_phrases P2
    WHERE P2.phrase_id = P1.destination_id;

My concern is that the growth of these tables is going to cause a crazy cost increase. I limited the result set of the search to 25, and it's paginated. However, I'm fairly certain the favorite_translations and translation_flags joins are being performed on the set before it is limited to 25, and I'm not sure how to work around that if that's actually the case. Perhaps I'm doing the LEFT JOINs in the wrong spot?

What can I do to improve the performance of this query? I have gone through 5 iterations of this query, improving it each time, but I feel that more can be done. I'm just not sure how.

One thing addressed in the comments is the LIMIT clause. The text search results should be limited to 25 before any other JOINs are performed. However, I'm not incredibly experienced with SQL, and I'm pretty sure that the 3 joins are done before the 25 result LIMIT is applied, causing a massive slow-down. This could be the primary source of improvement (but I don't know how, thus the question)!

Answer 1

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) > 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