PostgreSQL multiple counts query performance

Question

I'm writing a query in postgres to select the posts with more comments.

The following works but I'm wondering if it could become a performance problem with many posts.

Query:

SELECT 
    po.*, 
    (SELECT count(id) FROM comments WHERE post_id = po.id) AS comments_count
    FROM posts AS po
    ORDER BY comments_count DESC
    LIMIT 10;

Result:

id   title  body  comments_count
2    Foo    Bar   5
1    Click  Bait  4

Is there something I can do to improve this query performance or is it ok?

Gurwinder Singh · Accepted Answer · 2017-04-02 17:23:50Z

1

You can use join instead of correlated subquery. Assuming id is PK in posts table:

select p.*,
    count(c.id) as comments_count
from posts p join comments c on p.id = c.post_id
group by p.id
order by comments_count desc limit 10;

or

select p.*,
    c.comments_count
from posts p
join (
    select post_id,
        count(id) as comments_count
    from comments
    order by comments_count desc LIMIT 10
    ) c on p.id = c.post_id;

edited Apr 2, 2017 at 17:23

answered Apr 2, 2017 at 17:11

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

4 Comments

pietrovismara Over a year ago

In the second example i get this error: argument of WHERE must be type boolean, not type integer

Gurwinder Singh Over a year ago

@pietrovismara - That was a typo. Updated the answer. please try now

pietrovismara Over a year ago

Anyway, comparing the EXPLAIN result for your first query and mine, yours looks much better. Is this because using join the count is done only on matching comments instead of all?

Gurwinder Singh Over a year ago

It's because of the correlated subquery. It's complexity is O(n^2).

Collectives™ on Stack Overflow

PostgreSQL multiple counts query performance

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related