前端之家

  1. 首页
  2. 问答

Postgres：提高重复子查询的效率？

• 问答

我有一个包含两个表（模板和项目）的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,8

我有这样的查询：

select id,(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一次查询和遍历项目表吗？

clorchid 回答：Postgres：提高重复子查询的效率？

查询可以重写为：

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
,

有时候，在进行联接之前进行聚合要比对联接结果进行聚合更有效。

SELECT t.id,coalesce(p.num_projects,0) as num_projects,coalesce(p.num_deleted,0) as num_deleted,coalesce(p.num_listed,0) as num_listed
FROM template t
  LEFT JOIN (
    SELECT template_id,count(*) as num_projects
           count(*) filter (where p.is_deleted) as num_deleted,count(*) filter (where p.is_listed) as num_listed
    FROM project 
    GROUP BY template_id
  ) p ON p.template_id = t.id
postgresql
本文链接：https://www.f2er.com/3158322.html

大家都在问