Is it possible to improve the performance of this subquery?

For my application I have a topic table and a vote table. The vote options are -1, 0, and 1. I’m trying to find the amount of times each topic was voted -1 and 1.

So to find out, I am doing many subqueries, each of which uses the algorithm of find total number of -1 or 1 votes and then divide by the total number of votes.

Unfortunately, what I came up with is very slow. In part because I am calculating the count twice, but I’m not sure if it’s possible to reference it from the outer query.

Can the performance of this be improved?

SELECT title,
  (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id AND vote_choice = -1) 
/ (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id) as lp,
  (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id AND vote_choice = 1) 
/ (SELECT COUNT(vote)::float8 FROM vote WHERE topic_id = v1.topic_id) as rp
FROM topic
JOIN vote v1 ON topic.id = v1.topic_id 
GROUP BY v1.topic_id, topic.title;

Answer

I would use FILTER instead of correlated subqueries:

SELECT 
    title,
    1.0 * COUNT(*) FILTER(WHERE vote_choice = -1) / COUNT(*) as lp,
    1.0 * COUNT(*) FILTER(WHERE vote_choice = 1)  / COUNT(*) as rp
FROM topic
JOIN vote v1 ON topic.id = v1.topic_id 
GROUP BY v1.topic_id, topic.title;

Advertisement

Answer