If the deletes are taking this long, your tables may be bloated.
EXPLAIN ANALYZE
SELECT * FROM user_actions , user_actions ua2
WHERE ( user_actions.action_type = ua2.action_type AND
user_actions.user_id = ua2.user_id AND
user_actions.acting_user_id = ua2.acting_user_id AND
user_actions.target_post_id = ua2.target_post_id AND
user_actions.target_post_id > 0 AND
user_actions.id > ua2.id
)
The query above may show you where time is being spent the most:
"Nested Loop (cost=0.84..52037.91 rows=174200175 width=96) (actual time=8612.056..8612.056 rows=0 loops=1)"
" -> Index Scan using index_user_actions_on_target_post_id on user_actions (cost=0.42..3752.75 rows=68381 width=48) (actual time=6.663..38.637 rows=74415 loops=1)"
" Index Cond: (target_post_id > 0)"
" -> Index Scan using index_user_actions_on_user_id_and_action_type on user_actions ua2 (cost=0.42..0.70 rows=1 width=48) (actual time=0.115..0.115 rows=0 loops=74415)"
" Index Cond: ((user_id = user_actions.user_id) AND (action_type = user_actions.action_type))"
" Filter: ((user_actions.id > id) AND (user_actions.acting_user_id = acting_user_id) AND (user_actions.target_post_id = target_post_id))"
" Rows Removed by Filter: 224"
"Total runtime: 8612.127 ms"
The above, ran on a 130k posts Discourse in 8 seconds.
Maybe running this can help:
VACUUM VERBOSE ANALYZE user_actions;```