Know the count of foreign key in each of its table - Postgres

Viewed 133

I hope you all are in good health. I want to display the number of times a foreign key is present in its child table. For instance, I'm having a company table with a primary key 'cin' which links to five other tables and I want to know how many times each 'cin' is present in other tables.

I have made a query that works fine with small amount of data but when I run it on a database of 17GB, it takes forever (and is still running right now) where 'ov' is the name of the data base and 'opi', 'zii', 'li', 'kvi' and 'kra' are the other five tables that cin is linked as foreign key.

Select c.cin, name, br_section, address_line,
(Select count(cin) from ov.opi
where cin = c.cin)  as opi_count,
(Select count(cin) from ov.zii
where cin = c.cin) as zii_count,
(Select count(cin) from li
where cin = c.cin) as li_count,
(Select count(cin) from ov.kvi
where cin = c.cin) as kvi_count,
(Select count(cin) from ov.krators
where cin = c.cin) as kra_count
from ov.company as c 
group by c.cin

How can I optimize it for fast results? The output expected is somewhat like this

cin   opi_count   zii_count   li_count   kvi_count   kri_count
1        56         NULL         2         NULL         9
2       NULL        140         NULL        10          23
3        2          90           10        NULL         2
4        34         NULL         89         3          NULL
5       NULL        34           2          9          NULL
.        .           .           .          .           .
.        .           .           .          .           .
.        .           .           .          .           .

Thank you for your tme, Cheers!

1 Answers

Your code is optimized. The only improvement would be an index on cin in each of the referencing tables. So an index on ov.opi(cin), ov.zii(cin), and so on.

Related