8
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ What does the @@ operator do in WHERE P.tsv_text @@ plainto_tsquery('hello')? I looked for that in the documentation but could not find anything useful. \$\endgroup\$ – Phrancis Jul 11 '15 at 20:44
  • \$\begingroup\$ @Phrancis Sorry, maybe I should have mentioned that. That's the syntax to use the plainto_tsquery($1) result (a tsquery) to search the column new_phrases.tsv_text. It's PostgreSQL's text search mechanism. A tsquery is used to search a ts_vector (new_phrases.tsv_text is a ts_vector of the text column). $1 is any arbitrary string that is used for the keyword search. \$\endgroup\$ – Chris Cirefice Jul 11 '15 at 20:48
  • \$\begingroup\$ @Phrancis That is the text-matching operator for full text search. \$\endgroup\$ – 200_success Jul 11 '15 at 22:39
  • \$\begingroup\$ Pagination and limit/offset are pretty terrible for performance in anything except an index-scan or index-only scan of a table ordered by an indexed field. Joins must be performed before LIMIT and OFFSET since they affect how many rows are produced. That doesn't mean the system has to produce the complete output of the join, it might be able to do the join in an order that lets it partially execute just enough to get the desired results, but that depends on details of available indexes etc. \$\endgroup\$ – Craig Ringer Jul 12 '15 at 5:02
  • \$\begingroup\$ @CraigRinger That's exactly my concern ;) I'm no SQL expert - I'm a student who is working on way too many facets of a large project, so I've been doing my best. I don't understand SQL and JOINs enough to really get performant, but these queries are running on millions of rows and they're getting slow. I'm fairly certain I'm not doing the JOINs in the correct spot - they should be done after the initial text search and translations table JOIN is finished, but I'm pretty sure the join is being done before the result set is even limited to 25... \$\endgroup\$ – Chris Cirefice Jul 12 '15 at 7:02
5
\$\begingroup\$

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

\$\endgroup\$
  • 1
    \$\begingroup\$ Vogel, you are a godsend. I didn't know that I could just throw arbitrary SELECT statements in there like that! I knew that I was doing the JOINs wrong. I actually modified my API later to have the AND FT.user_id = $1 in the JOIN clause which actually got me the correct result set with a significant speedup. However, the JOINs were still causing a problem, as you've explained, so eliminating them really was the best option. Now I just have to make sure I have the indexes set up correctly for how I'm using the 'favorite' and 'flag' tables. Thanks a bunch :) \$\endgroup\$ – Chris Cirefice Jul 12 '15 at 16:36

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.