Slope One algorithm, optimize query

I try to implement slope one algorithm. I have system online consultation and expert can consult user. Expert is user with type=2. And I need to make functionality "who consulted with this expert also consulted with". Subqueries return array with sequence of 0(not consulted),1(consulted) for expert_id1 and expert_id2, but this sequence consists of over 100k values and this query execute very slow. Please any ideas to optimize this query.

SELECT  e1.id as expert_id1, e2.id as expert_id2,
      (
         SELECT array_accum(c.consulted) FROM (
           SELECT CASE WHEN (c.id is null) THEN 0 ELSE 1 END as consulted
           FROM co_user u
           CROSS JOIN user e
           LEFT JOIN consultation c ON e.id = c.expert_id and c.user_id = u.id
           WHERE e.type = 2 AND e.id = e1.id) as c
       ) as expert_id1_consulted,
      (
         SELECT array_accum(c.consulted) FROM (
           SELECT CASE WHEN (c.id is null) THEN 0 ELSE 1 END as consul开发者_C百科ted
           FROM user u
           CROSS JOIN user e
           LEFT JOIN consultation c ON e.id = c.expert_id and c.user_id = u.id
           WHERE e.type = 2 AND e.id = e2.id) as c
       ) as expert_id2_consulted
FROM user e1
CROSS JOIN user e2
WHERE e1.type = 2 AND
      e2.type = 2 AND
      e2.id > e1.id
ORDER BY e1.id

While explain analyse output would be really helpful there are a couple of red flags in this query. FWIW I tend to avoid subselects in the column list to the extent this can be avoided because these reduce readability.

However in this case your subplans create unnecessary joins through potentially large tables.

The first thing to do is factor out those subselects. They make your query harder to read and follow, and they add a number of duplicate joins that mean extra scans over possibly large tables. You can, for example, put CASE inside array_agg or the like.

If that doesn't work, please post explain analyse results and we can look at indexes from there.