I changed the FROM clause and now have the tables in the decreasing order of the number of rows they return, still no difference.
I have indexes on all joined columns on mv_assessment_hierarchy, but not on clg_assessment_result as it is an OLTP table.

Shiva.