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 JOIN
s 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 JOIN
s 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)!
@@
operator do inWHERE P.tsv_text @@ plainto_tsquery('hello')
? I looked for that in the documentation but could not find anything useful. \$\endgroup\$plainto_tsquery($1)
result (atsquery
) to search the columnnew_phrases.tsv_text
. It's PostgreSQL's text search mechanism. Atsquery
is used to search ats_vector
(new_phrases.tsv_text
is ats_vector
of thetext
column).$1
is any arbitrary string that is used for the keyword search. \$\endgroup\$LIMIT
andOFFSET
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\$JOIN
s 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 theJOIN
s in the correct spot - they should be done after the initial text search andtranslations
tableJOIN
is finished, but I'm pretty sure the join is being done before the result set is even limited to 25... \$\endgroup\$