Index on parent/child hierarchy

From: Jason Armstrong <ja(at)riverdrums(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Index on parent/child hierarchy
Date: 2012-01-25 11:54:19
Message-ID: CAF2ce0pwqYLuaxphXMMMET8dyuxYSY3+RaHosv7qLN6+GQPniQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I'm looking for advice on the best way to index a table that is defined as:

create table uuid.master(id uuid, parent uuid references
uuid.master(id), type_id smallint, primary key(id));

Besides the primary key, I have these two indices on the table too:
CREATE INDEX master_parent_idx ON uuid.master(parent);
CREATE INDEX master_type_idx ON uuid.master(type_id);

I have data arranged in four levels (ie type_id is from 1 to 4):

1. id=A type_id=1
2. id=B parent=A type_id=2
3. id=C parent=B type_id=3
4. id=D parent=C type_id=4
2. id=E parent=A type_id=2
3. id=F parent=E type_id=3
4. id=G parent=F type_id=4
4. id=H parent=F type_id=4
4. id=I parent=F type_id=4
3. id=J parent=E type_id=3
4. id=K parent=J type_id=4

I want to count all type_id=4 for a particular type_id=1 uuid.

I use this query:

SELECT count(t4.id)
FROM uuid.master AS t4
INNER JOIN uuid.master AS t3 ON t4.parent=t3.id
INNER JOIN uuid.master AS t2 ON t3.parent=t2.id
INNER JOIN uuid.master AS t1 ON t2.parent=t1.id
WHERE t1.id=UUID

Apart from creating a separate table to keep track of the counts, is
there a good way to index the table to help?

Regards,

--
Jason Armstrong

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jasen Betts 2012-01-25 12:01:10 Re: Numerous prepared transactions?
Previous Message Jasen Betts 2012-01-25 11:52:06 Re: Duplicated entries are not ignored even if a "do instead nothing" rule is added.