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 1phCgN-0003mN-HP for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 17:00:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phCgL-0001y0-Vd for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 17:00:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phCgL-0001xq-F5 for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 17:00:25 +0000 Received: from mail-yb1-xb2f.google.com ([2607:f8b0:4864:20::b2f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phCgJ-0004c5-3S for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 17:00:24 +0000 Received: by mail-yb1-xb2f.google.com with SMTP id i6so15965138ybu.8 for ; Tue, 28 Mar 2023 10:00:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680022822; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ij1qiM1Pi7zX5jr8rIAvW4K4gB5UDxW8a7v7mTq3WPQ=; b=Z1mucWSubVNUFk8HbWIwRYTGMRTZUhfc7vPs1jIBg7noD3vH2B40xOfBpnC3Xs/LtV hUxatqGDc98zkPfmuQqQCIAQnwutMwahlLmpNte3O5dwFRm9d3miMDUqMf9NUR8O8i24 b8igsJ7yU4JrnyUQUpM5A1ygPhDRCtiKzC8AEiObqRz/8uUjwU9HaLtzelPZ7ptNIbgf S0IkKK3Z3q1Af5e1//qd+yMsAk5LRxdxvXotoiveg3b0kLzm0FFuzEYtZ7LGKa06ltRh J7tfgM3HCp0uFqowORVRi4fF2lFG1IEGK08m0wF71vkzK+QafZOZzUmd3dNuMWUCx03E LyAg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680022822; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ij1qiM1Pi7zX5jr8rIAvW4K4gB5UDxW8a7v7mTq3WPQ=; b=MIjA3Ox8aFmB27cQkBQhHYkbkTougYs9crK14OENpKLLdNWGTtnu47um+rdOHE1h7Y 3EpHNwsXgf28fWjxWJOdbT36EJJ54BGKJl2Mp9elBLmBOp+J/hTEHegJGEvUMS/8qTN9 y42EZipa9gg1p9iz/NoMyc9XnHLFNAynw5al0lie3JmYQgtkvCz7EE2kYBNVr2f/mL/Q xjEAZOe8N+N58iIR6YPZlXBPcAWWgzXaunp/hxqQEVgQdnrz+dqYmSDLyU4brcPYI9sS lq5SWxlkCW4Chy0uO4K034BELrfUep3qNGwXbRve5BAOLSIFZ2GMODl8u9YTDaS7xb0i b9Pg== X-Gm-Message-State: AAQBX9dIbNcZd3Y+v/10yo2zKOIOzv4kOf00nyGVb/QlVM+FzhAbYnG/ sEfgFvgcjVluA8WC9LxaBBq9FlQVwak= X-Google-Smtp-Source: AKy350YQ+fJZ2qpjntwWdjR5sPFajtYZRG/0q/2QIb3JzPKa6KCeBsOHUxFNTpVF3e3DREn7g7oLfw== X-Received: by 2002:a25:f608:0:b0:a17:a06d:d6af with SMTP id t8-20020a25f608000000b00a17a06dd6afmr16601864ybd.3.1680022821873; Tue, 28 Mar 2023 10:00:21 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:dab:6b8c:9338:df42? ([2600:1700:bd2c:b810:dab:6b8c:9338:df42]) by smtp.googlemail.com with ESMTPSA id x94-20020a25a067000000b00b7767ca74a0sm2954300ybh.61.2023.03.28.10.00.21 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 28 Mar 2023 10:00:21 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------H0WV3vUkTuvskKg4lUksKgDr" Message-ID: <334a4103-6f8a-4942-c153-ca0692352d79@gmail.com> Date: Tue, 28 Mar 2023 12:00:21 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.8.0 Subject: Re: Cluster table based on grand parent? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: 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. --------------H0WV3vUkTuvskKg4lUksKgDr Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/28/23 11:28, Dominique Devienne wrote: > On Tue, Mar 28, 2023 at 6:06 PM Ron wrote: > > You can only get from parent to grandchild via//child.id > to grandchild.parent, so why not cluster grandchild > on grandchild.parent? > > > Hi. I don't understand your question. Yes, of course, if I want all > grand-children of a given parent, I'll do for example: > > select p.id , c.id , c.name , gc.* >   from  grandchild gc >    join child c on gc.parent = c.id >    join parent p on c.parent = p.id > where p.name = $1 > > But w/o clustering on a denormalized grandchild.grandparent FK column, as > Peter showed, > and cluster only on grandchild.parent, that's not going to access a mostly > continuous range > of pages to fetch those all grandchild rows for that one parent. But > probably 10 to 50 "row-clusters", > given the fan-out I mentioned earlier at the child-table level. Or am I > missing something? No, you're not missing something.  If you want to go directly from grandparent to grandchild, then you need to put grandparent_id in the grandchild table. Rob Sargent is right, too, though: *practically* it might not make a difference.  You've got to test. -- Born in Arizona, moved to Babylonia. --------------H0WV3vUkTuvskKg4lUksKgDr Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 3/28/23 11:28, Dominique Devienne wrote:
On Tue, Mar 28, 2023 at 6:06 PM Ron <ronljohnsonjr@gmail.com> wrote:
You can only get from parent to grandchild via child.id to grandchild.parent, so why not cluster grandchild on grandchild.parent?

Hi. I don't understand your question. Yes, of course, if I want all grand-children of a given parent, I'll do for example:

select p.id, c.id, c.name, gc.*
  from  grandchild gc
   join child c on gc.parent = c.id
   join parent p on c.parent = p.id
where p.name = $1

But w/o clustering on a denormalized grandchild.grandparent FK column, as Peter showed,
and cluster only on grandchild.parent, that's not going to access a mostly continuous range
of pages to fetch those all grandchild rows for that one parent. But probably 10 to 50 "row-clusters",
given the fan-out I mentioned earlier at the child-table level. Or am I missing something?

No, you're not missing something.  If you want to go directly from grandparent to grandchild, then you need to put grandparent_id in the grandchild table.

Rob Sargent is right, too, though: practically it might not make a difference.  You've got to test.

--
Born in Arizona, moved to Babylonia.
--------------H0WV3vUkTuvskKg4lUksKgDr--