查找具有两列的唯一组合的所有行

Question

我有这个桌子messages;

sender_id    recipient_id
1            2
1            3
1            3
2            1
3            1
2            3

我希望选择这样的行：

1. 任一sender_id或receiver_id= current_user.id。
2. 另一个字段应该是唯一的。

即我想从表中选择唯一，sender_id = 2或者recipient_id = 2我需要以下结果：

sender_id    recipient_id
2            1
2            3

怎么做？
为什么？因为我希望构建一个类似于Facebook的收件箱，在该收件箱中，已发送和已接收的消息将被聚合，而此查询是迄今为止的瓶颈。

我正在使用Rails 3.2和Postgres 9.3。

Answer 1

SELECT sender_id AS user_id, recipient_id AS other_user_id
FROM   messages
WHERE  sender_id = $current_user_id

UNION
SELECT recipient_id, sender_id
FROM   messages
WHERE  recipient_id = $current_user_id
-- ORDER BY 1, 2  -- optional

UNION（not UNION ALL）从结果中删除重复项，从而DISTINCT不必要。您可能希望ORDER BY在末尾添加以进行排序的输出。

假设一个大表具有较少的合格行，则两个btree索引通常可提供最佳性能。一个以领先或唯一sender_id，另一个以领先或唯一recipient_id。

单个 多列索引 （(sender_id, receiver_id)反之亦然）也可以，但是通常较慢。看：

• 复合索引对第一个字段的查询是否也有用？