Date: Mon, 23 May 2022 15:14:33 +0200
From: Frank Streitzig <fstreitzig@gmx.net>
To: kimaidou <kimaidou@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Count child objects for each line of a table: LEFT JOIN, LATERAL
 JOIN or subqueries ?
Message-ID: <YouIuUVD9ivC03pj@frastr-dev>
References: 
 <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/YouIuUVD9ivC03pj%40frastr-dev>
Precedence: bulk

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 lis=
t
> 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