postgreSQL计数和过滤器查询优化

Question

给定一个包含userId和articleId列的数据库表article_bookmarks，如果用户为一篇文章添加书签，则创建一个具有各自id的表项(userId,articleId)

我想检索由articleId给出的将特定文章加为书签的用户总数，以及该用户(由userId给出)是否也将该文章加为书签的信息。

我的SQL查询现在看起来像这样:

SELECT COUNT(ar.userId) AS bookmark_count,
EXISTS(SELECT 1 FROM article_bookmarks WHERE articleId=:articleId AND userId=:userId) AS user_bookmarked
FROM article_bookmarks ar
WHERE ar.articleId=:articleId

我的印象是这个查询不是最优的，因为读取基本上相同的userId数据两次似乎效率低下且冗余:一次在"计数"中聚合，另一次由单个userId通过子选择进行过滤。

是否有一种方法来优化或简化给定的SQL查询(即像SELECT COUNT(ar.userId) AS bookmark_count, EXISTS(:userId IN ar.userId) AS user_bookmarked[…]在有效的SQL)?

Answer 1

可以使用条件聚合。这里，表article_bookmarks只被扫描一次。

SELECT 
COUNT(ar.userId) AS bookmark_count,
MAX(CASE WHEN userId=:userId THEN 1 ELSE 0 END) AS user_bookmarked
FROM article_bookmarks ar
WHERE ar.articleId=:articleId

为了避免扫描整个表，需要在(articleId, userId)或(articleId) include (userId)上建立索引。