public inbox for [email protected]  

help / color / mirror / Atom feed

From: kimaidou <[email protected]>
To: Frank Streitzig <[email protected]>
Cc: [email protected]
Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?
Date: Mon, 23 May 2022 16:20:36 +0200
Message-ID: <CAMKXKO4UoPKACXYVWMxDAn-ZukRVD9S3TV52P93QOYHzC6AqbQ@mail.gmail.com> (raw)
In-Reply-To: <CAMKXKO4xgawFHoVpz6hjeV-uGNf2W0NU90PkVBuCxJDLDUVmWg@mail.gmail.com>
References: <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
	<YouIuUVD9ivC03pj@frastr-dev>
	<CAMKXKO4xgawFHoVpz6hjeV-uGNf2W0NU90PkVBuCxJDLDUVmWg@mail.gmail.com>

Hi Frank,

Thanks for your answer !

It seems it would perform better to aggregate as soon as possible, like you
illustrated in your example.
I will rewrite the query with "WITH" clauses to improve readability.

Thanks also for the Coalesce idea. It is better to see 0 instead of NULL.

Michaël

Le lun. 23 mai 2022 à 16:15, kimaidou <[email protected]> a écrit :

> So you
>
> Le lun. 23 mai 2022 à 15:14, Frank Streitzig <[email protected]> a
> écrit :
>
>> 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
>>
>>

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?
  In-Reply-To: <CAMKXKO4UoPKACXYVWMxDAn-ZukRVD9S3TV52P93QOYHzC6AqbQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox