Pre-filtering on right table in a LEFT JOIN I have the following schema/query where I basically need to filter parent table by some criteria and then collect aggregated data from its child table(s):

create table parent (id integer primary key, name text not null);
create table child (id integer primary key, pid integer not null references parent(id));

select parent.id, parent.name, q.cnt from parent
left join (
  select pid, count(*) cnt from child
-- where pid in (select id from parent where name like '%xyz%')
  group by pid
) q on parent.id = q.pid
where name like '%xyz%'


(Actual schema/query is more convoluted but the gist is the same.)

The issue is, unless I uncomment the WHERE clause on the right table the query takes significantly more time to execute. Having to specify the same filter in two places doesn't feel right. Am I doing something wrong? Should I transform the query somehow? Why is the inner filter even necessary; shouldn't it automatically discard records from the right table that cannot be joined? The database is PostgreSQL by the way.
For example you can do it this way:

select p.id, p.name, 
COALESCE((select count(*) from child where p.id = pid), 0) AS cnt 
from parent AS p
where name like '%xyz%'


or this:

select p.id, p.name, 
count(c.pid) AS cnt 
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id, p.name


if you need more columns from parent:

select p1.*, CTE.cnt
from parent p1
join (select p.id
count(c.pid) AS cnt 
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id) as CTE on p1.id = CTE.id