1

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.

3 Answers 3

1

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
8
  • The second example is impractical in the real life scenario where I need to retrieve the whole record from the parent table, not just one or two columns. But the first one looks interesting. Is there any cases when it and the original query with join are not equivalent? May 15, 2020 at 12:59
  • it's equivalent. Also you can remove "p.name" from the second query to use it as common table expression and join parent to this CTE (using inner join on CTE.id = parent.id) to add rest of the columns. May 15, 2020 at 13:20
  • Thanks, that answers the "how". I am also curious as to the "why" the original unfiltered query is so ineffective. May 15, 2020 at 14:05
  • you could see it from output of explain command. I suppose server runs your subquery separately (for the whole table) and then join the results with parent table. I don't know why Postgre can't "push" conditions to this subquery but you can force it to do it using lateral join as described in @a_horse_with_no_name answer (I suppose it should have similar execution plan to my first query) May 15, 2020 at 14:28
  • "The second example is impractical in the real life scenario where I need to retrieve the whole record from the parent table" select p.*, count(c.pid) as cnt from ....
    – jjanes
    May 15, 2020 at 18:21
1

Did you try a lateral join?

select parent.id, parent.name, q.cnt 
from parent
  left join lateral (
    select count(*) cnt 
    from child ch
    where ch.pid = parent.id
  ) q on true
where name like '%xyz%'
4
  • I am limited by what Slick can do and it doesn't do lateral unfortunately. May 15, 2020 at 13:56
  • 1
    @NebehrGudahtt: the joys of obfuscation layers...
    – user1822
    May 15, 2020 at 13:57
  • Well it has its pros as well... but for some reason I've kept stumbling upon its cons lately... May 15, 2020 at 13:59
  • 1
    @NebehrGudahtt: The reason being that there are so many that pop up once you do anything non-trivial. May 15, 2020 at 23:31
0

Why not simple

SELECT parent.id, 
       parent.name, 
       COUNT(child.pid) cnt 
FROM parent
LEFT JOIN child ON parent.id = child.pid
WHERE parent.name LIKE '%xyz%'
GROUP BY parent.id, 
         parent.name

? Anycase fullscan by parent (due to LIKE '%...%') needed...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.