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 1mvpdo-0000vx-7L for pgsql-admin@arkaria.postgresql.org; Fri, 10 Dec 2021 23:49:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mvpdm-0003oe-Qf for pgsql-admin@arkaria.postgresql.org; Fri, 10 Dec 2021 23:49:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mvpdm-0003oV-Ch for pgsql-admin@lists.postgresql.org; Fri, 10 Dec 2021 23:49:26 +0000 Received: from mail-oi1-x233.google.com ([2607:f8b0:4864:20::233]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mvpdf-0000qc-NC for pgsql-admin@lists.postgresql.org; Fri, 10 Dec 2021 23:49:26 +0000 Received: by mail-oi1-x233.google.com with SMTP id s139so15298844oie.13 for ; Fri, 10 Dec 2021 15:49:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=QFXu858SuSLfEgfNkS57rimWnfulfsrRJxul3gqSdB8=; b=j2S3h8JWwKDw93PJ+XjcuxaKFLLq4/oKE4KC4gkHQ/jHkTNpMlUwAEoJHEKezLlhPX skdVoYy3d6R2Rz6youe3LpRBZajqdULk/xTnwOlI2Y8ZI6elk+XjcDCg0JIxWc8r9Atr agbZuyf9rJ2nSa2B7+Q2BJR22AC2IPQkW/wqk3QXzgvNLGmnBIXoO6IMghxNyD4MpL+m MFeFntyPlxXXayF5kwI1Meq5/CwMCEyFKWbZmjA2xuWV3m67C6TnketcW2WLxKSSkW1s tMUqACXxp3XGr8otK3WzsBf7T2IUApUQTujjBLeFPEfh+TUEIuMFqd9DgGJJkzTzzw6j a0NQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=QFXu858SuSLfEgfNkS57rimWnfulfsrRJxul3gqSdB8=; b=hB0RBU0N/ANpFzydfukeJNklucA2Kaf1X5Mzq+4kCcdadRe1VIalXVeqyUTf86hf3U bFuitClf5EOMBGDMVWlGeCwLYcoAzX7AozeAjSQWX595L4JhoGzuKO10LddhFam3vDTS LnkCelT4Li5weZyZtU17xlVOhyvXgtWZmZNrziDORKe6UpCh5I0grOFRlzHIBWbvYEKH ePIzfCnCnzUGjsXU1eTJDxlC5+cbEwe6dw0OcdyXL4JHJnfsxspbYR9wF9QQ+wQ2/any X1JxnCVKBANJHHRVKdznzstQMobDRyZj6TuHixcSDi07701jNSD6R9MfJqNok9Ngu4w+ ULOQ== X-Gm-Message-State: AOAM531Zgx8hvVE/HB8fAmXPhyynAU+OzOmUBBRKCRhCBfJ1qCfMrJhh To4TKnrwgc92p5L3DSL0l5IkJdni2e8= X-Google-Smtp-Source: ABdhPJweFYhHy1EjA7KMJQvPOZsp0h51ZAXOt95TF7O3T4POK7RQZ/8ejCu2Geu1vUAyYtJo4CEbpw== X-Received: by 2002:aca:4b11:: with SMTP id y17mr15588589oia.170.1639180157525; Fri, 10 Dec 2021 15:49:17 -0800 (PST) Received: from [192.168.88.10] (ip68-11-68-85.no.no.cox.net. [68.11.68.85]) by smtp.googlemail.com with ESMTPSA id c4sm806005ook.16.2021.12.10.15.49.16 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 10 Dec 2021 15:49:17 -0800 (PST) Subject: Re: Very slow 101-feeling design/query.. To: pgsql-admin@lists.postgresql.org References: <144f1205-22cc-b6ef-b0f9-21c7d271397a@gmail.com> From: Ron Message-ID: Date: Fri, 10 Dec 2021 17:49:16 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------BAB616CE615FADDD572BB001" Content-Language: en-US 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. --------------BAB616CE615FADDD572BB001 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 -- Angular momentum makes the world go 'round. --------------BAB616CE615FADDD572BB001 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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> 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.




--

--
Angular momentum makes the world go 'round.
--------------BAB616CE615FADDD572BB001--