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)

those left joins are killing that query, find a way to re-write the query for pg, or better, use evaluation pkg!