Auf mehreren unserer größten Foren ist es immer die gleiche Abfrage, die eine hohe Datenbanklast verursacht: Abzeichen 17 (Leser), wahrscheinlich aufgrund der Größe von topic_timings.
Die Erklärung sieht ziemlich schrecklich aus. Ich sehe keine offensichtlichen Leistungsverbesserungen, außer dem Ausschalten des Abzeichens, aber ich dachte, ich poste hier und sehe, ob jemand Ideen hat.
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)