CODEWITHSUNDEEP
postgresql
ryekayo
ryekayo

Reputation: 2431

Optimizing Postgres count(*) query

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'));

Upvotes: 0

Views: 68

Answers (1)

stas.yaranov
stas.yaranov

Reputation: 1787

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

Upvotes: 1

Related Questions