[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Cluster table based on grand parent?
From: Ron <ronljohnsonjr () gmail ! com>
Date: 2023-03-28 17:00:21
Message-ID: 334a4103-6f8a-4942-c153-ca0692352d79 () gmail ! com
[Download RAW message or body]
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
> <http://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 <http://p.id>, c.id <http://c.id>, c.name <http://c.name>, gc.*
> from grandchild gc
> join child c on gc.parent = c.id <http://c.id>
> join parent p on c.parent = p.id <http://p.id>
> where p.name <http://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.
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
On 3/28/23 11:28, Dominique Devienne wrote:<br>
<blockquote type="cite"
cite="mid:CAFCRh-9+CQnoPtavF2rhahcHf7Rvszrk3XBdyJa5B=e5zV38Cg@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div dir="ltr">On Tue, Mar 28, 2023 at 6:06 PM Ron <<a
href="mailto:ronljohnsonjr@gmail.com" moz-do-not-send="true"
class="moz-txt-link-freetext">ronljohnsonjr@gmail.com</a>>
wrote:<br>
</div>
<div class="gmail_quote">
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex">
<div>You can only get from parent to grandchild via<i> </i><a
href="http://child.id" target="_blank"
moz-do-not-send="true">child.id</a> to
grandchild.parent, so why not cluster grandchild on
grandchild.parent?<br>
</div>
</blockquote>
<div><br>
</div>
<div>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:</div>
<div><br>
</div>
<div>select <a href="http://p.id" moz-do-not-send="true">p.id</a>,
<a href="http://c.id" moz-do-not-send="true">c.id</a>, <a
href="http://c.name" moz-do-not-send="true">c.name</a>,
gc.*</div>
<div> from grandchild gc</div>
<div> join child c on gc.parent = <a href="http://c.id"
moz-do-not-send="true">c.id</a></div>
<div> join parent p on c.parent = <a href="http://p.id"
moz-do-not-send="true">p.id</a></div>
<div>where <a href="http://p.name" moz-do-not-send="true">p.name</a>
= $1</div>
<div><br>
</div>
<div>But w/o clustering on a denormalized
grandchild.grandparent FK column, as Peter showed,</div>
<div>and cluster only on grandchild.parent, that's not going
to access a mostly continuous range</div>
<div>of pages to fetch those all grandchild rows for that one
parent. But probably 10 to 50 "row-clusters",</div>
<div>given the fan-out I mentioned earlier at the child-table
level. Or am I missing something?</div>
</div>
</div>
</blockquote>
<br>
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.<br>
<br>
Rob Sargent is right, too, though: <b>practically</b> it might not
make a difference. You've got to test.<br>
<br>
<div class="moz-signature">-- <br>
Born in Arizona, moved to Babylonia.</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic