Postgres优化查询：如何高效筛选两用户间的非群聊会话

针对你需要筛选指定两个用户之间的非群聊会话的需求，以下几种写法比原来的INTERSECT更简洁高效，同时能覆盖不同业务场景：

场景1：非群聊会话必然是一对一（仅两个用户） #

如果你的业务逻辑中，is_group_chat = false的会话一定只有两名参与者，那么可以用双EXISTS子查询实现，代码更简洁且性能更优：

SELECT c.id AS c_id, c.chat_name AS chat_name, c.created_at AS created_at
FROM chats c
WHERE c.is_group_chat = false
  AND EXISTS (
    SELECT 1 
    FROM user_chats uc 
    WHERE uc.chat_id = c.id 
      AND uc.user_id = '39438219-1488-4e6f-820a-2ba16ba12e01'
  )
  AND EXISTS (
    SELECT 1 
    FROM user_chats uc 
    WHERE uc.chat_id = c.id 
      AND uc.user_id = '16c1beb9-687a-45b3-8d2b-88c71a681168'
  );

这种写法避免了重复查询chats表，仅通过两次存在性检查确认会话包含指定的两个用户，执行效率比INTERSECT更高。

场景2：需要严格确保会话仅包含这两个用户（无其他参与者） #

如果非群聊会话可能存在超过两个用户的情况，需要额外排除这类会话，可以用GROUP BY + HAVING组合实现：

SELECT c.id AS c_id, c.chat_name AS chat_name, c.created_at AS created_at
FROM chats c
JOIN user_chats uc ON uc.chat_id = c.id
WHERE c.is_group_chat = false
  AND uc.user_id IN ('39438219-1488-4e6f-820a-2ba16ba12e01', '16c1beb9-687a-45b3-8d2b-88c71a681168')
GROUP BY c.id, c.chat_name, c.created_at
HAVING 
  -- 确保会话恰好有两个不同的用户
  COUNT(DISTINCT uc.user_id) = 2
  -- 排除存在其他用户的会话
  AND NOT EXISTS (
    SELECT 1 
    FROM user_chats uc2 
    WHERE uc2.chat_id = c.id
      AND uc2.user_id NOT IN ('39438219-1488-4e6f-820a-2ba16ba12e01', '16c1beb9-687a-45b3-8d2b-88c71a681168')
  );

或者用PostgreSQL支持的BOOL_AND函数简化逻辑：

SELECT c.id AS c_id, c.chat_name AS chat_name, c.created_at AS created_at
FROM chats c
WHERE c.is_group_chat = false
  AND EXISTS (
    SELECT 1
    FROM user_chats uc
    WHERE uc.chat_id = c.id
    GROUP BY uc.chat_id
    HAVING 
      COUNT(DISTINCT uc.user_id) = 2
      AND BOOL_AND(uc.user_id IN ('39438219-1488-4e6f-820a-2ba16ba12e01', '16c1beb9-687a-45b3-8d2b-88c71a681168'))
  );

BOOL_AND会检查分组内所有user_id都属于指定集合，结合COUNT(DISTINCT)确保恰好有两个用户，完美匹配“仅存在于指定两个用户之间”的需求。

为什么比原写法更优 #

原INTERSECT写法需要执行两次几乎完全相同的查询，再对结果集取交集，不仅代码冗余，还会带来额外的查询开销。上述优化方案仅需一次主查询，通过子查询或分组条件完成筛选，逻辑更清晰，性能也更出色。

内容的提问来源于stack exchange，提问作者Divya