I just merged another try to improve performance of this job:
https://github.com/discourse/discourse/pull/22487
I tested in on few instances and it was fine, but they were smaller than yours.
If it is still failing with this commit, could you provide me EXPLAIN ANALYSE report? Script to generate it:
topic = Topic.last
user_option_sql_fragment =
if SiteSetting.watched_precedence_over_muted
<<~SQL
INTERSECT
SELECT user_id FROM user_options WHERE user_options.watched_precedence_over_muted IS false
SQL
else
<<~SQL
EXCEPT
SELECT user_id FROM user_options WHERE user_options.watched_precedence_over_muted IS true
SQL
end
user_ids_sql = <<~SQL
(
SELECT user_id FROM category_users WHERE category_id = #{topic.category_id.to_i} AND notification_level = #{CategoryUser.notification_levels[:muted]}
UNION
SELECT user_id FROM tag_users tu JOIN topic_tags tt ON tt.tag_id = tu.tag_id AND tt.topic_id = #{topic.id} WHERE tu.notification_level = #{TagUser.notification_levels[:muted]}
EXCEPT
SELECT user_id FROM topic_users tus WHERE tus.topic_id = #{topic.id} AND tus.notification_level = #{TopicUser.notification_levels[:watching]}
)
#{user_option_sql_fragment}
SQL
sql = User.where("id IN (#{user_ids_sql})").to_sql
sql_with_index = <<SQL
EXPLAIN ANALYZE #{sql};
SQL
result = ActiveRecord::Base.connection.execute("#{sql_with_index}")
puts sql_with_index
result.each do |r|
puts r.values
end
It would help me to find missing index or which part of this query is so slow.