Subject: Re: Faster db architecture for a twisted table.
From: Mitchell Skinner <mitch@arctur.us>
To: pgsql-performance@postgresql.org
In-Reply-To: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
Content-Type: text/plain
Date: Sat, 03 Dec 2005 16:02:58 -0800
Message-Id: <1133654578.15792.8.camel@firebolt>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Sat, 2005-12-03 at 23:00 +0000, Rodrigo Madera wrote:
> 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?

Well, I don't know how close this example is to your actual problem, but
the siblings array is redundant, AFAICS.  If you got rid of it, you
could query for full sibling brothers with something like (not tested):

select bro.* from
        person p inner join person bro
        on (p.mother = bro.mother)
           AND (p.father = bro.father)
where
        bro.sex='M' and p.id=34

...assuming you added a "sex" field, which you would need in any case to
query for brothers.

You could query for half-siblings by changing the AND into an OR, I
think.

Mitch