Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ivLLJ-0003ng-Qq for pgsql-sql@arkaria.postgresql.org; Sat, 25 Jan 2020 13:19:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ivLLH-0006xE-Gn for pgsql-sql@arkaria.postgresql.org; Sat, 25 Jan 2020 13:19:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ivLLH-0006x7-0b for pgsql-sql@lists.postgresql.org; Sat, 25 Jan 2020 13:19:15 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ivLL9-0000Fk-4o for pgsql-sql@lists.postgresql.org; Sat, 25 Jan 2020 13:19:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=runbox.com; s=rbselector1; h=Content-Type:MIME-Version:Message-ID:Date:Subject: In-Reply-To:References:To:From; bh=USgxsZ0bu1GZSpDrYih6AMnNL6iNS3jR/eEw+cAAgSA=; b=mpPx3+nuqar1fvsPt8eOquRBfz muUn4ShyHTQTL3ZOrjSm7n84kxyHQm/RaHFOtxcIaPGmiDLQzqjWJcEfJMe/FgUVn/+yqY3ZCEOK2 zKIpWepXnszQJYDu1ggEGnRBz/QFJvUAGz/SDFryutPTyu3d92zcBRgPn2ro+jIrELPqagYzdYVNw v9zdzhv+4wyCUFJRcGW2gRMPcDC/dWDla03GEhdqcJ39F/eVlAYG5Wz9EVoWenSA+yR+WwzGUHCMT RoltE3dUPXM7JmahjM/JpvUVB0ai4/JprQIeYHp+y1mruMUd0CCdiA0ah1sbz+xD0ZYy+SnWJCC8+ p/gMI2fA==; Received: from [10.9.9.202] (helo=mailfront20.runbox) by mailtransmit02.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1ivLL6-00021W-B2; Sat, 25 Jan 2020 14:19:04 +0100 Received: by mailfront20.runbox with esmtpsa [Authenticated alias (300526)] (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) id 1ivLKn-00021u-71; Sat, 25 Jan 2020 14:18:45 +0100 From: "Mike Sofen" To: =?UTF-8?Q?'Brice_Andr=C3=A9'?= , References: In-Reply-To: Subject: RE: SQL schema and query optimisation for fast cross-table query execution Date: Sat, 25 Jan 2020 05:18:42 -0800 Message-ID: <0f9f01d5d381$f8216e00$e8644a00$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0FA0_01D5D33E.E9FFB4A0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJuyIJjpw+/qyGFTpBNCkWG5pbj8qbJbgLw Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0FA0_01D5D33E.E9FFB4A0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable From: Brice Andr=C3=A9 Sent: Saturday, January = 25, 2020 12:49 AM wrote: =20 The current DB schema is as follows: user table : this table records all users registered on the web-site - user_id - ... challenge table: this table records all ongoing challenges - challenge_id - ... user_challenge_association table : this table records which user is = registered to which challenge - user_id - challenge_id activity table : this table records all activities of the users - user_id - date - ... =20 a naive implementation of my query could be something like this: SELECT * FROM activity WHERE user_id IN (SELECT user_id FROM = user_challenge_association WHERE challenge_id IN (SELECT challenge_id = FROM user_challenge_association WHERE user_id =3D ||current_user||)) = ORDER BY date Brice --------------------------------------------------------------- =20 Brice, you are correct, =E2=80=9CIN=E2=80=9D clauses are horrific = performers because they turn every member of the IN into an OR, so the = larger your IN list, the slower your query runs. =20 =20 But every IN can be replaced with a join, and that leverages the power = of the relational engine. With standard indexes on the primary and = foreign keys, this will be extremely fast and scalable to many hundreds = of millions of rows.=20 =20 Taking your query and rewriting it with joins: SELECT u.user_name, c.challenge_name, a.activity_name, a.activity_date FROM activity a=20 Join user_challenge_association uca on (user_id) Join challenge c on (challenge_id) Join user u on (user_id) Where u.user_id =3D ||current_user||)) ORDER BY date =20 Mike Sofen ------=_NextPart_000_0FA0_01D5D33E.E9FFB4A0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

From: Brice = Andr=C3=A9 <brice@famille-andre.be> =C2=A0Sent: Saturday, = January 25, 2020 12:49 AM
wrote:

 

The current DB schema is as follows:
user table : this = table records all users registered on the web-site
   - = user_id
   - ...
challenge table: this table records all = ongoing challenges
   - challenge_id
   - = ...
user_challenge_association table : this table records which user = is registered to which challenge
   - user_id
  =  - challenge_id
activity table : this table records all = activities of the users
   - user_id
   - = date
   - ...
   
a naive implementation of = my query could be something like this:

SELECT * FROM activity = WHERE user_id IN (SELECT user_id FROM user_challenge_association WHERE = challenge_id IN (SELECT challenge_id FROM user_challenge_association = WHERE user_id =3D ||current_user||)) ORDER BY = date

Brice

-------------------------------------------------------= --------

 

Brice, you are correct, =E2=80=9CIN=E2=80=9D clauses = are horrific performers because they turn every member of the IN into an = OR, so the larger your IN list, the slower your query runs.=C2=A0 =

 

But every IN can be replaced with a join, and that = leverages the power of the relational engine.=C2=A0 With standard = indexes on the primary and foreign keys, this will be extremely fast and = scalable to many hundreds of millions of rows.

 

Taking your = query and rewriting it with joins:

SELECT u.user_name, c.challenge_name, a.activity_name, = a.activity_date

FROM activity a =

Join = =C2=A0user_challenge_association uca on (user_id)

Join challenge c on (challenge_id)

Join user u on (user_id)

Where u.user_id =3D ||current_user||)) ORDER BY = date

 

Mike = Sofen

------=_NextPart_000_0FA0_01D5D33E.E9FFB4A0--