PostgreSQL用户积分统计视图开发 优化LATERAL关联慢查询

• 最初的直接多表连接聚合写法结果错误的核心：prognosis_scores、question_scores与users均为一对多关系，直接在三表层面连接会为单个用户生成笛卡尔积行（例如用户2在prognosis表有2条记录、在question表有1条记录，连接后会生成2*1=2行重复的question侧数据），最终求和时会导致积分被重复累加，结果失真。
• 改用LEFT JOIN LATERAL的写法结果正确但性能极差的核心：对users表的每一行触发两次独立的相关子查询扫描，在1000个用户、3万条prognosis_scores记录的场景下，会触发2000次逐行匹配的子查询执行，完全没有利用批量聚合的效率优势，因此耗时从400ms飙升到16s。

无需使用LATERAL关联，通过两阶段聚合即可同时兼顾结果准确性和执行效率：先对两个分数表分别按user_id做单表预聚合，让每个用户在每个分数表中仅保留1行聚合结果，再将聚合后的临时结果与users表做左连接。
该方案的扫描次数和最初的错误写法完全一致，仅多了一次分组聚合的CPU开销，性能可以回到接近最初的400ms水平，且从根源上避免了笛卡尔积导致的重复计算问题。

对应的视图定义SQL为：

CREATE VIEW score_calculations AS
SELECT
  u.id AS user_id,
  u.name,
  u.company_id,
  u.team_id,
  u.email_verified,
  u.banned,
  COALESCE(ps.pts_won, 0) + COALESCE(qs.pts_won, 0) AS pts_won,
  COALESCE(ps.good_gap, 0) AS good_gap,
  COALESCE(ps.good_score, 0) AS good_score,
  COALESCE(ps.good_winner, 0) AS good_winner
FROM users u
-- 对prognosis_scores做单表预聚合，每个用户仅输出1行
LEFT JOIN (
  SELECT
    user_id,
    SUM(pts_won) AS pts_won,
    SUM(good_gap) AS good_gap,
    SUM(good_score) AS good_score,
    SUM(isGoodPrognosis::INT) AS good_winner
  FROM prognosis_scores
  GROUP BY user_id
) ps ON ps.user_id = u.id
-- 对question_scores做单表预聚合，每个用户仅输出1行
LEFT JOIN (
  SELECT
    user_id,
    SUM(pts_won) AS pts_won
  FROM question_scores
  GROUP BY user_id
) qs ON qs.user_id = u.id;

• 结果准确性：对照示例数据验证，user1在prognosis表的预聚合结果pts_won为1，结合question表的积分值，最终总积分2；user2在prognosis表的预聚合结果为pts_won=3+0=3、good_gap=1、good_score=1、good_winner=1，在question表的预聚合结果为pts_won=10，最终总积分为3+10=13，和目标值完全匹配。
• 性能表现：在你的测试环境下，该写法的执行耗时可以回到400ms级别，远快于逐行LATERAL关联的16s水平。
• 后续扩容建议：随着数据量增长，只需在两个分数表的user_id字段上建立B-tree索引，即可进一步压缩聚合阶段的扫描耗时。

内容的提问来源于stack exchange，提问作者B.Samuel