On multiple of our largest forums it’s always the same query that is causing a high database load: badge 17 (Reader), probably due to the size of topic_timings.
The explain looks quite horrendous. I don’t see any obvious performance improvements except for turning off the badge but I thought I’d post here and see if anyone has any ideas.
Nested Loop Semi Join (cost=1045564.36..1122735.60 rows=1 width=12)
Join Filter: (users.id = "ANY_subquery".user_id)
-> Seq Scan on users (cost=0.00..3149.19 rows=29219 width=4)
-> Materialize (cost=1045564.36..1119148.13 rows=1 width=4)
-> Subquery Scan on "ANY_subquery" (cost=1045564.36..1119148.12 rows=1 width=4)
-> GroupAggregate (cost=1045564.36..1119148.11 rows=1 width=12)
Group Key: pt.user_id, pt.topic_id, t.posts_count
Filter: (count(*) >= t.posts_count)
-> Gather Merge (cost=1045564.36..1119148.09 rows=1 width=12)
Workers Planned: 2
-> Incremental Sort (cost=1044564.34..1118147.95 rows=2 width=12)
Sort Key: pt.user_id, pt.topic_id, t.posts_count
Presorted Key: pt.user_id
-> Merge Left Join (cost=970980.80..1118147.86 rows=1 width=12)
Merge Cond: (pt.user_id = ub.user_id)
Filter: (ub.id IS NULL)
-> Sort (cost=956553.39..967890.72 rows=4534932 width=12)
Sort Key: pt.user_id
-> Nested Loop (cost=67.77..377652.59 rows=4534932 width=12)
Join Filter: (t_1.id = pt.topic_id)
-> Nested Loop (cost=67.19..335182.35 rows=4883 width=20)
-> Hash Join (cost=66.76..58411.29 rows=317 width=12)
Hash Cond: (t_1.category_id = c.id)
-> Nested Loop (cost=0.42..58339.78 rows=1847 width=16)
-> Parallel Seq Scan on topics t (cost=0.00..42353.02 rows=2030 width=8)
Filter: (posts_count > 100)
-> Index Scan using forum_threads_pkey on topics t_1 (cost=0.42..7.88 rows=1 width=8)
Index Cond: (id = t.id)
Filter: ((deleted_at IS NULL) AND visible)
-> Hash (cost=66.20..66.20 rows=11 width=4)
-> Seq Scan on categories c (cost=0.00..66.20 rows=11 width=4)
Filter: (allow_badges AND (NOT read_restricted))
-> Index Scan using index_posts_on_topic_id_and_post_number on posts p (cost=0.43..870.75 rows=234 width=8)
Index Cond: (topic_id = t_1.id)
Filter: ((deleted_at IS NULL) AND (post_type = ANY ('{1,2,3}'::integer[])))
-> Index Only Scan using post_timings_unique on post_timings pt (cost=0.57..8.57 rows=10 width=12)
Index Cond: ((topic_id = p.topic_id) AND (post_number = p.post_number))
-> Sort (cost=14427.41..14442.29 rows=5955 width=8)
Sort Key: ub.user_id
-> Bitmap Heap Scan on user_badges ub (cost=126.58..14054.03 rows=5955 width=8)
Recheck Cond: (badge_id = 17)
-> Bitmap Index Scan on index_user_badges_on_badge_id_and_user_id (cost=0.00..125.09 rows=5955 width=0)
Index Cond: (badge_id = 17)