使用COALESCE优化PostgreSQL子查询

Question

想象一下：有两个表，一个是cases，另一个是case_messages。一个case可以有任意多个或零个messages。这些messages中的一个可以成为case的最终答案。我现在想返回一个列出cases的列表，并且列出一个bool列告诉我是否有最终答案。如果对于一个case而言根本没有message，则查询应该返回"false"。只有在case_messages中至少有一个相关message，isfinalanswer=true时，查询才应该返回true。我通过使用coalesce和一个子查询来实现这个功能，但它看起来非常丑陋：

SELECT cases.id, cases.title,
COALESCE((SELECT isfinalanswer FROM case_messages WHERE caseid = cases.id ORDER BY isfinalanswer DESC LIMIT 1), false) AS hasfinalanswer,
FROM cases;

我该如何改善这个问题？

- Henrik Söderlund

Answer 1

我建议使用exists：

SELECT c.id, c.title,
       (exists (SELECT 1 FROM case_messages cm WHERE cm.caseid = c.id and isfinalanswer = true)
       ) as hasfinalanswer
FROM cases c;

我不知道你是否认为这个更好看了一些。

需要补充的是，就性能而言（问题中没有提到），您需要在case_messages(caseid, isfinalanswer)上创建一个索引。通过这样的索引，这很可能是最有效的解决方案。

- Gordon Linoff

---

踩贴不是我干的。我点赞了。关于索引的好建议。我认为我们那张表上没有索引。 - Henrik Söderlund

@ypercube . . .（这些评论在我的答案上更有意义。）这两个查询应该产生相同的输出。我不知道你所说的查询“相同”是什么意思，但是使用exists时，order by是不必要的。 - Gordon Linoff

是的，我错了。我没有注意到子查询中的 ORDER BY 使用了与 SELECT 相同的列（isfinalanswer）。顺便说一下，我没有收到这个通知 - 是偶然看到的。只有当我已经在这里发表评论时，它才会通知我。 - ypercubeᵀᴹ

@ypercube ... 最后一点很有趣。我以为通过包含你的名字，你会收到通知。 - Gordon Linoff

不，只有之前的评论者和原帖作者才能（使用自动完成功能）。你可以尝试一下，会发现 ypercube 或 Henrik 可以在这里使用自动完成，但 Clodoaldo 不能。 - ypercubeᵀᴹ

我将此标记为答案，因为代码比另一个答案更简洁。我没有注意到两种选择之间有任何可察觉的性能差异。 - Henrik Söderlund

Answer 2

相关子查询（在选择列表上的查询）可能会对性能产生不良影响。请使用左连接和distinct on。

select distinct on (c.id)
    c.id, c.title,
    coalesce(cm.isfinalanswer, false) as hasfinalanswer
from
    cases c
    left join
    case_messages cm on cm.caseid = c.id
order by 1, 3 desc

在这种情况下，coalesce 可以替换为 is true。

    cm.isfinalanswer is true as hasfinalanswer

编辑：为避免distinct on所需的order by成本，请进行预聚合。

select id, title, coalesce(isfinalanswer, false) as hasfinalanswer
from
    cases c
    left join (
        select caseid as id, bool_or(isfinalanswer) as isfinalanswer
        from case_messages
        group by 1
    ) cm using (id)

- Clodoaldo Neto

---

相关子查询并不一定对性能有害。这取决于许多因素。而distinct on需要对所有数据进行排序，这也会影响性能。 - Gordon Linoff

@ypercube...原始查询在子查询中有一个order by，而不是在外部查询中。 - Gordon Linoff

@Gordon 一种不使用 distinc on 的替代方案 - Clodoaldo Neto