Postgresql - Optimize query with calculated columns-CodePudding

I have to create a view in postgresql with calculated columns, but my query is taking a very very long time to run.

I have a table called surveys which contains one entry for every survey.

Sno= Serial number of person who was called

survey round = surveys are conducted for multiple periods called rounds, upon which we need to group and calculate metrics

call_status=status of survey calls

I want to get stats per round like how many users were called i.e. unique sno, how many calls were completed, count per different call status values

My current query is =

select distinct  sc.survey_round, 
(select  count(distinct sno) FROM "Suvita".survey_call_details sc1 where sc.survey_round =sc1.survey_round  ) as "Total respondents call",
(select count(*) fROM "Suvita".survey_call_details sc2 where sc.survey_round =sc2.survey_round and sc2.iscomplete = 'Yes') as "Survey Completed",
(select count(*) FROM "Suvita".survey_call_details sc3 where sc.survey_round =sc3.survey_round)  as "Calls Attempted",
(select count(*) FROM "Suvita".survey_call_details sc4 where sc.survey_round =sc4.survey_round and sc4.call_status = 'Picked Up')  as "Calls Answered"  
FROM "Suvita".survey_call_details sc

Please suggest a better way to write these queries in postgresql.

Edit: I have got the answer. Based on Alexey's response, I re-qrote the queries using count and sum and they worked really fast!!

SELECT sc.survey_round, 
    COUNT(distinct sc.sno) as "Total respondents call",
    COUNT(*) as "Calls Attempted",
    sum(CASE WHEN sc.call_status = 'Picked Up' THEN 1 ELSE 0 END) as "Calls Answered",
    sum(CASE WHEN sc.iscomplete = 'Yes' THEN 1 ELSE 0 END) as "Survey Completed",    
    sum(CASE WHEN sc.nominate = 'Yes' THEN 1 ELSE 0 END) as "Agreed to Nominate"
FROM "Suvita".survey_call_details sc
group by sc.survey_round  order by sc.survey_round desc

CodePudding user response：

First, you do not need subqueries to count something in one table, just use CASE statement with conditions in COUNT.

Second, COUNT(DISTINCT sno) can be slower then COUNT(sno). To get distinct sno values you can use JOIN LATERAL, that would be used as a subquery.

Then your query become this

SELECT 
    sc.survey_round,
    COUNT(sc1.sno) as "Total respondents call",
    COUNT(CASE WHEN sc.iscomplete = 'Yes' THEN 1 ELSE 0 END) as "Survey Completed",
    COUNT(*) as "Calls Attempted",
    COUNT(CASE WHEN sc.call_status = 'Picked Up' THEN 1 ELSE 0 END) as "Calls Answered"
FROM "Suvita".survey_call_details sc
JOIN LATERAL (
    SELECT DISTINCT sno 
    FROM "Suvita".survey_call_details 
    WHERE survey_round = sc.survey_round
) sc1 ON TRUE
GROUP BY sc.survey_round

Atlast, you should have indexes on survey_round and sno columns. With live data maybe complex index on survey_round sno would perform better.