How to optimize SQL Join + Expresssion?

Question

PostgreSQL

There is a view:

create view posts_view as
select p.*,
         case when pl.likes is null then 0 else pl.likes end
         -
         case when pdl.dislikes is null then 0 else pdl.dislikes end
         as rating
  from posts as p
  left join (select
             count(*) as likes,
             post_id
             from post_rate
             where rate = 1
             group by post_id) as pl
             on p.id = pl.post_id
  left join (select
             count(*) as dislikes,
             post_id
             from post_rate
             where rate = -1
             group by post_id) as pdl
             on p.id = pdl.post_id;

There is a query: Order by in the query calculates the rating for the entire table, and then does the sorting. Where to start optimization? Is it possible to create an index on rating so that the rating is stored in a computed + sorted form and effectively recalculated if post_rate changes?
select * from posts_view order by rating limit 5;

Answer 1

Everything is bad:
1.

case when pl.likes is null then 0 else pl.likes end

it's more convenient to write it in a different way:
coalesce(pl.likes)
2. Why do you group the rating by posts, if you then filter it when joining?!
3. Why do you do such joins with subqueries at all, when you can do a join with a ratings table once, group by post, and aggregate by summation by rating?

select pr.id, sum(pr.rate) as rating
  from posts as p
      left join post_rate pr on pr.post_id = p.id
  group by p.id

If you need to include something else from the posts table in the select, then either bring it under grouping and include it in the select, or enter the entire query I have given in with and join it again with the posts table by id with getting the necessary