Postgresql – How to unite 2 subqueries to reduce retrieving data from cumbersome tables

performancepostgresqlpostgresql-9.1query-performance

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?

Best Answer

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.

Related Question