Re: Faster db architecture for a twisted table. - Mailing list pgsql-performance

From Klint Gore
Subject Re: Faster db architecture for a twisted table.
Date
Msg-id 43936CB3FB.5E5FKG@129.180.47.120
Whole thread Raw
In response to Faster db architecture for a twisted table.  (Rodrigo Madera <rodrigo.madera@gmail.com>)
List pgsql-performance
On Sat, 3 Dec 2005 23:00:21 +0000, Rodrigo Madera <rodrigo.madera@gmail.com> wrote:
> Imagine a table named Person with "first_name" and "age".
>
> Now let's make it fancy and put a "mother" and "father" field that is
> a reference to the own table (Person). And to get even fuzzier, let's
> drop in some siblings:
>
> CREATE TABLE person(
>    id bigint PRIMARY KEY,
>    first_name TEXT,
>    age INT,
>    mother bigint REFERENCES person,
>    father biging REFERENCES person,
>    siblings array of bigints  (don't remember the syntax, but you get the point)
> );
>
> Well, this is ok, but imagine a search for  "brothers of person id
> 34". We would have to search inside the record's 'siblings' array. Is
> this a bad design? is this going to be slow?

Do you need the array at all?

alter table person add column gender;

select id
>from person
where gender = 'male'
and (mother = (select mother from person where id = 34)
     OR father = (select father from person where id = 34))

You can change the OR depending if you want half brothers or not

> What would be a better design to have these kind of relationships?
> (where you need several references to rows inside the table we are).

We use that structure (without the sibiling array) for our systems.
Siblings are calculated from parents (in our case, livestock, there can
be hundreds).  You have to be prepared to use recursive functions and
make sure that a person doesnt appear anywhere higher in their family
tree.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0
Next
From: Rory Campbell-Lange
Date:
Subject: Dividing up a single 250GB RAID10 server for postgres