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
thefirst (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
givingsome 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@godal.com - http://www.godal.com/
+47 40 45 20 46