En varios de nuestros foros más grandes, es la misma consulta la que causa una alta carga en la base de datos: la insignia 17 (Lector), probablemente debido al tamaño de topic_timings.
El explain se ve bastante horrible. No veo ninguna mejora de rendimiento obvia, excepto desactivar la insignia, pero pensé en publicar aquí para ver si alguien tiene alguna 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..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)