Postgres: make repeated subqueries more efficient?

Question

I have a Postgres 9.6 database with two tables, template and project.

template
   id               integer
   name             varchar

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

I want to get a list of all templates, with a count of the projects for each template, and a count of the deleted projects for each template, i.e. this type of output

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

I have a query like this:

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;

However, looking at the EXPLAIN, this isn't very efficient as the large project table is queried separately three times.

Is there any way to get Postgres to query and iterate over the project table just once?

Lukasz Szozda · Accepted Answer · 2019-11-05 15:44:58Z

3

The query could be rewritten as:

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

edited Nov 5, 2019 at 15:44

answered Nov 5, 2019 at 15:43

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2019-11-05 16:01:21Z

1

Sometimes, doing the aggregation before joining is more efficient then aggregating the result of a join.

SELECT t.id, t.name, 
       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

answered Nov 5, 2019 at 16:01

user330315

Collectives™ on Stack Overflow

Postgres: make repeated subqueries more efficient?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related