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 1mvq8c-0003aF-L5 for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 00:21:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mvq8b-0001N0-Hv for pgsql-admin@arkaria.postgresql.org; Sat, 11 Dec 2021 00:21:17 +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 1mvq8b-0001Mr-3d for pgsql-admin@lists.postgresql.org; Sat, 11 Dec 2021 00:21:17 +0000 Received: from mail-ot1-x334.google.com ([2607:f8b0:4864:20::334]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mvq8Y-00015k-Gc for pgsql-admin@lists.postgresql.org; Sat, 11 Dec 2021 00:21:16 +0000 Received: by mail-ot1-x334.google.com with SMTP id w6-20020a9d77c6000000b0055e804fa524so11312216otl.3 for ; Fri, 10 Dec 2021 16:21:13 -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=U/aWJ194F/sBuWU3TWXHJP7RSVyaMwpUyenA6gG7W9A=; b=FPeayZW6Mvqjw3QymzLd8D08274JDhSeTsJH7WOrO6WhDlX2fMwXVgcrCa/r0nRe+D cntpSe9GsrQsjG0c6fXumog9BdOZdxHZEazLCOIfznj589GpkWDB3xMtEmKVKHDlkLi2 eL8zvPISCeb0P2R0fhPa2dZNiprqhHDZ4aq0FtTY8EyYKjlsiy6w955nfdEDxxY3Iaj7 S/vbJsP5HaujyTUKMmUEkRT+rkApQTb3olEvxyO84ZpOZ/DKhHCFWOXrap2HOEx6fbQZ KDtewXi6YJ5z7xkG4vOII7+1KoMlczN2krlYO6YrzvjV6pe1EqKNG6ktmamKm8Owugw9 kNFQ== 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=U/aWJ194F/sBuWU3TWXHJP7RSVyaMwpUyenA6gG7W9A=; b=2ukCZMO6LxXus8+bFHn68LmSpFLkCvXAl7QwNlYEvUCqs9asUkVokOr7BdBle1VTPj +RPyoNulCtzffe5jxVXkDiz57KwgvX816asLdIXLahDE7sOxcmhpdZ2NV9irlbSnbGeU NqU8Z/sA74z2p8Xbd9Mt2dbUI45GV6wGaYFCfTPJ8DuX2xQXd4YmMOY2pvPOfMWHy35u yV6XuFrovjw7AKH31uoo9JFxtD7xqj7JNycsiljp2t2lnuWLov23dVdiDrFbnaiFEDf6 AZ+DkJzugmAWOr2lvQxgwWuvRWGIhKxKfqxUA8QtNyIWruobf5KVHAjEA5sJ27QZ959O NfcA== X-Gm-Message-State: AOAM530fUvyt/ahCWVQd1ysU5pDzZlnUDFr55b2CQ3DxmVTsGe9BzbcP zpb2ZdsEiQufFPum8MEnSoAUf+ZubQI= X-Google-Smtp-Source: ABdhPJwbceMSPjErgsrfSoAuEoBO3aJuy3EivZSKLmhhXOfcBJWiXqg9m+lSc2wXU5Q9K83F8MglrQ== X-Received: by 2002:a9d:7f91:: with SMTP id t17mr13451405otp.197.1639182072156; Fri, 10 Dec 2021 16:21:12 -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 109sm754744otv.30.2021.12.10.16.21.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 10 Dec 2021 16:21:11 -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 18:21:11 -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="------------152797988489AEC07CB03730" 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. --------------152797988489AEC07CB03730 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 > > 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. > > > > -- > Wells Oliver > wells.oliver@gmail.com -- Angular momentum makes the world go 'round. --------------152797988489AEC07CB03730 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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> 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> 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.


--

--
Angular momentum makes the world go 'round.
--------------152797988489AEC07CB03730--