I have three queries that have have been running for over 1 hour at 100% CPU.
I identified them with:
sudo -u postgres psql -c "SELECT (now() - xact_start) as t, pid, query FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active') order by t desc;";
And got the output below. I rebuilt earlier today. It’s not an urgent problem as we have a 12 core server and this keeps 1 core occupied full time, worth looking in to though.
t | pid | query
-----------------+-------+--------------------------------------------------------------------------------------------------------------------------------------
01:02:34.648308 | 1455 | SELECT +
| | posts.id +
| | FROM posts +
| | LEFT JOIN post_search_data pd +
| | ON pd.locale = 'en' +
| | AND pd.version = 2 +
| | AND pd.post_id = posts.id +
| | LEFT JOIN topics ON topics.id = posts.topic_id +
| | WHERE pd.post_id IS NULL +
| | AND topics.id IS NOT NULL +
| | AND topics.deleted_at IS NULL +
| | AND posts.raw != '' +
| | ORDER BY posts.id DESC +
| | LIMIT 20000 +
| |
01:02:34.648308 | 1461 | SELECT +
| | posts.id +
| | FROM posts +
| | LEFT JOIN post_search_data pd +
| | ON pd.locale = 'en' +
| | AND pd.version = 2 +
| | AND pd.post_id = posts.id +
| | LEFT JOIN topics ON topics.id = posts.topic_id +
| | WHERE pd.post_id IS NULL +
| | AND topics.id IS NOT NULL +
| | AND topics.deleted_at IS NULL +
| | AND posts.raw != '' +
| | ORDER BY posts.id DESC +
| | LIMIT 20000 +
| |
01:02:34.648308 | 1462 | SELECT +
| | posts.id +
| | FROM posts +
| | LEFT JOIN post_search_data pd +
| | ON pd.locale = 'en' +
| | AND pd.version = 2 +
| | AND pd.post_id = posts.id +
| | LEFT JOIN topics ON topics.id = posts.topic_id +
| | WHERE pd.post_id IS NULL +
| | AND topics.id IS NOT NULL +
| | AND topics.deleted_at IS NULL +
| | AND posts.raw != '' +
| | ORDER BY posts.id DESC +
| | LIMIT 20000