Optimal query pattern to count related entries in PostgreSQL Assume I have the following table schema and data

create table categories(
    id integer primary key,
    parent_id integer
);

insert into categories
values
    (1,0),
    (2,0),
    (3,1),
    (4,1);


I need to count the number of children for each entry, i.e. the result should be

 id | parent_id | count
----+-----------+-------
  1 |         0 |     2
  2 |         0 |     0
  3 |         1 |     0
  4 |         1 |     0


The following query produces the correct result but I'm worried about the performance. I guess that it loops through the table as many time as there are rows in it.

select *, (select count(*) from categories b where a.id=b.parent_id) from categories a


It is possible to optimize it?
nested selects  can be slower or be faster depends on how many records are being processed. 

select *, (select count(*) from categories b where a.id=b.parent_id) 
   from categories a


This can be faster or have the same speed if only a few records or with where clause filters this down 

select *, (select count(*) from categories b where a.id=b.parent_id) 
   from categories a  where a.id = 5


A faster solution for all the records would be something like this  

Select categories.*, coalesce(counted, 0) from categories 
   left join ( select parent_id, count(*) as counted from categories group by parent_id) child
   on categories.id = child.parent_id


with this query  planner has an easier time figuring out what to do.  Nested selects the planner has harder time with as it has to treat them as VOLATILE.  meaning it has to keep calling the nested query for each record