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 1mw4m2-0002Hd-06 for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 15:58:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mw4lz-0004QO-4Z for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 15:58:55 +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 1mw4ly-0004QF-PM for pgsql-admin@lists.postgresql.org; Sat, 11 Dec 2021 15:58:54 +0000 Received: from mout.kundenserver.de ([212.227.17.24]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mw4lv-0008Dn-Tb for pgsql-admin@postgresql.org; Sat, 11 Dec 2021 15:58:54 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue107 [212.227.15.184]) with ESMTPSA (Nemesis) id 1McYP5-1mO6zs3MU9-00d0QP; Sat, 11 Dec 2021 16:58:49 +0100 From: "Michel SALAIS" To: "'Wells Oliver'" , "'pgsql-admin'" References: In-Reply-To: Subject: RE: Very slow 101-feeling design/query.. Date: Sat, 11 Dec 2021 16:58:48 +0100 Message-ID: <023f01d7eea7$fcb071d0$f6115570$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0240_01D7EEB0.5E754F00" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJu9kAoRoJ4eEH4768KCg56rnu2vqr/YSMg Content-Language: fr X-Provags-ID: V03:K1:ieF09PXTyZ1AliIqH7MyyDonle1ECCLfgEgKjTVlCuDS/r9jN7b 95sCTs0WcJumgwYCQ9vAmVHyfSctAHbbAiWDKzxXiBB7xwSvnIwYeD+70pw/NTrDYc3LOPE qlgiYulZxwesqkXYMaPxEEpOjB1r3lUU0iKQA+t/2bo6DgkrIQVELMrC5l3j6zGwUzOKm7U tR8K8IFLJ/G2gtBHqwPfw== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:bXisPsV7p6c=:MsW5CDm/YMRa/+OUsVCTLZ Hz/QLFk3ZDiTMITblDGENh3f0i55TzEjv/XdjB6WXvvUhRJiDd0IEPVNBwvzuSsxTGcS0tpyK bv4K3LXQK0PFMyVm7hB7XAtGGE8GIZDcT34nMX5Y6u5+yVWXN29LTUAttXLjtLMsdJ0Lm7oHS ExNKPbPXyMSxUU5vJkcBKIxPcr341CxFffrmKOmibq/4hGmPzoKCmm1lFvLmOu//IlHX7Jl8k +ISTXYkZBSiXv3viUPpvKVloKC93Sj0vB6FlleYQ1bnHiv1q8fVTfDIFy04iJGXU599iPkwH9 ZG9XfYh1iVrmvOI/gWye+GaNnV3A6DRIckagLN1EeJzwjMYWWc8DCUdgZmqWsKx/vrunCyMM6 TmY2Ykqjqz55xLpe4Vt2Cz+FDzaC1F8sr2Gq9hSB1/ta+wknMb2vE03kTL7mE7Eze7tayNKuw GTZt+9Ox0mEI80j9CMkZfo33jHIQ31C+OfeLwkswf9rKv4xCMIjyiAFSAP05DpuiUY/D7tcI7 cM18YeViCrFReC5+eGBswpJBt7brDk9L/iKf6Fp4XM5/cElyCh93xW4nI1tqLBaOeLLzIYpch X8DsAry4Sx2D04JyV1Pft1AIug5sQuw737Yj4Siy19Zf9T2hGv+mTttrqCL2S/fMvLWWU4FKf buXrPEMddBofFJ0YMY9ja8Ohqu90UBp//hwRjYEr54ryONE7GiGi+SIswAKYuael1qAM= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0240_01D7EEB0.5E754F00 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi Using view which does a join in two tables to access things in one of = them is not a good idea. You pay for a join even though it is not = necessary. So if you just want distinct game_id and you don=E2=80=99t = care if its play_uuid is in joints, then you can accelerate using the = table plays directly. =20 Michel SALAIS De : Wells Oliver =20 Envoy=C3=A9 : vendredi 10 d=C3=A9cembre 2021 23:53 =C3=80 : pgsql-admin Objet : Very slow 101-feeling design/query.. =20 This feels very 101 but I feel like it should be much faster: =20 A table "joints" with a PK of play_uuid, target_id, joint_seq, = joint_timestamp. =20 "joints" is partitioned using RANGE on joint_timestamp for monthly = partitions 1/1 - 2/1, 2-1 - 3/1, etc. =20 "joints" has an FK where play_uuid refers to table "plays" and column = "play_uuid" where "play_uuid" is the PK. =20 "plays" additionally has an indexed column game_id. =20 "joints" has 1133,932,391 rows across 12 monthly partitions for 2021, = and "plays has 585,627 rows. We made a view called "vw_joints" which = just does: =20 SELECT * FROM joints JOIN plays USING (play_id); =20 Then doing: =20 SELECT DISTINCT game_id FROM vw_joints =20 Takes 35-45 minutes. Which seems nuts. We do this kind of design in a = few different plays to normalize things, but it comes at the cost of = these agonizingly slow (and seemingly dead simple) qeuries. =20 Is there any optimization to do here beyond flattening table and = de-normalizing data? Is the partitioning causing a slowness here? I feel = like partitioning is creating some difficulty... =20 Appreciate it. =20 --=20 Wells Oliver wells.oliver@gmail.com =20 ------=_NextPart_000_0240_01D7EEB0.5E754F00 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi

Using view which does a join in two = tables to access things in one of them is not a good idea. You pay for a = join even though it is not necessary. So if you just want distinct = game_id and you don=E2=80=99t care if its play_uuid is in joints, then = you can accelerate using the table plays = directly.

 

Michel = SALAIS

De : Wells Oliver = <wells.oliver@gmail.com>
Envoy=C3=A9 : vendredi 10 = d=C3=A9cembre 2021 23:53
=C3=80 : pgsql-admin = <pgsql-admin@postgresql.org>
Objet : Very slow = 101-feeling design/query..

 

This = feels very 101 but I feel like it should be much = faster:

 

A = table "joints" with a PK of play_uuid, target_id, joint_seq, = joint_timestamp.

 

"joints" is partitioned using RANGE on = joint_timestamp for monthly partitions 1/1 - 2/1, 2-1 - 3/1, = etc.

 

"joints" has an FK where play_uuid refers to = table "plays" and column "play_uuid" where = "play_uuid" is the PK.

 

"plays" additionally has an indexed column = game_id.

 

"joints" has 1133,932,391 rows across = 12 monthly partitions for 2021, and "plays has 585,627 rows. = We made a view called "vw_joints" which just = does:

 

SELECT * FROM joints JOIN plays USING = (play_id);

 

Then doing:

 

SELECT DISTINCT game_id FROM = vw_joints

 

Takes 35-45 minutes. Which seems nuts. We do this kind = of design in a few different plays to normalize things, but it comes at = the cost of these agonizingly slow (and seemingly dead simple) = qeuries.

 

Is there any optimization to do here beyond flattening = table and de-normalizing data? Is the partitioning causing a slowness = here? I feel like partitioning is creating some = difficulty...

 

Appreciate it.

 

-- =

------=_NextPart_000_0240_01D7EEB0.5E754F00--