# Postgres using 100% of CPU

**URL:** <https://meta.discourse.org/t/postgres-using-100-of-cpu/45261>\
**Category:** Self-hosting\
**Tags:** server-resources\
**Created:** [5 juni 2016 om 00:19 UTC](https://meta.discourse.org/t/postgres-using-100-of-cpu/45261 "2016-06-05T00:19:26Z")\
**Posts on this page:** 1\
**Showing post:** 13

<div class="post-metadata">

**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\
**Post date:** [6 juni 2016 om 15:17 UTC](https://meta.discourse.org/t/postgres-using-100-of-cpu/45261/13 "2016-06-06T15:17:18Z")

</div>

If the deletes are taking this long, your tables may be bloated.

```plaintext
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:

```plaintext
"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:

````plaintext
VACUUM VERBOSE ANALYZE user_actions;```
````

---

_[View the full topic](https://meta.discourse.org/t/postgres-using-100-of-cpu/45261)._
