Tree structure index usage

I have a tree structure like:

CREATE TABLE my_tree (
id SERIAL PRIMARY KEY,
dir INTEGER references my_tree ON DELETE CASCADE ON UPDATE CASCADE,
name TEXT,
UNIQUE(name, dir)
);

Then I have a function is_parent(integer, integer), it will tell you if the second integer (id) is a sub directory of the first (regardless of the number of levels).

now obviously is_parent does some queries itself.

and someone may choose to update the dir value of a row.

is there any way I can create an efficient index on this structure/function?

The only way I have come up with, is to create an auxilliary table which basically looks like this:

CREATE TABLE my_tree_is_parent (
is_parent boolean,
dir INTEGER REFERENCES my_tree,
id INTEGER,
PRIMARY KEY (dir, id)
);

and the appropriate triggers to populate and update the table according to modifications in my_tree, however, albeit giving some performance increase (in some cases), it complicates the queries a lot and makes the whole system a lot less userfriendly.

any ideas?

Regards,

Aasmund Midttun Godal

aasmund(at)godal(dot)com - http://www.godal.com/
+47 40 45 20 46