From d9ae3db8d5543cf0b7fa44186c191c9bb2472d23 Mon Sep 17 00:00:00 2001 From: Claire Date: Wed, 5 May 2021 22:04:52 +0200 Subject: Improve performance of follow recommendation scheduler (#16159) Express follow_recommendations in terms of account_summaries rather than accounts, integrate filters that are unconditionally used, and materialize the resulting view. This should result in the bulk of the computation being performed only once instead of **once per recommendation language**. --- db/views/follow_recommendations_v02.sql | 34 +++++++++++++++++++++++++++++++++ 1 file changed, 34 insertions(+) create mode 100644 db/views/follow_recommendations_v02.sql (limited to 'db/views') diff --git a/db/views/follow_recommendations_v02.sql b/db/views/follow_recommendations_v02.sql new file mode 100644 index 000000000..673c5cc85 --- /dev/null +++ b/db/views/follow_recommendations_v02.sql @@ -0,0 +1,34 @@ +SELECT + account_id, + sum(rank) AS rank, + array_agg(reason) AS reason +FROM ( + SELECT + account_summaries.account_id AS account_id, + count(follows.id) / (1.0 + count(follows.id)) AS rank, + 'most_followed' AS reason + FROM follows + INNER JOIN account_summaries ON account_summaries.account_id = follows.target_account_id + INNER JOIN users ON users.account_id = follows.account_id + LEFT OUTER JOIN follow_recommendation_suppressions ON follow_recommendation_suppressions.account_id = follows.target_account_id + WHERE users.current_sign_in_at >= (now() - interval '30 days') + AND account_summaries.sensitive = 'f' + AND follow_recommendation_suppressions.id IS NULL + GROUP BY account_summaries.account_id + HAVING count(follows.id) >= 5 + UNION ALL + SELECT account_summaries.account_id AS account_id, + sum(reblogs_count + favourites_count) / (1.0 + sum(reblogs_count + favourites_count)) AS rank, + 'most_interactions' AS reason + FROM status_stats + INNER JOIN statuses ON statuses.id = status_stats.status_id + INNER JOIN account_summaries ON account_summaries.account_id = statuses.account_id + LEFT OUTER JOIN follow_recommendation_suppressions ON follow_recommendation_suppressions.account_id = statuses.account_id + WHERE statuses.id >= ((date_part('epoch', now() - interval '30 days') * 1000)::bigint << 16) + AND account_summaries.sensitive = 'f' + AND follow_recommendation_suppressions.id IS NULL + GROUP BY account_summaries.account_id + HAVING sum(reblogs_count + favourites_count) >= 5 +) t0 +GROUP BY account_id +ORDER BY rank DESC -- cgit