Optimizing Postgres count(*) query

Question

I have a series of queries I need to run. They are monotonous and almost all of them use the same foreign key (trial_id). Is there a way to turn all these individual queries to one query that will post all results that I need?

select count(*) as totalstudies from study;
select count(*) as deletedstudies from study where trial_id = (select id from trial where name = 'abc');

select count(*) as portalemaillog from portalemaillog;
select count(*) as deletedemaillog from portalemaillog where trial_id = (select id from trial where name = 'abc');

select count(*) as totalsites from trialsite;
select count(*) as deletedsites from trialsite where trial_id = (select id from trial where name = 'abc');

select count(*) as totalsubjects from trialsubject;
select count(*) as deletedsubjects from trialsubject where trial_id = (select id from trial where name = 'abc');

select count(*) as totaltimepointcount from timepoint;
select count(*) as deletedtimepointcount from timepoint where id = (select id from trialversion where id = (select id from trial where name = 'abc'));

I don't know about your schema, but a JOIN is likely to perform definitely better than those subqueries and it will be more intuitive to read as well. About your original question, you can combine every subquery you like in a single one, defining an output field for each one. — Eggplant
– Eggplant, Commented Nov 10, 2015 at 15:20
Can you possibly provide an example as to using the join statement with the count(*) aggregate? I have done joins before but wasn't exactly sure on how to use it in this instance.. — ryekayo
– ryekayo, Commented Nov 10, 2015 at 15:21
You would use the aggregate function on the join just like it were a single table, something like: SELECT COUNT(*) AS deletedstudies FROM study AS s INNER JOIN trial AS t ON s.trial_id = t.id WHERE t.name = 'abc'; — Eggplant
– Eggplant, Commented Nov 10, 2015 at 15:30
And as an example for combining multiple subqueries into one here you are a starting point: SELECT (SELECT COUNT(*) FROM a) AS a_rows, (SELECT COUNT(*) FROM b) AS b_rows, (SELECT COUNT(*) FROM c) AS c_rows; — Eggplant
– Eggplant, Commented Nov 10, 2015 at 15:33

stas.yaranov · Accepted Answer · 2015-11-10 15:42:27Z

1

For the first four (as they are similar) you can write something like:

with trial as (select id from trial where name = 'abc')
select count(t.id) as totalcount, count(trial.id) as subcount, name from (
    select id, trial_id, 'studies' as name from studies
    union all
    select id, trial_id, 'portal' from portalemaillog
    union all
    select id, trial_id, 'trialsite' from trialsite
    union all
    select id, trial_id, 'trialsubject' from trialsubject
) t
left join trial on trial.id = t.trial_id
group by name;

This will return result like this:

 totalcount | subcount |   name    
------------+----------+-----------
          4 |        2 | portal
          6 |        4 | trialsite
          7 |        3 | trialsubject
         10 |        5 | studies

answered Nov 10, 2015 at 15:42

stas.yaranov

1,78711 silver badges17 bronze badges

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

1 Comment

ryekayo Over a year ago

Worked pretty well. Thanks for that

Collectives™ on Stack Overflow

Optimizing Postgres count(*) query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related