如何提高子查询性能？

Question

我有2张桌子，users和games。用户可以有很多游戏。我需要所有有users人数的人games，以及他们的games_hosted人数（games有专栏finished的）true。

附言：我需要将所有数据加载到管理表中。由于游戏太多。我决定对数据进行分页和限制。但是，甚至限制以下查询也需要花费相同的时间。如何更好地查询？

 select 
  distinct "uid", 
  "username", 
  (
    select 
      count(id) 
    from 
      games 
    where 
      state = 'finished' 
      and user_uid = users.uid
  ) as games_hosted 
from 
  "users" 
  inner join "games" on "games"."user_uid" = "users"."uid" 
where 
  "games"."state" in ('published', 'finished') 
  and "username" < 'HariShankar' 
order by 
  "username" desc 
limit 
  10

Answer 1

您可以在下面尝试使用case when表达式

select "uid", "username", count(case when state = 'finished' then id end) as games_hosted 
from "users" inner join "games" 
on "games"."user_uid" = "users"."uid" 
where "games"."state" in ('published', 'finished') and "username" < 'HariShankar' 
group by "uid", "username"
order by "username" desc 
limit 10