Su molti dei nostri forum più grandi è sempre la stessa query a causare un elevato carico sul database: badge 17 (Lettore), probabilmente a causa delle dimensioni di topic_timings.
L’explain sembra piuttosto orrendo. Non vedo miglioramenti ovvi delle prestazioni tranne che disattivare il badge, ma ho pensato di postare qui per vedere se qualcuno ha qualche idea.
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..335182.35 rows=4883 width=20)
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)