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