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 <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1mvtq8-0007VR-3K
	for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 04:18:28 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1mvtq6-0007EC-Pu
	for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 04:18:26 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1mvtq6-0007E2-Fe
	for pgsql-admin@lists.postgresql.org; Sat, 11 Dec 2021 04:18:26 +0000
Received: from impout007aa.msg.chrl.nc.charter.net ([47.43.20.31]
 helo=impout007.msg.chrl.nc.charter.net)
	by makus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1mvtpz-0002wp-V2
	for pgsql-admin@postgresql.org; Sat, 11 Dec 2021 04:18:25 +0000
Received: from cerberus.crazybean.net ([71.11.29.254])
	by cmsmtp with ESMTP
	id vtpymjWkPhBsjvtpymFQQo; Sat, 11 Dec 2021 04:18:18 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.4 cv=BISgEXcG c=1 sm=1 tr=0 ts=61b4268a
 a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17
 a=xqWC_Br6kY4A:10 a=IOMw9HtfNCkA:10 a=pGLkceISAAAA:8 a=DOxZ6I4ch-azDpbUzcMA:9
 a=QEXdDO2ut3YA:10 a=A8qHrOjyU3pE4J8l:21 a=_W_S_7VecoQA:10
Received: from smtpclient.apple (<unknown>
 [2001:470:e117:4:4dbf:8ebe:940d:30b4])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 7c981428
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Fri, 10 Dec 2021 23:18:17 -0500 (EST)
From: Rui DeSousa <rui@crazybean.net>
Message-Id: <351E9FBA-1FF4-4A7B-BD78-ED60F36BA7F7@crazybean.net>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_CEDA971B-2685-4B27-B54F-E132E8A8C237"
Mime-Version: 1.0 (Mac OS X Mail 15.0 \(3693.20.0.1.32\))
Subject: Re: Very slow 101-feeling design/query..
Date: Fri, 10 Dec 2021 23:18:14 -0500
In-Reply-To: 
 <CAOC+FBWG-0BH_prvkbMTFuAQ37pr+Kx3S=V=krE1qkGhojXtJw@mail.gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
To: Wells Oliver <wells.oliver@gmail.com>
References: 
 <CAOC+FBWG-0BH_prvkbMTFuAQ37pr+Kx3S=V=krE1qkGhojXtJw@mail.gmail.com>
X-Mailer: Apple Mail (2.3693.20.0.1.32)
X-CMAE-Envelope: 
 MS4xfNvI8TaGDWbqA1PpK3i9PtbnjEKL5BuxQ0zFUrYtQgwy3VsxJbcKcmKnMq+5hGDUKX/f6Q6zGmuWe5SwCOaEj9V6PjF8Unlhx1mWo2CH0JkacOSKjZhk
 3r0w+rLfhQLz8NoT3cKduJCsGrTdJOyRnrOwCbC6zHlcudvQXs9kNcqgQXrT+eiksXuVWCVayXE7OxW26CNlAhhtafxFOZNCDSs+dhKkpqHAKECUsJLNiqAp
 l/idCAGWPDi9vUYqezVJRg==
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/351E9FBA-1FF4-4A7B-BD78-ED60F36BA7F7%40crazybean.net>
Precedence: bulk


--Apple-Mail=_CEDA971B-2685-4B27-B54F-E132E8A8C237
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8



> On Dec 10, 2021, at 5:53 PM, Wells Oliver <wells.oliver@gmail.com> =
wrote:
>=20
> SELECT DISTINCT game_id FROM vw_joints

Seeing distinct makes me cringe; most uses of it foreshadow either a =
poor design or a bad query; not always but more often than not.

If you=E2=80=99re really trying to do a distinct you can select from the =
game table and do an exists against plays and joints tables thus =
removing the distinct clause; but I digress as the given query is most =
likely an over simplified query just as example.

Partitioning does nothing here as the query doesn=E2=80=99t filter on =
the partitioned field thus no partition elimination.



--Apple-Mail=_CEDA971B-2685-4B27-B54F-E132E8A8C237
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br =
class=3D""><div style=3D"font-size: 17px;"><br class=3D""><blockquote =
type=3D"cite" class=3D""><div class=3D"">On Dec 10, 2021, at 5:53 PM, =
Wells Oliver &lt;<a href=3D"mailto:wells.oliver@gmail.com" =
class=3D"">wells.oliver@gmail.com</a>&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div class=3D""><span =
style=3D"caret-color: rgb(0, 0, 0); font-family: Helvetica; font-style: =
normal; font-variant-caps: normal; font-weight: normal; letter-spacing: =
normal; text-align: start; text-indent: 0px; text-transform: none; =
white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; =
text-decoration: none; float: none; display: inline !important;" =
class=3D"">SELECT DISTINCT game_id FROM =
vw_joints</span></div></blockquote></div><br style=3D"font-size: 17px;" =
class=3D""><div style=3D"font-size: 17px;" class=3D"">Seeing distinct =
makes me cringe; most uses of it foreshadow either a poor design or a =
bad query; not always but more often than not.</div><div =
style=3D"font-size: 17px;" class=3D""><br class=3D""></div><div =
style=3D"font-size: 17px;" class=3D"">If you=E2=80=99re really trying to =
do a distinct you can select from the game table and do an exists =
against plays and joints tables thus removing the distinct clause; but I =
digress as the given query is most likely an over simplified query just =
as example.</div><div style=3D"font-size: 17px;" class=3D""><br =
class=3D""></div><div style=3D"font-size: 17px;" class=3D"">Partitioning =
does nothing here as the query doesn=E2=80=99t filter on the partitioned =
field thus no partition elimination.</div><div style=3D"font-size: =
17px;" class=3D""><br class=3D""></div><div class=3D""><br =
class=3D""></div></body></html>=

--Apple-Mail=_CEDA971B-2685-4B27-B54F-E132E8A8C237--