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 1phCW3-0003I6-ET for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 16:49:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phCW2-0001mw-C1 for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 16:49:46 +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 1phCW2-0001mn-1V for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 16:49:46 +0000 Received: from mail-pj1-x1036.google.com ([2607:f8b0:4864:20::1036]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1phCVz-0004Vx-LB for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 16:49:45 +0000 Received: by mail-pj1-x1036.google.com with SMTP id qe8-20020a17090b4f8800b0023f07253a2cso13239662pjb.3 for ; Tue, 28 Mar 2023 09:49:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1680022182; 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=Fa7hTZOT/fVOpJpdCrMATblaebtfsJr+vMPACoslZwo=; b=m1TqDMcN5k1K8UmJy6tTInweZHhWh2SLAztGjJ2nthKV5S+bsG3fi3PTtNjmnRW4M9 FJAfY+PAxzMRvUFPy3ziK2qLY9Kzhk9radDVyPoIsehPoi7cuW2s5ooqfPuXydy5vCpu bRUD+6Ik/dhZeTYsa8eMuX1ZcuF4t0N3P4lXfzsqzHqNVBw19lTFBGhZzxdi5Nik8PYS AxYqe+Znr9b3Kdq/rG67tvXxhgyEwFubyR4WcKUDpaBh8kFt4P89j0qwtrYKW3URgVit +v2XMa/4ti2oBCf98nqe5LR9Y91o/jtm2ijHd7R/1jwT5awVxpiw5rzOYQTjr0rI6dot mqTg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680022182; 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=Fa7hTZOT/fVOpJpdCrMATblaebtfsJr+vMPACoslZwo=; b=htTo5ywUgWSkCTOm/TUcrYPMJJlABuXrJ8kEpgyIydF8unoUKVzazE6ZrBrO1RvhTF 1xPZoZ9cMTNZ2zUeTdeOmX6AElP2KrIplMRlaGprNVE+tLieTtyHbQCXqBMZFXV1pQc8 Sba+OoJAFDhacSHDuXbKQ51ct159I8e6jyEgCNghkzVMOHALaLyyKcBmLNGedcQWgj1e aGhppX039ElyKtrK3MhH5bBzNaY9FJjZWzgTLxG04FwDMroA5WSBkom29/k9m52s8Ufp J9reErEAmrWt4b4kfQ47P7yFcGG9AXn77EQ8++LcVO25wk+ecdlXFP5z/jwZofqxa28D M9IQ== X-Gm-Message-State: AAQBX9eSAUeqz3ahDqvRjBLmw7851+vi2rHmzmhNWhXUk3HDN++xR3jI aIFXFHzkyfLCbIlCovMjH0D68zXlhlo= X-Google-Smtp-Source: AKy350bLzxNfxiA2LnIKe4DHV+ZtgqFNm1ymYdv2C1zcOcMpr5fTmApkUlm62hWTJ6TKHGB0+KuD0A== X-Received: by 2002:a17:903:32d0:b0:19c:dbce:dce8 with SMTP id i16-20020a17090332d000b0019cdbcedce8mr17905674plr.15.1680022181829; Tue, 28 Mar 2023 09:49:41 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:33d0:f2f0:d848:1531? ([2601:681:5a00:f790:33d0:f2f0:d848:1531]) by smtp.gmail.com with ESMTPSA id a7-20020a170902ecc700b0019c93ee6902sm21352677plh.109.2023.03.28.09.49.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 28 Mar 2023 09:49:41 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------ZizR0z9ODJ2xOTdEAHLZMLF4" Message-ID: Date: Tue, 28 Mar 2023 10:49:40 -0600 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-CA To: pgsql-general@lists.postgresql.org References: From: Rob Sargent 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. --------------ZizR0z9ODJ2xOTdEAHLZMLF4 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/28/23 10: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? Are you using HDD (spinning) or SSD discs? Is you world strictly three levels: grand,parent,child? What tests have you done so far to compare clustered to non-clustered? --------------ZizR0z9ODJ2xOTdEAHLZMLF4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 3/28/23 10: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?
Are you using HDD (spinning) or SSD discs?
Is you world strictly three levels: grand,parent,child?
What tests have you done so far to compare clustered to non-clustered?

--------------ZizR0z9ODJ2xOTdEAHLZMLF4--