CODEWITHSUNDEEP
sqlperformancepostgresqldatabase-performance
David Chouinard
David Chouinard

Reputation: 6836

Improving performance of join on subselect

Consider this table (comments):

         id | post_id |      text
------------+---------|----------------
      79507 |     12  | Lorem Ipsum
      79544 |     12  | Foo, bar
      79545 |     14  | Interesting...

And this aggregate query:

SELECT comment_id, SUM(vote) AS votes
FROM votes 
GROUP BY comment_id;

 comment_id | votes 
------------+-------
      79507 |    3
      79544 |    4
      79545 |    1

I'm looking to joining the comments table and the aggregate query, but only interested in a very small subset of the data (only a particular post_id). This naive approach uses a subquery to correctly return the result for post_id 12:

SELECT comment_id, votes, text FROM comments c LEFT JOIN
  (SELECT comment_id, SUM(votes) AS vote
   FROM votes 
   GROUP BY comment_id) AS v
ON c.id = v.comment_id 
WHERE c.post_id = 12;

 comment_id | votes |      text
------------+-------|----------------
      79507 |    3  | Lorem Ipsum
      79544 |    4  | Foo, bar

However, this is highly inefficient, since we are computing the inner subquery on the entire table, but we are only interested in a very small subset of it (the votes table in this application is huge).

Intuitively, it seems we should be filtering the inner query and there we're missing a WHERE comment_id IN (...) in the subselect. However, we don't know which comment_ids we will need at that stage in the computation. Another subselect inside the subselect could be used to retrieve the appropriate comment_ids, but that seems very clumsy.

I'm inexperienced in SQL and not sure if there exists a cleaner solution. Perhaps the subselect approach is the wrong one altogether.

Upvotes: 2

Views: 96

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Not sure I understood well, don't you need something like that ?

SELECT c.id as comment_id, SUM (v.vote) as votes, c.text
FROM comments c
LEFT JOIN votes v ON c.id = v.comment_id
WHERE c.post_id = 12
GROUP BY c.id, c.text

Upvotes: 3

Related Questions