提升查询性能

Question

我正在构建一个论坛应用程序，用户可以在其中发布消息。这些信息可以被其他人看到。

表结构(简化)：

// table: users
user_id | username | gender
---------------------------
1       | john     | m
2       | jane     | f
...

// table: posts
post_id | user_id | title
-------------------------
1       | 1       | Hello
...

// table: views
view_id | post_id | user_id | timestamp
---------------------------------------
1       | 1       | 2       | 2020-01-01 12:00:00
...

现在，我想创建一个查询来返回有关这个帖子的一些统计信息。我想获得唯一的视图(每次用户查看一个帖子，这是日志，但我只想计数所有用户一次)，我想要得到的比例男性/女性。

SELECT
    title,
    (SELECT COUNT(DISTINCT user_id) FROM views WHERE post_id = 1) AS unique_views,
    (SELECT COUNT(user_id) FROM users WHERE gender = 'm' AND user_id IN (SELECT user_id FROM views WHERE post_id = 1) AS male_views,
    (SELECT COUNT(user_id) FROM users WHERE gender = 'f' AND user_id IN (SELECT user_id FROM views WHERE post_id = 1) AS female_views
FROM
    posts
WHERE
    post_id = 1

该查询工作正常，但它是一个包含5个子查询的查询。我还没有太多的数据可供测试，但我担心，如果我有+100万用户、+100万帖子和+1000万的浏览量，性能就会下降。

另一种方法是将查询完全拆分为多个查询:一个查询针对的是全部独特的视图，一个用于性别观点(具有不同的)，但之后仍将是6个查询。

我使用postgresql，我有一个关于users.user_id、users.gender、posts.post_id、views.view_id、views.post_id的索引。

问题：是否有另一种方法(例如，使用JOIN)来执行此查询，并且当数据库容量增加时性能会更好？

Answer 1

您可以加入并执行条件聚合，而不是嵌套子查询：

select 
    p.title,
    count(distinct u.user_id) unique_views,
    count(u.user_id) filter(where u.gender = 'm') male_views,
    count(u.user_id) filter(where u.gender = 'f') female_views
from views v
inner join users u on u.user_id = v.user_id
inner join posts p on p.post_id = v.post_id
where p.post_id = 1
group by p.post_id, p.title