Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?

Am Mon, May 23, 2022 at 01:55:07PM +0200 schrieb kimaidou:
> Hi list,
>
> I have a basic need, often encountered in spatial analysis: I have a list
> of cities, parks, childcare centres, schools. I need to count the number of
> items for each city (0 if no item exists for this city)
>
> I have tested 3 different SQL queries to achieve this goal:
>
> * one with several LEFT JOINS: http://sqlfiddle.com/#!17/fe902/3
> * one with sub-queries: http://sqlfiddle.com/#!17/fe902/4
> * one with several LATERAL JOINS: http://sqlfiddle.com/#!17/fe902/6

Hello,

Cost of queries see link "View Execution Plan" in fiddle

query 1: 134.62
query 2: 8522.32
query 3: 134.62

query 1 and 3 have wrong count in result (columns nb_school,
nb_childcare, nb_park)

My try has cost of 81.83

select c.*
, coalesce(s.cnt,0) as cnt_school
, s.schools
, coalesce(cc.cnt,0) as cnt_childcare
, cc.childcares
, coalesce(p.cnt,0) as cnt_park
, p.parks
from city c
left outer join
(select fk_id_city, count(*) as cnt
,string_agg(name, ', ') AS schools
from school
group by fk_id_city) s
on s.fk_id_city = c.id
left outer join
(select fk_id_city, count(*) as cnt
,string_agg(name, ', ') AS childcares
from childcare
group by fk_id_city) cc
on cc.fk_id_city = c.id
left outer join
(select fk_id_city, count(*) as cnt
,string_agg(name, ', ') AS parks
from park
group by fk_id_city) p
on p.fk_id_city = c.id
order by c.id
;

IMHO, but without a where clause, the cost will increase with the amount
of data.

Regards,
Frank