Cfrgtkky

I have a table of pairs to aggregate as follows:

+---------+----------+

| left_id | right_id |

+---------+----------+

| a       | b        |

+---------+----------+

| a       | c        |

+---------+----------+

And a table of values as so:

+----+-------+

| id | value |

+----+-------+

| a  | 1     |

+----+-------+

| a  | 2     |

+----+-------+

| a  | 3     |

+----+-------+

| b  | 1     |

+----+-------+

| b  | 4     |

+----+-------+

| b  | 5     |

+----+-------+

| c  | 1     |

+----+-------+

| c  | 2     |

+----+-------+

| c  | 3     |

+----+-------+

| c  | 4     |

+----+-------+

For each pair, I would like to calculate the length of the union, intersection and set differences (each way) comparing the values, so that the output would look like this:

+---------+----------+-------+--------------+-----------+------------+

| left_id | right_id | union | intersection | left_diff | right_diff |

+---------+----------+-------+--------------+-----------+------------+

| a       | b        | 5     | 1            | 2         | 2          |

+---------+----------+-------+--------------+-----------+------------+

| a       | c        | 4     | 3            | 0         | 1          |

+---------+----------+-------+--------------+-----------+------------+

What would be the best way to approach this using PostgreSQL?

UPDATE: here is a rextester link with data https://rextester.com/RWID9864

You need scalar sub-queries that do that.

The UNION can also be expressed by an OR which makes that query somewhat shorter to write. But for the intersection you need a query that is a bit longer.

To calculate the "diff", use the except operator:

SELECT p.*, 

       (select count(distinct value) from values where id in (p.left_id, p.right_id)) as "union",

       (select count(*)

        from (

          select v.value from values v where id = p.left_id

          intersect

          select v.value from values v where id = p.right_id

        ) t) as intersection,

       (select count(*)

        from (

          select v.value from values v where id = p.left_id

          except

          select v.value from values v where id = p.right_id

        ) t) as left_diff,

       (select count(*)

        from (

          select v.value from values v where id = p.right_id

          except

          select v.value from values v where id = p.left_id

        ) t) as right_diff

from pairs p

I don't know what causes your slowness, as I cannot see table sizes and/or explain plans. Presuming both tables are large enough to make nested loops inefficient and to not dare thinking about joining values to itself, I'd try to rewrite it free from scalar subqueries like this:

select p.*,

       coalesce(stats."union", 0) "union",

       coalesce(stats.intersection, 0) intersection,

       coalesce(stats.left_cnt - stats.intersection, 0) left_diff,

       coalesce(stats.right_cnt - stats.intersection, 0) right_diff

from pairs p

left join (

       select left_id,

              right_id,

              count(*) "union",

              count(has_left and has_right) intersection,

              count(has_left) left_cnt,

              count(has_right) right_cnt

       from (

              select p.*,

                     v."value" the_value,

                     true has_left

              from pairs p

              join "values" v on v.id = p.left_id

       ) l

       full join (

              select p.*,

                     v."value" the_value,

                     true has_right

              from pairs p

              join "values" v on v.id = p.right_id

       ) r using(left_id, right_id, the_value)

       group by left_id,

                right_id

) stats on p.left_id = stats.left_id

       and p.right_id = stats.right_id;

Each join condition here allows hash and/or merge join, so the planner will have a chance to avoid nested loops.