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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
