Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity.

This are the message columns :

id
sender_id
receiver_id
body
activity_id

I'm looking to get all the activities who have at least one message from both directions of message.

Here is what I mean, message id 1 :

id|sender_id|receiver_id|body   |activity_id
1 |20       |44         |hi     | 55 

So for activity 55 there is only one directional messaging, if you take activity messages below :

id|sender_id|receiver_id|body     |activity_id
1 |20       |44         |hi       | 55 
3 |44       |20         |hi there | 55
4 |44       |20         |who're u | 55

So above messages for activity 55 are "multi directional", meaning there are messages from both sides. In this case there are more than one message from 44 to 22 but thats ok, as long as there is at least one message from 44 to 22 and at least one message from 22 to 44 I want to get that activity_id.

I'd like to get all the activity_ids which have at least one or more message exchange from both sides.

I was thinking to do it something like this :

select distinct(activity_id) from messages
where sender_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
) or receiver_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
)

It's not really working at the moment, can someone point me to the right direction?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

You don't need to include the table activities in the query at all. Only messages:

SELECT DISTINCT activity_id
FROM   messages m
WHERE  sender_id <= receiver_id   -- reduce rows
AND    EXISTS (
    SELECT 1
    FROM   messages
    WHERE  activity_id = m.activity_id
    AND    sender_id   = m.receiver_id
    AND    receiver_id = m.sender_id
    );

That's much like @dezso's query, just on the right table and with an additional feature.

The condition sender_id <= receiver_idis not strictly needed, but excludes half of the rows right away, increasing performance.

Index

The perfect index for the case should be a multicolumn index on all three columns in the WHERE condition of the EXISTS semi-join:

CREATE INDEX foo_idx ON messages (activity_id, sender_id, receiver_id);

Two squeeze out the last drop of read performance, you could make that a highly specialized partial index:

CREATE INDEX foo_idx ON messages (activity_id, sender_id, receiver_id)
WHERE receiver_id <= sender_id;

And adapt the query accordingly:

...
WHERE  EXISTS (
    SELECT 1
    FROM   messages
    WHERE  activity_id = m.activity_id
    AND    sender_id   = m.receiver_id
    AND    receiver_id = m.sender_id
    AND   receiver_id <= sender_id  -- redundant, just for partial index
    );

The added condition lets Postgres know, it can use the partial index.
This will only be moderately faster than the plain solution on top. Only worth the hassle if you need to optimize performance.

Alternatives

If there are many messages per activity, in particular also multiple message exchanges between different pairs, then this query will be faster:

SELECT activity_id
FROM   activities a
WHERE  EXISTS (
   SELECT 1
   FROM   messages m
   WHERE  m.activity_id = a.activity_id
   AND    sender_id <= receiver_id
   AND    EXISTS (
      SELECT 1
      FROM   messages
      WHERE  activity_id = m.activity_id
      AND    sender_id   = m.receiver_id
      AND    receiver_id = m.sender_id
      AND    receiver_id <= sender_id -- ?
      )
   );

Similar optimization possible as above. But you'd need another index:

CREATE INDEX foo2_idx ON messages (activity_id, sender_id, receiver_id)
WHERE  sender_id <= receiver_id;

In this case the simple index might prevail (instead of reading from two smaller indexes). Again, test.

And while you are retrieving the whole table I would also compare performance with INTERSECT:

SELECT DISTINCT activity_id
FROM (
   SELECT DISTINCT activity_id, sender_id, receiver_id
   FROM   messages m
   WHERE  sender_id <= receiver_id
   INTERSECT 
   SELECT DISTINCT activity_id, receiver_id, sender_id
   FROM   messages m
   WHERE  receiver_id <= sender_id
   ) sub;

Might perform well, even without index. I would experiment with indexes and possibly GROUP BY instead of DISTINCT, or drop the DISTINCT in the subquery (depending on data distribution) to find the fastest variant.

All of these queries count a message to self as "exchange", since those qualify for both directions. If there are no messages with sender_id = receiver_id, replace all the <= with < ...

share|improve this answer
1  
I would be interested which combination of query and index wins the race in your case (along with basic info on data distribution) ... –  Erwin Brandstetter Apr 4 at 16:37
1  
Thanks for another great response. is there a way I can measure that other than with explain analyze? I mean results get cached on db side, so what would be accurate way to measure these? –  Gandalf StormCrow Apr 4 at 20:38
    
The results themselves don't get cached. Only the underlying tables - and that's ok, since it's the typical mode of operation that tables are cached. Just run each query a couple of times and take the best result. In Postgres 9.3 you can use (TIMING OFF) to only get total server runtime without overhead for measuring time in each node. –  Erwin Brandstetter Apr 4 at 22:09

This query is checking if the (sender, receiver) pair is there for the same activity:

SELECT DISTINCT activity_id
  FROM activities a
 WHERE EXISTS (
                SELECT 1
                  FROM activities
                 WHERE activity_id = a.activity_id
                   AND sender_id = a.receiver_id
                   AND receiver_id = a.sender_id
              );

The same index ypercube suggests can be useful with this, too.

share|improve this answer

There is a probably more efficient but surely less complicated way:

SELECT activity_id
FROM messages
GROUP BY activity_id
HAVING MIN(sender_id) < MAX(sender_id) ;

An index on (activity_id, sender_id) would surely improve efficiency.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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