From: Mladen Gogala <gogala.mladen@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Very slow 101-feeling design/query..
Date: Sat, 11 Dec 2021 14:22:23 -0500
Message-ID: <e8c3a8cf-09be-542f-7a8f-f6194eef7b83@gmail.com> (raw)
In-Reply-To: <CAOC+FBVV92QZ+hpCrtKDUXWe3ALB1UkdJtzoe5K=g3YDNctS8w@mail.gmail.com>
References: <CAOC+FBWG-0BH_prvkbMTFuAQ37pr+Kx3S=V=krE1qkGhojXtJw@mail.gmail.com>
<023f01d7eea7$fcb071d0$f6115570$@msym.fr>
<CAOC+FBVV92QZ+hpCrtKDUXWe3ALB1UkdJtzoe5K=g3YDNctS8w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gogala.mladen@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Very slow 101-feeling design/query..
In-Reply-To: <e8c3a8cf-09be-542f-7a8f-f6194eef7b83@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox