Web client slow load times for chat history

bstutting

Platinum Partner
Joined
Jun 10, 2022
Messages
2
Reaction score
0
Sharing a finding and asking whether anyone else sees the same thing.

Setup
- V20 Update 9 (Build 995), on-premise on Debian 12 / PostgreSQL 15, 8 vCPU, 31 GB RAM
- Around 300 users, heavy internal chat use
- Roughly 1 million chat messages and 7 million rows in chat_participant
- Server is not under load (load average well under 1, plenty of free memory)

Symptom
Users who are in many large, busy group chats wait 3 to 4 seconds for the chat list to load in the web client. Light chat users on the same system get it in well under a second. Opening a conversation once the list is loaded is fast. In the browser dev tools the time is all server wait on the conversation list request.

What I found
While an affected user loads the chat list, this is the statement running in PostgreSQL (first part shown):

with lastmsg as /*materialized*/ (
select me.id as idp, max(fkid_message) as id_message
from chat_conversation_member me
join chat_participant mine on (mine.fkid_chat_conversation_member=me.id and mine.is_visible)
where me.participant_no=$1 and me.type=0 and not me.is_removed and not me.is_archived
group by idp order by id_message desc OFFSET $2 LIMIT $3
)
...

The stock indexes on chat_participant cover fkid_chat_conversation_member but not fkid_message, so max(fkid_message) cannot be answered from an index. For a heavy user, EXPLAIN ANALYZE shows a Parallel Seq Scan over the whole chat_participant table.

Test on an offline copy of the database (not production)
For the heaviest chat user (about 100 active conversations, about 180,000 visible participant rows):
- Stock schema: 1.6 to 2.3 s, about 58,700 buffer pages
- With one extra index: about 60 ms, about 1,440 buffer pages, Index Only Scan

The index I tested:
create index on chat_participant (fkid_chat_conversation_member, fkid_message) where is_visible;

It came out at 141 MB and built in a few seconds.

To be clear, I have NOT applied this to a production system. I know direct database changes are unsupported and may be lost on upgrade or restore. I have opened a support ticket with the details and am waiting on guidance.

Questions
1. Is anyone else with a large chat history seeing a slow chat list for users in big group chats?
2. Has 3CX acknowledged this, or is there a supported way to improve it short of purging chat history?
3. If anyone from 3CX reads this: could a covering index like the above, or a rewrite of the lastmsg query, be considered for a future update?

Happy to share the full EXPLAIN output if it is useful.

Thanks
 

Latest Posts

Forum statistics

Threads
112,541
Messages
593,105
Members
165,749
Latest member
LzyPenguin