public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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:22:48 +0200
Message-ID: <CAMKXKO56Kc9Y32GEscw4F=mjkB9N3+aO8gVX1w9HmNC9T=1OrA@mail.gmail.com> (raw)
In-Reply-To: <CAMKXKO4UoPKACXYVWMxDAn-ZukRVD9S3TV52P93QOYHzC6AqbQ@mail.gmail.com>
References: <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
<YouIuUVD9ivC03pj@frastr-dev>
<CAMKXKO4xgawFHoVpz6hjeV-uGNf2W0NU90PkVBuCxJDLDUVmWg@mail.gmail.com>
<CAMKXKO4UoPKACXYVWMxDAn-ZukRVD9S3TV52P93QOYHzC6AqbQ@mail.gmail.com>
By the way, I was in fact aware of the duplicate count for the "nb_schools"
and other fields, this is why I used a count(DISTINCT ) to have a correct
count in the first example. I kept the nb_schools and 2 other fields to
illustrate the cost of using DISTINCT in the aggregate functions.
Le lun. 23 mai 2022 à 16:20, kimaidou <[email protected]> a écrit :
> 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: <CAMKXKO56Kc9Y32GEscw4F=mjkB9N3+aO8gVX1w9HmNC9T=1OrA@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