Forum .LRN Q&A: Very slow query in dotlrn-homeworks (5 seconds)

Hello, all!

I have detected a very slow select when I display
the dotlrn-homework portlet. It get near 5 seconds
with a AMD Opteron CPU. The select and the explain
is below. I am working with Postgres 7.4.5 in Linux.

Any help to decrease the time of this select?

Best regards,
Agustin

--------------------SELECT
select
o.object_id,
coalesce(f.label, fs_tree.name) as name,
fs_tree.live_revision as version_id,
fs_tree.content_type,
r.content_length, r.title,
fs_tree.parent_id as folder_id,
lpad(' ',(tree_level(fs_tree.tree_sortkey) - 1), ' ') as spaces,
rels.related_object_id as homework_file_id,
p.first_names || ' ' || p.last_name as file_owner_name,
o.creation_user
from
(select cr_items.*
from cr_items, cr_items as cr_items2
where cr_items2.item_id in (5745352)
and cr_items.tree_sortkey between
cr_items2.tree_sortkey and tree_right(cr_items2.tree_sortkey)
and tree_level(cr_items.tree_sortkey) > '1'
and tree_level(cr_items.tree_sortkey) <= '1' + 1) fs_tree
join acs_objects o on (o.object_id = fs_tree.item_id)
left join cr_folders f on (f.folder_id = fs_tree.item_id)
left join persons p on (p.person_id = o.creation_user)
left join cr_revisions r on (r.revision_id = fs_tree.latest_revision)
left join cr_item_rels rels on
(rels.item_id = o.object_id and rels.relation_tag = 'homework_correction')
where not exists (select 1
from cr_item_rels
where related_object_id = o.object_id
and relation_tag = 'homework_correction')

order by content_item__get_path(fs_tree.item_id, null)
;

---EXPLAIN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1770.61..1770.66 rows=20 width=184)
Sort Key: content_item__get_path(cr_items.item_id, NULL::integer)
-> Hash Left Join (cost=1.06..1770.18 rows=20 width=184)
Hash Cond: ("outer".object_id = "inner".item_id)
-> Merge Left Join (cost=0.00..1768.71 rows=20 width=180)
Merge Cond: ("outer".latest_revision = "inner".revision_id)
-> Nested Loop Left Join (cost=0.00..14972.84 rows=20 width=156)
-> Nested Loop Left Join (cost=0.00..14904.03 rows=20 width=128)
-> Nested Loop (cost=0.00..14802.69 rows=20 width=85)
-> Nested Loop (cost=0.00..14610.56 rows=40 width=77)
Join Filter: (("outer".tree_sortkey >= "inner".tree_sortkey) AND ("outer".tree_sortkey <= tree_right("inner".tree_sortkey)))
-> Index Scan using cr_items_by_latest_revision on cr_items (cost=0.00..13331.08 rows=359 width=77)
Filter: ((tree_level(tree_sortkey) > 1) AND (tree_level(tree_sortkey) <= 2))
-> Index Scan using cr_items_pk on cr_items cr_items2 (cost=0.00..3.55 rows=1 width=14)
Index Cond: (item_id = 5745352)
-> Index Scan using acs_objects_pk on acs_objects o (cost=0.00..4.79 rows=1 width=8)
Index Cond: (o.object_id = "outer".item_id)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on cr_item_rels (cost=0.00..1.06 rows=1 width=0)
Filter: ((related_object_id = $0) AND ((relation_tag)::text = 'homework_correction'::text))
-> Index Scan using cr_folders_pk on cr_folders f (cost=0.00..5.05 rows=1 width=47)
Index Cond: (f.folder_id = "outer".item_id)
-> Index Scan using persons_pk on persons p (cost=0.00..3.43 rows=1 width=32)
Index Cond: (p.person_id = "outer".creation_user)
-> Index Scan using cr_revisions_pk on cr_revisions r (cost=0.00..260.85 rows=4221 width=32)
-> Hash (cost=1.05..1.05 rows=4 width=8)
-> Seq Scan on cr_item_rels rels (cost=0.00..1.05 rows=4 width=8)
Filter: ((relation_tag)::text = 'homework_correction'::text)
(29 rows)