How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

Question

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?

Answer 1

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) 
                     < 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
                  THEN 1
             END) as unread_user1
     , COUNT(CASE WHEN 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
                  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 >= 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.

Answer 2

If newer messages consistently have larger ID values in the message table than the older messages, perhaps you could simplify the filter conditions in your subqueries by removing the checks involving message_last_write_id_*. Those checks may seem to be limiting the number of rows in the result set (thus potentially speeding up the subqueries), but in reality, if a message matches the conversation and its ID is larger than the last read ID and the poster is not user.id but the other party, then it is an unread message for user.id.

So, this is how you could count unread_user1:

SELECT
  COUNT(*)
FROM
  message AS m
  INNER JOIN conversation AS c
    ON  m.conversation_id = c.id
    AND m.poster_id = c.user2_id
WHERE
  c.user1_id = u.id
  AND m.id > COALESCE(c.message_last_read_id_user1, 0)

You could also try replacing the m.id > COALESCE(c.message_last_read_id_user1, 0) predicate with a disjunction to see if it can result in a more efficient execution plan for the entire query:

  …
  AND (c.message_last_read_id_user1 IS NULL
    OR m.id > c.message_last_read_id_user1)

Similarly, this would be the subquery for unread_user2:

SELECT
  COUNT(*)
FROM
  message AS m
  INNER JOIN conversation AS c
    ON  m.conversation_id = c.id
    AND m.poster_id = c.user1_id
WHERE
  c.user2_id = u.id
  AND m.id > COALESCE(c.message_last_read_id_user2, 0)
  -- or like this:
  -- AND (c.message_last_read_id_user2 IS NULL
  --   OR m.id > c.message_last_read_id_user2)