@sok777 thank you for sharing the query plan! It’s very helpful.
Your instance contains a big amount of users and a big amount of the user_chat_channel_memberships records (as you shared here).
Before you shared the test plan, I tried to reproduce the problem on sites with big amount of users, but the query worked well. Now, I see that those sites don’t have a big amount of user_chat_channel_memberships, very likely this is what’s specific about your site and what caused so serious problems in your case.
As I understand, you don’t have problems now, because you removed all mentions at some point, but I would expect this query to become problematic again at some point, even with small amount of mentions. So I will be optimizing it in any case.
I’m currently working on fixing the underling problem that caused creating huge loads of chat_mention records in database. While it’s in progress, it’s important to keep @ all and @ here in the watched words. I’ll post an update here when I take care of that, and after that I’ll make sure to optimize this query.