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?

Answer 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;