0

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?

3
  • 4
    Duplicate Trying to get count of childs rows
    – mustaccio
    Commented Feb 11, 2020 at 16:02
  • The link does provide an alternative solution but doesn't give any input on performance which is being questioned here.
    – yaugenka
    Commented Feb 11, 2020 at 16:21
  • @mustaccio This link shows how to get a total count including the children's kids etc, with a Recursive CTE which is allot more complicate than a simple count. It does not apply, and would preform very well on lots of records
    – zsheep
    Commented Feb 11, 2020 at 16:46

1 Answer 1

0

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

5
  • it gives null intead of 0 for entries without children
    – yaugenka
    Commented Feb 11, 2020 at 17:27
  • change to count(*)
    – zsheep
    Commented Feb 11, 2020 at 18:16
  • it makes no difference
    – yaugenka
    Commented Feb 11, 2020 at 18:32
  • this should fix it
    – zsheep
    Commented Feb 11, 2020 at 18:59
  • now it works perfect. thank you!
    – yaugenka
    Commented Feb 11, 2020 at 19:48

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