Nós recentemente excluímos TODAS as menções e, desde então, a carga da CPU caiu para 15-20%, o que é muito bom, mas, portanto, a consulta acima não retornará resultados. (ou apenas alguns, já que a excluímos)
1 curtida
A consulta acima deve retornar resultados em qualquer caso, ela começa com EXPLAIN VERBOSE, então retorna um plano de execução. Quando tiver tempo, você poderia copiar e colar a consulta exata da minha mensagem, executá-la e compartilhar os resultados?
Limit (custo=123.37..22204.48 linhas=1000 largura=40)
Saída: users.id, (array_agg(ARRAY[uccm.id, c_msg.id])), uccm.user_id
-> GroupAggregate (custo=123.37..339399.59 linhas=15365 largura=40)
Saída: users.id, array_agg(ARRAY[uccm.id, c_msg.id]), uccm.user_id
Chave de Grupo: users.id, uccm.user_id
-> Nested Loop (custo=123.37..336077.65 linhas=417317 largura=24)
Saída: users.id, uccm.user_id, uccm.id, c_msg.id
Inner Unique: true
-> Merge Join (custo=123.23..271629.01 linhas=417317 largura=28)
Saída: users.id, group_users.group_id, uccm.id, uccm.user_id, c_msg.id
Condição de Mesclagem: (user_options.user_id = group_users.user_id)
-> Nested Loop Left Join (custo=121.72..221430.16 linhas=132835 largura=28)
Saída: users.id, user_options.user_id, uccm.id, uccm.user_id, c_msg.id
Filtro: ((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 (custo=121.32..149930.76 linhas=132967 largura=42)
Saída: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.following, cc.chatable_type, c_msg.id
Filtro de Junção: ((c_msg.user_id <> users.id) AND (cc.id = c_msg.chat_channel_id))
-> Nested Loop (custo=120.91..57868.92 linhas=21066 largura=54)
Saída: 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 (custo=120.63..51231.73 linhas=21066 largura=33)
Saída: 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 (custo=120.21..36835.88 linhas=21096 largura=29)
Saída: 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
Condição de Mesclagem: (uccm.user_id = users.id)
-> Index Scan usando user_chat_channel_unique_memberships em public.user_chat_channel_memberships uccm (custo=0.41..12177.25 linhas=21618 largura=25)
Saída: 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 usando users_pkey em public.users (custo=0.42..23823.53 linhas=237544 largura=4)
Saída: users.id
Filtro: (users.last_seen_at < '2023-11-27 08:00:00'::timestamp without time zone)
-> Index Scan usando index_user_options_on_user_id em public.user_options (custo=0.42..0.68 linhas=1 largura=4)
Saída: user_options.user_id
Condição de Índice: (user_options.user_id = users.id)
Filtro: (user_options.chat_enabled AND (user_options.chat_email_frequency = 1))
-> Index Scan usando chat_channels_pkey em public.chat_channels cc (custo=0.28..0.32 linhas=1 largura=21)
Saída: 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
Condição de Índice: (cc.id = uccm.chat_channel_id)
-> Index Scan usando index_chat_messages_on_chat_channel_id_and_created_at em public.chat_messages c_msg (custo=0.41..4.19 linhas=12 largura=16)
Saída: c_msg.id, c_msg.chat_channel_id, c_msg.user_id
Condição de Índice: ((c_msg.chat_channel_id = uccm.chat_channel_id) AND (c_msg.created_at > '2023-11-20 08:00:00'::timestamp without time zone))
Filtro: ((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 usando chat_mentions_index em public.chat_mentions c_mentions (custo=0.40..0.52 linhas=1 largura=8)
Saída: c_mentions.chat_message_id, c_mentions.user_id, c_mentions.notification_id
Condição de Índice: (c_mentions.chat_message_id = c_msg.id)
-> Index Only Scan usando index_group_users_on_user_id_and_group_id em public.group_users (custo=0.42..44019.59 linhas=760157 largura=8)
Saída: group_users.user_id, group_users.group_id
-> Index Only Scan usando groups_pkey em public.groups (custo=0.14..0.16 linhas=1 largura=4)
Saída: groups.id
Condição de Índice: (groups.id = group_users.group_id)
(51 linhas)
2 curtidas
@sok777 obrigado por compartilhar o plano de consulta! É muito útil.
Sua instância contém uma grande quantidade de usuários e uma grande quantidade de registros de user_chat_channel_memberships (como você compartilhou aqui).
Antes de você compartilhar o plano de teste, tentei reproduzir o problema em sites com grande quantidade de usuários, mas a consulta funcionou bem. Agora, vejo que esses sites não têm uma grande quantidade de user_chat_channel_memberships, muito provavelmente é isso que é específico do seu site e o que causou problemas tão sérios no seu caso.
Pelo que entendi, você não tem problemas agora, porque removeu todas as menções em algum momento, mas eu esperaria que essa consulta se tornasse problemática novamente em algum momento, mesmo com uma pequena quantidade de menções. Portanto, vou otimizá-la de qualquer maneira.
Atualmente estou trabalhando para corrigir o problema subjacente que causou a criação de enormes quantidades de registros de chat_mention no banco de dados. Enquanto isso está em andamento, é importante manter @ todos e @ aqui nas palavras observadas. Postarei uma atualização aqui quando cuidar disso, e depois disso, garantirei a otimização desta consulta.
6 curtidas
O problema subjacente foi resolvido por
Não deve haver mais problemas de desempenho e não há necessidade de manter menções em massa em palavras observadas.
Em relação a isto:
Parece que não há reclamações sobre esta consulta, então eu não a otimizei. Por favor, não hesite em relatar em caso de problemas.
5 curtidas