Hello!
We are tuning our installation (OpenACS 5.1 with dot
and Postgres 7.4.8 and many users)
and we are detected one slowly sql select like this:
select r.package_id,
r.folder_id as root_folder_id
from fs_root_folders r,
(select parent.item_id as folder_id
from cr_items parent,
cr_items children
where children.item_id = '7023982'
and children.tree_sortkey
between parent.tree_sortkey
and tree_right(parent.tree_sortkey)) t
where r.folder_id = t.folder_id;
The explain of this select reveals that between clause
do not use index (tree_sortkey). We are rewrite that
select like:
select r.package_id,
r.folder_id as root_folder_id
from fs_root_folders r,
(select parent.item_id as folder_id
from cr_items parent,
(select tree_ancestor_keys ((select tree_sortkey
from cr_items children
where item_id = '7023982')) as tree_sortkey)
skparents
where parent.tree_sortkey = skparents.tree_sortkey) t
where r.folder_id = t.folder_id;
Now the predicate is indexable and the result is fastest
(10x) than the original clause.
The questions are:
-Is the second select equivalent to first?
-Must we rewrite all conditions with "tree_sortkey between"
for use the index? This kind of clause is expanded around
all the OpenACS / dotLearn code.
Best regards,
Agustin
