# Query causing 400% CPU Load **URL:** \ **Category:** Bug\ **Tags:** chat\ **Created:** [November 15, 2023, 10:46am UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542 "2023-11-15T10:46:49Z")\ **Posts on this page:** 1\ **Showing post:** 25
**Author:** ![sok777](https://avatars.discourse-cdn.com/v4/letter/s/82dd89/32.png) [@sok777](https://meta.discourse.org/u/sok777)\ **Post date:** [November 30, 2023, 4:16pm UTC](https://meta.discourse.org/t/query-causing-400-cpu-load/285542/25 "2023-11-30T16:16:21Z")
```plaintext Limit (cost=123.37..22204.48 rows=1000 width=40) Output: users.id, (array_agg(ARRAY[uccm.id, c_msg.id])), uccm.user_id -> GroupAggregate (cost=123.37..339399.59 rows=15365 width=40) Output: users.id, array_agg(ARRAY[uccm.id, c_msg.id]), uccm.user_id Group Key: users.id, uccm.user_id -> Nested Loop (cost=123.37..336077.65 rows=417317 width=24) Output: users.id, uccm.user_id, uccm.id, c_msg.id Inner Unique: true -> Merge Join (cost=123.23..271629.01 rows=417317 width=28) Output: users.id, group_users.group_id, uccm.id, uccm.user_id, c_msg.id Merge Cond: (user_options.user_id = group_users.user_id) -> Nested Loop Left Join (cost=121.72..221430.16 rows=132835 width=28) Output: users.id, user_options.user_id, uccm.id, uccm.user_id, c_msg.id Filter: (((uccm.user_id = c_mentions.user_id) AND (uccm.following IS TRUE) AND ((cc.chatable_type)::text = 'Category'::text)) OR ((cc.chatable_type)::text = 'DirectMessage'::text)) -> Nested Loop (cost=121.32..149930.76 rows=132967 width=42) Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.following, cc.chatable_type, c_msg.id Join Filter: ((c_msg.user_id <> users.id) AND (cc.id = c_msg.chat_channel_id)) -> Nested Loop (cost=120.91..57868.92 rows=21066 width=54) Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following, cc.id, cc.chatable_type Inner Unique: true -> Nested Loop (cost=120.63..51231.73 rows=21066 width=33) Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following Inner Unique: true -> Merge Join (cost=120.21..36835.88 rows=21096 width=29) Output: users.id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following Inner Unique: true Merge Cond: (uccm.user_id = users.id) -> Index Scan using user_chat_channel_unique_memberships on public.user_chat_channel_memberships uccm (cost=0.41..12177.25 rows=21618 width=25) Output: uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.following, uccm.muted, uccm.desktop_notification_level, uccm.mobile_notification_level, uccm.created_at, uccm.updated_at, uccm.last_unread_mention_when_emailed_id, uccm.join_mode, uccm.last_viewed_at -> Index Scan using users_pkey on public.users (cost=0.42..23823.53 rows=237544 width=4) Output: users.id Filter: (users.last_seen_at < '2023-11-27 08:00:00'::timestamp without time zone) -> Index Scan using index_user_options_on_user_id on public.user_options (cost=0.42..0.68 rows=1 width=4) Output: user_options.user_id Index Cond: (user_options.user_id = users.id) Filter: (user_options.chat_enabled AND (user_options.chat_email_frequency = 1)) -> Index Scan using chat_channels_pkey on public.chat_channels cc (cost=0.28..0.32 rows=1 width=21) Output: cc.id, cc.chatable_id, cc.deleted_at, cc.deleted_by_id, cc.featured_in_category_id, cc.delete_after_seconds, cc.chatable_type, cc.created_at, cc.updated_at, cc.name, cc.description, cc.status, cc.user_count, cc.last_message_sent_at, cc.auto_join_users, cc.user_count_stale, cc.type, cc.slug, cc.allow_channel_wide_mentions, cc.messages_count, cc.threading_enabled, cc.last_message_id Index Cond: (cc.id = uccm.chat_channel_id) -> Index Scan using index_chat_messages_on_chat_channel_id_and_created_at on public.chat_messages c_msg (cost=0.41..4.19 rows=12 width=16) Output: c_msg.id, c_msg.chat_channel_id, c_msg.user_id Index Cond: ((c_msg.chat_channel_id = uccm.chat_channel_id) AND (c_msg.created_at > '2023-11-20 08:00:00'::timestamp without time zone)) Filter: ((c_msg.deleted_at IS NULL) AND ((uccm.last_read_message_id IS NULL) OR (c_msg.id > uccm.last_read_message_id)) AND ((uccm.last_unread_mention_when_emailed_id IS NULL) OR (c_msg.id > uccm.last_unread_mention_when_emailed_id))) -> Index Only Scan using chat_mentions_index on public.chat_mentions c_mentions (cost=0.40..0.52 rows=1 width=8) Output: c_mentions.chat_message_id, c_mentions.user_id, c_mentions.notification_id Index Cond: (c_mentions.chat_message_id = c_msg.id) -> Index Only Scan using index_group_users_on_user_id_and_group_id on public.group_users (cost=0.42..44019.59 rows=760157 width=8) Output: group_users.user_id, group_users.group_id -> Index Only Scan using groups_pkey on public.groups (cost=0.14..0.16 rows=1 width=4) Output: groups.id Index Cond: (groups.id = group_users.group_id) ``` (51 rows) --- _[View the full topic](https://meta.discourse.org/t/query-causing-400-cpu-load/285542)._