[Answered ]-Complex SQL query for messaging app

Fix syntax

Basically, you just need to move the WHERE condition to its proper place, like @Lamak commented:

SELECT  ...
FROM conversation_user cu
INNER JOIN ...
WHERE conversation_id IN
    (SELECT conversation_id
     FROM conversation_user
     WHERE user_id = 32)
AND user_id != 32
ORDER BY m.send_date DESC
LIMIT 1;

Make it fast

According to comment:

I’m trying to select the last message in each of the […] conversations user 32 is having.

SELECT cu.conversation_id
     , ufrom.username AS from_user
     , uto.username   AS to_user
     , m.message
     , m.send_date
FROM   conversation_user cu
LEFT   JOIN LATERAL (
   SELECT from_user_id, to_user_id, message, send_date
   FROM   message   m
   WHERE  m.conversation_id = cu.conversation_id
   ORDER  BY send_date DESC
   LIMIT  1
   ) m ON TRUE
LEFT   JOIN auth_user ufrom ON ufrom.id = m.from_user_id
LEFT   JOIN auth_user uto   ON uto.id = m.to_user_id
WHERE  cu.user_id = 32;

Notes

• A join is typically faster than an IN construct on a subquery, especially with big sets. But you don’t need either. You have been overcomplicating things.

• You could have a simpler query with DISTINCT ON, but I expect this one to be faster.
  Details:

  • Select first row in each GROUP BY group?
  • Optimize GROUP BY query to retrieve latest record per user

DB design

• The query assumes that (user_id, conversation_id) is UNIQUE – which you confirmed in the comment. Be sure to add an actual UNIQUE constraint, which provides the much needed index automatically.

• An index on message on (conversation_id, send_date DESC) would help, too. Details:

  • Is a composite index also good for queries on the first field?

• Assuming auth_user.id is the PK, so it would be indexed.

• message.to_user_uid is probably supposed to be to_user_id – like from_user_id.

• You probably want to add another FK to stay consistent:

  "message_to_user_id_fkey" FOREIGN KEY (to_user_id) REFERENCES auth_user(id)
  

  Not sure why you think you need DEFERRABLE INITIALLY DEFERRED. If you don’t know you need this, remove it. It’s for special purposes and makes regular operations more expensive.

• If only two users can take part in the same conversation, it would be more efficient to remove conversation_user altogether and add user1 and user2 or similar to conversation – unless there are more attributes for each combination of user/conversation. Potentially simplify message, too. You only need a boolean information instead of from_user and to_user.
  According to relational theory, conversation can be seen as a the implementation of many-to-many relationship between table auth_user and itself.