MIME-Version: 1.0
From: kimaidou <kimaidou@gmail.com>
Date: Mon, 23 May 2022 13:55:07 +0200
Message-ID: 
 <CAMKXKO6Sam3WrzCb12yRyW+=OT_7K9zz3MfAQy4PNywuqhkGTQ@mail.gmail.com>
Subject: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN
 or subqueries ?
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000977aae05dfac83e7"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMKXKO6Sam3WrzCb12yRyW%2B%3DOT_7K9zz3MfAQy4PNywuqhkGTQ%40mail.gmail.com>
Precedence: bulk

--000000000000977aae05dfac83e7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

I love the first one, with LEFT JOINS, because it is concise, seems simple,
and allows querying easily more than one aggregated field from the child
items. But we need to use the DISTINCT clause inside the aggregation
functions (count, string_agg), in order to count each child only once.

The one with the subqueries seems the more common way (I have seen it a
lot) but I find it cumbersome, and I guess it won't scale well for bigger
datasets.

The one with the LATERAL joins seems overcomplicated, but I probably missed
some easier way to use the lateral join. I do not know well how the LATERAL
differs from the subqueries...

I would like to have your opinion on this scenario. What is the best query
for this use case, considering the fact that it should perform well on
heavier datasets (1 million cities and thousands of children).

NB: I used SQL Fiddle to help everyone see the data and SQL queries. Not
sure if everyone can modify it or not. Please try to keep the 3 example
unchanged. It seems SQL Fiddle has not been update since at least 2018, so
PostgreSQL version is 9.6.

Regards
Micha=C3=ABl

--000000000000977aae05dfac83e7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi list,</div><div><br></div><div>I have a basic need=
, often encountered in spatial analysis: I have a list of cities, parks, ch=
ildcare centres, schools. I need to count the number of items for each city=
 (0 if no item exists for this city)<br></div><div><br></div><div>I have te=
sted 3 different SQL queries to achieve this goal:</div><div><br></div><div=
>* one with several LEFT JOINS: <a href=3D"http://sqlfiddle.com/#!17/fe902/=
3">http://sqlfiddle.com/#!17/fe902/3</a></div><div>* one with sub-queries: =
<a href=3D"http://sqlfiddle.com/#!17/fe902/4">http://sqlfiddle.com/#!17/fe9=
02/4</a></div><div><div></div><div>* one with several LATERAL JOINS: <a hre=
f=3D"http://sqlfiddle.com/#!17/fe902/6">http://sqlfiddle.com/#!17/fe902/6</=
a></div><div><br></div><div>I love the first one, with LEFT JOINS, because =
it is concise, seems simple, and allows querying easily more than one aggre=
gated field from the child items. But we need to use the DISTINCT clause in=
side the aggregation functions (count, string_agg), in order to count each =
child only once.</div><div><br></div><div>The one with the subqueries seems=
 the more common way (I have seen it a lot) but I find it cumbersome, and I=
 guess it won&#39;t scale well for bigger datasets.<br></div><div><br></div=
><div>The one with the LATERAL joins seems overcomplicated, but I probably =
missed some easier way to use the lateral join. I do not know well how the =
LATERAL differs from the subqueries...<br></div><div><br></div><div>I would=
 like to have your opinion on this scenario. What is the best query for thi=
s use case, considering the fact that it should perform well on heavier dat=
asets (1 million cities and thousands of children).</div><div><br></div><di=
v>NB: I used SQL Fiddle to help everyone see the data and SQL queries. Not =
sure if everyone can modify it or not. Please try to keep the 3 example unc=
hanged. It seems SQL Fiddle has not been update since at least 2018, so Pos=
tgreSQL version is 9.6.<br></div><div><br></div><div>Regards</div><div>Mich=
a=C3=ABl<br></div><div><br></div><div><br></div></div></div>

--000000000000977aae05dfac83e7--