postgresql - Postgres : make repeated subqueries more efficient?

Question

我有一个 Postgres 9.6 数据库，其中包含两个表、模板和项目。

template
   id               integer
   name             varchar

project
   id               integer
   name             varchar
   template_id      integer (foreign key)
   is_deleted       boolean
   is_listed        boolean

我想获取所有模板的列表，每个模板的项目数，以及每个模板的已删除项目数，即这种类型的输出

 id,name,num_projects,num_deleted,num_listed
 1,"circle",19,2,7
 2,"square",10,0,8

我有这样的查询:

select id, name, 
(select count(*) from project where template_id=template.id)
  as num_projects,
(select count(*) from project where template_id=template.id and is_deleted)
  as num_deleted,
(select count(*) from project where template_id=template.id and is_listed)
  as num_listed
from template;

但是，查看 EXPLAIN，这不是很有效，因为大型项目表被单独查询了​​ 3 次。

有没有办法让 Postgres 只查询和迭代项目表一次？

Answer 1

查询可以重写为:

SELECT t.id, t.name, 
  COUNT(p.template_id) as num_projects,
  COUNT(p.template_id) FILTER(WHERE p.is_deleted) as num_deleted,
  COUNT(p.template_id) FILTER(WHERE p.is_listed) as num_listed
FROM template t
LEFT JOIN project p
  ON p.template_id=t.id
GROUP BY t.id, t.name