From: Mitchell Skinner <mitch@arctur.us>
To: pgsql-performance@postgresql.org
Subject: Re: Faster db architecture for a twisted table.
Date: Sat, 03 Dec 2005 16:02:58 -0800
Message-ID: <1133654578.15792.8.camel@firebolt> (raw)
In-Reply-To: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mitch@arctur.us
Subject: Re: Faster db architecture for a twisted table.
In-Reply-To: <1133654578.15792.8.camel@firebolt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox