You need to analyze the query first of all.
use
select explain analyze .... query ...
in psql.
Read the extensive advice here.
https://openacs.org/xowiki/postgresql-admin
If this is not the only slow query you have, but other complex queries are also slow, you might need to tune PostgreSQL itself as well. There is additional advice on that page for that, but first analyze the query.
I know in older versions of assessment there were missing indexes but I don't know if they affected this query. You might check the latest code in CVS if you are not running dotlrn 2.3.1
