Postgres using 100% of CPU

Installation
13

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;```
6 Likes