2

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?

2 Answers 2

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.

5
  • I tried your method but it's slower than mine.
    – Dmitro
    Jan 11, 2016 at 14:55
  • Have you compared the plans and seen where each cost originates from? Jan 11, 2016 at 16:32
  • Problem with message table join, I have simplified your query pastebin.com/80Vzb9za and made explain analyze explain.depesz.com/s/2B0. Index scan conversation_pkey with condition id = m.conversation_id, tooks most time. Is it possible to fix it?
    – Dmitro
    Jan 12, 2016 at 15:43
  • Oh, I have postgresql 9.1 and I don't have LATERAL JOIN.
    – Dmitro
    Jan 12, 2016 at 16:10
  • I removed that one. Always a good idea to tag your question with the correct version, at least I assume current version unless tagged otherwise. Jan 12, 2016 at 19:31
0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.