From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Very slow 101-feeling design/query..
Date: Fri, 10 Dec 2021 17:49:16 -0600
Message-ID: <ba1c1fd3-5cd9-4489-53d9-b102450d60d8@gmail.com> (raw)
In-Reply-To: <CAOC+FBXFtjELi+HrQiL+oNySrTeVz-g3te1a3Jr97iC+VCrxWw@mail.gmail.com>
References: <CAOC+FBWG-0BH_prvkbMTFuAQ37pr+Kx3S=V=krE1qkGhojXtJw@mail.gmail.com>
<144f1205-22cc-b6ef-b0f9-21c7d271397a@gmail.com>
<CAOC+FBXFtjELi+HrQiL+oNySrTeVz-g3te1a3Jr97iC+VCrxWw@mail.gmail.com>
What table is play_id in, and is it indexed?
On 12/10/21 5:27 PM, Wells Oliver wrote:
> PG 13.4. Can't quite run the EXPLAIN ANALYZE since it takes so long, but
> EXPLAIN SELECT DISTINCT game_id FROM vw_joints shows (s = joints, p = plays)
>
> ----------------------------------------------------------------------------------------------------
> HashAggregate (cost=63810150.11..63810168.40 rows=1829 width=4)
> Group Key: p.game_id
> -> Hash Left Join (cost=21647.78..60977838.19 rows=1132924766 width=4)
> Hash Cond: (s.play_uuid = p.play_uuid)
> -> Append (cost=0.00..57982241.49 rows=1132924766 width=16)
> -> Seq Scan on joints_2021_01 s_1 (cost=0.00..13.00
> rows=300 width=16)
> -> Seq Scan on joints_2021_02 s_2 (cost=0.00..13.00
> rows=300 width=16)
> -> Seq Scan on joints_2021_03 s_3 (cost=0.00..13.00
> rows=300 width=16)
> -> Seq Scan on joints_2021_04 s_4 (cost=0.00..2217902.16
> rows=49859816 width=16)
> -> Seq Scan on joints_2021_05 s_5 (cost=0.00..2965019.35
> rows=63440735 width=16)
> -> Seq Scan on joints_2021_06 s_6 (cost=0.00..3797848.89
> rows=85688889 width=16)
> -> Seq Scan on joints_2021_07 s_7 (cost=0.00..5867829.24
> rows=115975424 width=16)
> -> Seq Scan on joints_2021_08 s_8 (cost=0.00..17274328.41
> rows=380175741 width=16)
> -> Seq Scan on joints_2021_09 s_9 (cost=0.00..18226427.88
> rows=393209088 width=16)
> -> Seq Scan on joints_2021_10 s_10 (cost=0.00..1942824.01
> rows=44003201 width=16)
> -> Seq Scan on joints_2021_11 s_11 (cost=0.00..25385.72
> rows=570672 width=16)
> -> Seq Scan on joints_2021_12 s_12 (cost=0.00..13.00
> rows=300 width=16)
> -> Hash (cost=14292.90..14292.90 rows=588390 width=20)
> -> Seq Scan on plays p (cost=0.00..14292.90 rows=588390
> width=20)
> JIT:
> Functions: 34
> Options: Inlining true, Optimization true, Expressions true, Deforming true
> (22 rows)
>
>
>
> On Fri, Dec 10, 2021 at 3:17 PM Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> On 12/10/21 4:53 PM, Wells Oliver wrote:
> > 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...
>
> What Postgresql version?
>
> What does the query plan look like?
>
> --
> Angular momentum makes the world go 'round.
>
>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
--
Angular momentum makes the world go 'round.
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: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Very slow 101-feeling design/query..
In-Reply-To: <ba1c1fd3-5cd9-4489-53d9-b102450d60d8@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