postgresql - Slope One 算法，优化查询

Question

我尝试执行 slope one算法。我有系统在线咨询，专家可以咨询用户。 Expert 是 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 analyze 输出真的很有帮助，但此查询中有几个危险信号。 FWIW 我倾向于避免在列列表中进行子选择，因为这会降低可读性。

但是在这种情况下，您的子计划会通过潜在的大表创建不必要的连接。

首先要做的是分解出那些子选择。它们使您的查询更难阅读和遵循，并且它们添加了许多重复连接，这意味着对可能较大的表进行额外扫描。例如，您可以将 CASE 放在 array_agg 之类的内容中。

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