There are 3 tables: user
, conversation
, message
.
Table user
:
- id,
- login,
- last_login_time (time of last user's login),
- ...
Table conversation
:
- id,
- user1_id,
- user2_id,
- message_last_read_id_user1 - id of last read message by user user1_id,
- message_last_write_id_user1 - id of last written message by user user1_id,
- message_last_read_id_user2 - id of last read message by user user2_id
- message_last_write_id_user2 - id of last written message by user user2_id,
- ...
Table message
:
- id,
- conversation_id,
- poster_id - user's id, who send that message,
- msg,
- ...
The aim to select users who logged during the last hour and select for each of them amount of unread messages:
SELECT u.id, u.login, u.last_login_time,
(
SELECT count ( * ) FROM conversation c, message m
WHERE c.user1_id = u.id AND m.conversation_id = c.id AND
COALESCE(c.message_last_read_id_user1, 0) < COALESCE(message_last_write_id_user2, 0) AND
m.id > COALESCE(message_last_read_id_user1, 0) AND m.id <= COALESCE(message_last_write_id_user2, 0) AND
m.poster_id = c.user2_id
) AS unread_user1,
(
SELECT count ( * ) FROM conversation c, message m
WHERE user2_id = u.id AND m.conversation_id = c.id AND
COALESCE(message_last_read_id_user2, 0) < COALESCE(message_last_write_id_user1, 0) AND
c.user1_id > 0 AND m.id > COALESCE(message_last_read_id_user2, 0) AND m.id <= COALESCE(message_last_write_id_user1, 0) AND
m.poster_id != c.user2_id
) AS unread_user2
FROM user u WHERE u.last_login_time >= 1452504418;
One of unread_user1 or unread_user2 always will be 0. Query plan is here http://explain.depesz.com/s/VT6
In that query two subqueries have cross join conversation c, message m
and it's very heavy operation. So maybe someone have idea how to unite these subqueries into one?