Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nt8vW-00070B-NH for pgsql-sql@arkaria.postgresql.org; Mon, 23 May 2022 14:20:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nt8vV-0007Kl-EJ for pgsql-sql@arkaria.postgresql.org; Mon, 23 May 2022 14:20:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nt8vV-0007Kb-2P for pgsql-sql@lists.postgresql.org; Mon, 23 May 2022 14:20:53 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nt8vR-000608-HW for pgsql-sql@lists.postgresql.org; Mon, 23 May 2022 14:20:52 +0000 Received: by mail-ej1-x62b.google.com with SMTP id rq11so7047047ejc.4 for ; Mon, 23 May 2022 07:20:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=PfhcvJoJvgJQfXEcB64UARpCTbjHGEhKpXDsBG77tfY=; b=KeDia2PsxOGeyTK8wxLuEN/LsKlscNHSIwb9opXu1qvNR1H4sxKYYVE0TGLON/P23d 58RLW3zO5DjJXrYNomD/CoGAuHqkErZqY/OFftRtCpJOMLtb5hRrP/Tyzgbq195qxnH6 ZsUXNKVNlYGGAydbnfOQy7wYO0G4vwFuNqvXzf5RzYQEfcxce3ahrIley64OZIIp8/gB KoQjiRJH7FT0H2SxbAvcfNdAI3pm5Gr+HmF4DNHJ4tIkxsQaug625Iy87ZDdhAeEEfas Np1jyCHOBceS2QUsxQzT+XBS4TelKZ1Ju8C7+Zsp+5TQvVC8KS5+ICCBoj37OXccklci 0k7g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=PfhcvJoJvgJQfXEcB64UARpCTbjHGEhKpXDsBG77tfY=; b=DexRvecUfdv2AVIOj+cKz2AkAqS/NEJ7/08L8+PRsq1PpFqE0MN1RwnhyzejCBls6j RulCpOBHOUzYzG6L/LrVJHU86MgWdx6m2n4bbInvJOnJPENuZ82tmZCtss5ZnR7pl1v8 CI89MYyrikrhIRS4M1EYSJ8K36LJHu3pDykT+xvS3OZnOY53IlhNZFGuSo0onLMObm+J c4kkLKtgICDyfrX/nlt8XyBBEx4Sc8y1+aua7+tESb0R9DkU9jTKjwBizC4QcrGeK7CZ AnqjwZTQLviSzgOXUvz7FkKib2EcXGUpKAw8XWDHC09+138C6sGv5AQZpuSQXS8Gav3z 1P7w== X-Gm-Message-State: AOAM53000Sln+9dzJ6QfRnnUPJSqNx4j7KHtC81bRn8JgbxjKirLbOtw /eD4RNgmey3RguxJMLLC1CVh51DUy+oI5rvBpnJ/mnMb X-Google-Smtp-Source: ABdhPJyEa1g6XmRP3b1M8nXpmg/w6gUm3qqH+WAiGTMo5d+MAaxuy/BTp5DwNSRi2Ov6+jATCTcM64MKezBCviWn7SE= X-Received: by 2002:a17:907:3f98:b0:6fe:e28c:472c with SMTP id hr24-20020a1709073f9800b006fee28c472cmr4752967ejc.519.1653315648287; Mon, 23 May 2022 07:20:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: kimaidou Date: Mon, 23 May 2022 16:20:36 +0200 Message-ID: Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ? To: Frank Streitzig Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000eaf64305dfae8bad" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eaf64305dfae8bad Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=C3=ABl Le lun. 23 mai 2022 =C3=A0 16:15, kimaidou a =C3=A9cri= t : > So you > > Le lun. 23 mai 2022 =C3=A0 15:14, Frank Streitzig a > =C3=A9crit : > >> 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 =3D 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 =3D 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 =3D c.id >> order by c.id >> ; >> >> IMHO, but without a where clause, the cost will increase with the amount >> of data. >> >> Regards, >> Frank >> >> --000000000000eaf64305dfae8bad Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Frank,

Thanks for your an= swer !

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

Thanks also for the Coalesce idea. It is better t= o see 0 instead of NULL.

Micha=C3=ABl

Le= =C2=A0lun. 23 mai 2022 =C3=A0=C2=A016:15, kimaidou <kimaidou@gmail.com> a =C3=A9crit=C2=A0:
<= blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l= eft:1px solid rgb(204,204,204);padding-left:1ex">
So you

Le=C2=A0lun. 23 mai 2022 =C3=A0=C2=A015:14, Frank Streitzig <fstreitzig@gmx.net> a = =C3=A9crit=C2=A0:
http://sqlfiddle.com/#!17/fe902= /3
> * one with sub-queries: http://sqlfiddle.com/#!17/fe902/4<= br> > * one with several LATERAL JOINS: http://sqlfiddle.com/#!17/fe= 902/6

Hello,

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

query 1:=C2=A0 134.62
query 2: 8522.32
query 3:=C2=A0 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=C2=A0 c.*
=C2=A0 =C2=A0 =C2=A0 =C2=A0 , coalesce(s.cnt,0) as cnt_school
=C2=A0 =C2=A0 =C2=A0 =C2=A0 , s.schools
=C2=A0 =C2=A0 =C2=A0 =C2=A0 , coalesce(cc.cnt,0) as cnt_childcare
=C2=A0 =C2=A0 =C2=A0 =C2=A0 , cc.childcares
=C2=A0 =C2=A0 =C2=A0 =C2=A0 , coalesce(p.cnt,0) as cnt_park
=C2=A0 =C2=A0 =C2=A0 =C2=A0 , p.parks
=C2=A0 from city c
=C2=A0 =C2=A0 left outer join
=C2=A0 =C2=A0 =C2=A0 =C2=A0(select fk_id_city, count(*) as cnt
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,string_agg(name, &#= 39;, ') AS schools
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from school
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0group by fk_id_city) s
=C2=A0 =C2=A0 =C2=A0 on s.fk_id_city =3D c.id
=C2=A0 =C2=A0 left outer join
=C2=A0 =C2=A0 =C2=A0 (select fk_id_city, count(*) as cnt
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,string_agg(name, &#= 39;, ') AS childcares
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from childcare
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0group by fk_id_city) cc
=C2=A0 =C2=A0 =C2=A0 on cc.fk_id_city =3D c.id
=C2=A0 =C2=A0 left outer join
=C2=A0 =C2=A0 =C2=A0 (select fk_id_city, count(*) as cnt
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0,string_agg(name, &#= 39;, ') AS parks
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from park
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0group by fk_id_city) p
=C2=A0 =C2=A0 =C2=A0 on p.fk_id_city =3D c.id
=C2=A0 order by c.id
;

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

Regards,
Frank

--000000000000eaf64305dfae8bad--