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
translation_flags, and if the user id matches in those sets,
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
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)!