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 1nt8qb-0006i8-UD for pgsql-sql@arkaria.postgresql.org; Mon, 23 May 2022 14:15:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nt8qa-00047E-MY for pgsql-sql@arkaria.postgresql.org; Mon, 23 May 2022 14:15:48 +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 1nt8qa-000472-Bj for pgsql-sql@lists.postgresql.org; Mon, 23 May 2022 14:15:48 +0000 Received: from mail-ed1-x533.google.com ([2a00:1450:4864:20::533]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nt8qW-0005xl-5h for pgsql-sql@lists.postgresql.org; Mon, 23 May 2022 14:15:47 +0000 Received: by mail-ed1-x533.google.com with SMTP id c10so19342458edr.2 for ; Mon, 23 May 2022 07:15:43 -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=M8hZBa4v+pEdA9sbdd+9o2yQUcwnXsZ9wHANf6KWSIA=; b=LqcwduFJJt0eMvI2UD+neJegtKhLcnW8SnT/2D1nkpqX9+DMdRTySjhwRsBYXOlNlz 1ap9u4Kp7x3fnbBkxNQxRFe782lABU+YjhbdIDxtwUy9hp5h8cCCQIMSFHVW3+UHz8QK yRYyptWlRC+6tIEQ0Lc/uKUDy13xpGCfSpxIaqwdiwT3Kcgx/AKonmLcIRnL/dlT75FX WUPQAXa9tVhqwLw8ydl/1oZchWI8WW9Psi2is+ytF/WCHeUGVeF8mh1W1cEv10pDvinD GIPcp/aE2xw36aNiAC+00f8WXuUIp6PVW2r9ECO0ceg0iRBOXJIsrfKiNN4w7g5RKr6S zvnw== 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=M8hZBa4v+pEdA9sbdd+9o2yQUcwnXsZ9wHANf6KWSIA=; b=IXycLvXaIftS/KPR8uioLz2D1MKOpp+m5q/Ecf/SAoDRtlQD/y1Cf0RcToa3HqoPA9 8w7c3QxTtKCHHBn8pXPWPTo8fDnGNrtp8wV9eAxlhcXBN2Ff7L00KnS8auc++mi+cpDT 4UqPr01m1VEAVg3Oe6k+Z/m3gDWWcWXKK6/b02VjRxwmFMtlMCWSfLu3gqRiqOBe/tya cnhqt6GON0RXxlBCkDXyfnMwmDEbaHT15u4wgO4P607l4ck9/b/TjXGXtzGFRaLhuCmU qLZQz3+x5fnBXb8FeuhVCqC3jbRmteoWLpbLPLaAre90QbZu9mkaf9x2Rs7S919E+T0y MFvA== X-Gm-Message-State: AOAM5313odmyDB0skDqf3XB4MqCmNblBKxWL/9kg8w8u+0YK5Vs9fMVl 68edrYXgt9Nl+ShYGrlK2hVCLQMQGP1a+iHW4ho= X-Google-Smtp-Source: ABdhPJx3GUHdzRbLKaCo3DT6hVJo1Q3ShQ9k+DzmCuJufY0xdsWKl5r0Q1UxRyITGv+FedB7RTktNrZN77Io6/oEMLg= X-Received: by 2002:aa7:d415:0:b0:42a:bb4d:7deb with SMTP id z21-20020aa7d415000000b0042abb4d7debmr24640188edq.6.1653315343162; Mon, 23 May 2022 07:15:43 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: kimaidou Date: Mon, 23 May 2022 16:15:31 +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="000000000000bb271905dfae79fb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000bb271905dfae79fb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 li= st > > of cities, parks, childcare centres, schools. I need to count the numbe= r > 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 > > --000000000000bb271905dfae79fb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
So you

Le=C2=A0lun. 23 mai 2022 =C3=A0=C2=A015:14, Fran= k Streitzig <fstreitzig@gmx.net> a =C3=A9crit=C2=A0:
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 l= ist
> of cities, parks, childcare centres, schools. I need to count the numb= er 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<= 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

--000000000000bb271905dfae79fb--