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
 
We have received the support ticket created for this case.
We shall proceed from there and update forum after resolution.
 

Latest Posts

Forum statistics

Threads
112,542
Messages
593,112
Members
165,751
Latest member
Teresa Nguyen