斜率算法，优化查询

Question

我尝试实现slope one算法。我有系统在线咨询和专家可以咨询用户。专家是用户与type=2。我需要使功能“谁咨询了这个专家也咨询了”。对于expert_id1和expert_id2，子查询返回序列为0(未咨询)、1(咨询)的数组，但此序列包含超过100k的值，并且此查询执行速度非常慢。请提出优化此查询的任何想法.

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 consulted
           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

Answer 1

虽然explain analyse输出将非常有用，但在此查询中有几个危险信号。FWIW我倾向于避免列列表中的子选择，这是可以避免的，因为这会降低可读性。

但是，在这种情况下，您的子计划通过可能很大的表创建了不必要的联接。

要做的第一件事是提取这些子选择。它们使您的查询更难阅读和跟踪，并且它们添加了大量重复的联接，这意味着对可能很大的表进行额外的扫描。例如，您可以将CASE放在array_agg内部或类似的地方。

如果这不起作用，请发布解释分析结果，我们可以从那里查看索引。