How to unite 2 subqueries to reduce retrieving data from cumbersome tables? 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) &lt; COALESCE(message_last_write_id_user2, 0) AND 
        m.id &gt; COALESCE(message_last_read_id_user1, 0) AND m.id &lt;= 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) &lt; COALESCE(message_last_write_id_user1, 0) AND 
    c.user1_id &gt; 0 AND m.id &gt; COALESCE(message_last_read_id_user2, 0) AND m.id &lt;= 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 &gt;= 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?
One idea is to count using an expression:

SELECT u.id, u.login, u.last_login_time
     , COUNT(CASE WHEN COALESCE(c.message_last_read_id_user1, 0) 
                     &lt; COALESCE(message_last_write_id_user2, 0) 
                   AND m.id &gt; COALESCE(message_last_read_id_user1, 0) 
                   AND m.id &lt;= COALESCE(message_last_write_id_user2, 0) 
                   AND m.poster_id = c.user2_id
                  THEN 1
             END) as unread_user1
     , COUNT(CASE WHEN COALESCE(message_last_read_id_user2, 0) 
                     &lt; COALESCE(message_last_write_id_user1, 0) 
                   AND c.user1_id &gt; 0 
                   AND m.id &gt; COALESCE(message_last_read_id_user2, 0) 
                   AND m.id &lt;= COALESCE(message_last_write_id_user1, 0) 
                   AND m.poster_id &lt;&gt; c.user2_id
                  THEN 1
             END) as unread_user2   
FROM user u 
JOIN conversation c
    ON c.user1_id = u.id
JOIN message m
    ON m.conversation_id = c.id
WHERE u.last_login_time &gt;= 1452504418
GROUP BY u.id, u.login, u.last_login_time;


I might have missed some parts when rewriting, but hopefully you will get some ideas.