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 1mw7x3-0003dn-J0 for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 19:22:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mw7x2-00035O-60 for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 19:22:32 +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 ) id 1mw7x1-0002zh-N9 for pgsql-admin@lists.postgresql.org; Sat, 11 Dec 2021 19:22:31 +0000 Received: from mail-qt1-x829.google.com ([2607:f8b0:4864:20::829]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mw7ww-00018r-Sw for pgsql-admin@lists.postgresql.org; Sat, 11 Dec 2021 19:22:30 +0000 Received: by mail-qt1-x829.google.com with SMTP id p19so11549650qtw.12 for ; Sat, 11 Dec 2021 11:22:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=ov9/6odkauM9vyyN0MVcvx8M9tZqndLuEPbq1KBRd8w=; b=QLUkWSix5bc35VAsX1DEJIqZkr/P/cf4h4UI422xSor35GjPID6jBtJnvVfi05SwGV iTIDXxV21U1yXleaCwHxW6ZrnWNc4b3x/M0HRceYCMVhRZsz7v8N005UYRj8vcgJppmb 2xYqwScp/WaWaJqfgTi7q/xPKWzyyhA54LX1NFfoMeaIdhxFrR2906yulAOgRMDtoMyz OLh1oo3EN5qWfpelIjpntCAMeJD12ltkeEQ0pbcqEmokDt2/Uxcl3uCAzdzCxcMTzIse dDbdSIOoaUll3X5X4flDC7CUnGH66IjVCiv8MTFB7rF4m2+ufV6ACuGvJCcFW8y0OX+i 8Dbw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=ov9/6odkauM9vyyN0MVcvx8M9tZqndLuEPbq1KBRd8w=; b=MfW8qY+Xug7tUV33uSTeEmrGK4G5B3HHaVLCblytLmR82WTo69yqCj7BvCU27JfNKP s/E2XnPFHu2vCXicSYn6omUN0GhSqSyr6Lo/Ab3PtWpl1da5PdNpBT9NOfd1b/ZIZXqW LPmOWJWtZdnp9bMZES3sKTPcMMaC1L9rZvc0fPaIQF+z2guZlZ9ys06ADGx9BIp+kKng F/aF4v79CqXNF1tVgkus6XcVzX+yw/VdNWSu9oz9i5LFYNbuQH8fDXJjfhmA6MVCdmwe yq1XWvAtWy76c0NYMYGEpuYsfw9eCkWYjzrLUWMeBUCke/uMJvFcQFx2Oflne4o8CGwY XBzA== X-Gm-Message-State: AOAM530EalFsuWRYrNz6buBA9gXoAUmMzP/MCmGwiQC6FyYolkcr0p+o sa4+R5wta80BIF9pxVe0Kx+el+Z0QBnUpw== X-Google-Smtp-Source: ABdhPJzcMWDKMqlr/6R9a3z6qcJZzQB3eFId008ifCbWhMS3TWIjCMvUJZUDl06NasrBb6Ls6qaFUA== X-Received: by 2002:a05:622a:388:: with SMTP id j8mr35284901qtx.366.1639250544919; Sat, 11 Dec 2021 11:22:24 -0800 (PST) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id m4sm4546646qtu.87.2021.12.11.11.22.23 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 11 Dec 2021 11:22:24 -0800 (PST) Content-Type: multipart/alternative; boundary="------------irE7EGyyRPnqWHf8rj63eQc0" Message-ID: Date: Sat, 11 Dec 2021 14:22:23 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.3.0 Subject: Re: Very slow 101-feeling design/query.. Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <023f01d7eea7$fcb071d0$f6115570$@msym.fr> From: Mladen Gogala In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------irE7EGyyRPnqWHf8rj63eQc0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 12/11/21 13:32, Wells Oliver wrote: > We have hundreds of millions of joint locations across hundreds of > thousands of plays across thousands of games. We also have a few > hundred games with plays in them, but no joint measurements. The joint > data is a measurement of body joints through time (up to 300x a second) > > So my idea was the plays table contains the game identifier (there is > no root game table), and the joints table refers to the plays table by > a play UUID. > > As an example: One game contains 231 plays. There are 396,144 rows of > joint data. > > I partitioned the joints data by month because of the volume. > > The end result is using the joints view (which joins to plays) is just > dead, grinding slow, and I'm trying to think of alternatives that stay > within Postgres, i.e. maybe BigQuery and/or RedShift is more of an > appropriate solution, but I don't want to go there quite yet. > > Just trying to optimize this design and open to ideas. Thanks. > > On Sat, Dec 11, 2021 at 7:58 AM Michel SALAIS wrote: > > 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’t > care if its play_uuid is in joints, then you can accelerate using > the table plays directly. > > /Michel SALAIS/ > > *De :* Wells Oliver > *Envoyé :* vendredi 10 décembre 2021 23:53 > *À :* pgsql-admin > *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... > > -- > > Well, you can create a trigger which would fire whenever row is inserted or deleted and would update joint, game_id and the count in a separate table. That is the usual solution for the problem you described. Also, if there is a small amount of games, you can use hash index instead of B-tree indexes. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------irE7EGyyRPnqWHf8rj63eQc0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 12/11/21 13:32, Wells Oliver wrote:
We have hundreds of millions of joint locations across hundreds of thousands of plays across thousands of games. We also have a few hundred games with plays in them, but no joint measurements. The joint data is a measurement of body joints through time (up to 300x a second)

So my idea was the plays table contains the game identifier (there is no root game table), and the joints table refers to the plays table by a play UUID.

As an example: One game contains 231 plays. There are 396,144 rows of joint data.

I partitioned the joints data by month because of the volume.

The end result is using the joints view (which joins to plays) is just dead, grinding slow, and I'm trying to think of alternatives that stay within Postgres, i.e. maybe BigQuery and/or RedShift is more of an appropriate solution, but I don't want to go there quite yet.

Just trying to optimize this design and open to ideas. Thanks.

On Sat, Dec 11, 2021 at 7:58 AM Michel SALAIS <msalais@msym.fr> wrote:

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’t 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é : vendredi 10 décembre 2021 23:53
À : 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...

--

Well, you can create a trigger which would fire whenever row is inserted or deleted and would update joint, game_id and the count in a separate table. That is the usual solution for the problem you described. Also, if there is a small amount of games, you can use hash index instead of B-tree indexes.


-- 

Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------irE7EGyyRPnqWHf8rj63eQc0--