I had done that earlier, actually. I killed the job to let a reindex finish, so this was it restarting after that.
It looks like this morning the jobs finished sometime overnight and are running again. Current status:
This subquery returns around 13,000 rows on our instance:
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
It’s then being LEFT JOINED to the user_badges table, which has 84,000 rows. It seems like something in the last condition WHERE ub.badge_id = 15 AND q.user_id IS NULL is blowing up this query. If I leave off the WHERE clause then it will execute in a reasonable amount of time (20 seconds or so), but if I include even just the WHERE ub.badge_id = 15, then I cannot even get an EXPLAIN to execute against this query in a reasonable amount of time. The EXPLAIN has been hanging for several minutes now with no results. The actual execution of the full query has been running for hours. Is there anything we can do to optimize this query?
