From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Very slow 101-feeling design/query..
Date: Fri, 10 Dec 2021 18:21:11 -0600
Message-ID: <da128cf9-c256-c19d-ff36-2991686e6f97@gmail.com> (raw)
In-Reply-To: <CAOC+FBVP7YB5WHyxCY6cG75g85rNahAapBFNZcSss6R=Wm7X_A@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>
<ba1c1fd3-5cd9-4489-53d9-b102450d60d8@gmail.com>
<CAOC+FBVP7YB5WHyxCY6cG75g85rNahAapBFNZcSss6R=Wm7X_A@mail.gmail.com>
This is why you need to paste actual queries and table definitions. (Snip
out columns with names you don't want on the internet.)
Have the tables been recently analyzed?
On 12/10/21 5:50 PM, Wells Oliver wrote:
> Sorry, that was a typo, there is no play_id, the view is defined as SELECT
> * FROM joints JOIN plays USING (play_uuid);
>
>
> On Fri, Dec 10, 2021 at 3:49 PM Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
> 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.
>
>
>
> --
> 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: <da128cf9-c256-c19d-ff36-2991686e6f97@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