ChatState table:
id:serial | chat_owner_phone_id text | state:JSON | createdTime:timestamp
1|44397775468|{"pinned": false, "chat_id": "33628241540", "isGroup": false, "archived": false, "unreadCount": 0, "chatStartTimestamp": 1662733034}| 2022-09-09T19:17:25Z
Message table:
id:uuid | content:JSON | createdTime:timestamp
b86b2fac-c414-4f39-94be-258990b3cc62|{"text": "je suis plus intéressée", "chat_id": "33628241540", "from_me": false, "phone_id": "44397775468", "timestamp": 1662552335, "event_name": "TextMessage", "message_id": "BD176C76A39DC372E1E92529D2624519"}|2022-09-09T20:19:28Z
I want to fetch each chat with last message. This works but is there a more efficient method?
SELECT state, content FROM chatstate LEFT JOIN
(SELECT DISTINCT ON (content->>'chat_id') * from message as m
where content->>'phone_id' = $1
order by content->>'chat_id', content->>'timestamp' desc
) o on state->>'chat_id' = content->>'chat_id'
where chat_owner_phone_id = $2 ORDER BY state->>'chatStartTimestamp' desc;
$1 = '44397775468', $2 = '44397775468' (Two parameter is same)
Explain analyze output:
Sort (cost=412571.36..412577.95 rows=2634 width=747) (actual time=405.480..405.523 rows=443 loops=1)
Sort Key: ((chatstate.state ->> 'conversationTimestamp'::text)) DESC
Sort Method: quicksort Memory: 476kB
-> Hash Right Join (cost=410687.81..412421.71 rows=2634 width=747) (actual time=394.259..404.773 rows=443 loops=1)
Hash Cond: ((m.content ->> 'chat_id'::text) = chatstate.chat_id)
-> Unique (cost=407711.21..409390.39 rows=3775 width=709) (actual time=376.657..386.708 rows=590 loops=1)
-> Sort (cost=407711.21..408550.80 rows=335837 width=709) (actual time=376.655..380.406 rows=55504 loops=1)
" Sort Key: ((m.content ->> 'chat_id'::text)), ((m.content ->> 'timestamp'::text)) DESC"
Sort Method: quicksort Memory: 36626kB
-> Index Scan using message_phone_id_hash on message m (cost=0.00..376885.73 rows=335837 width=709) (actual time=0.070..189.617 rows=55504 loops=1)
Index Cond: ((content ->> 'phone_id'::text) = '44397775468'::text)
-> Hash (cost=2943.68..2943.68 rows=2634 width=253) (actual time=17.564..17.576 rows=443 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 161kB
-> Index Scan using chatstate_chat_owner_phone_id_chatid_unique on chatstate (cost=0.43..2943.68 rows=2634 width=253) (actual time=13.254..17.321 rows=443 loops=1)
Index Cond: (chat_owner_phone_id = '44397775468'::text)
Planning Time: 4.995 ms
JIT:
Functions: 20
" Options: Inlining false, Optimization false, Expressions true, Deforming true"
" Timing: Generation 1.574 ms, Inlining 0.000 ms, Optimization 0.787 ms, Emission 12.155 ms, Total 14.516 ms"
Execution Time: 450.592 ms