Slow query postgres 8.3

Hi,

I am trying to tune a query that is taking too much time on a large
dataset (postgres 8.3).

SELECT DISTINCT

role_user.project_id AS projectId,

sfuser.username AS adminUsername,

sfuser.full_name AS adminFullName

FROM

role_operation role_operation,

role role,

sfuser sfuser,

role_user role_user

WHERE

role_operation.role_id=role.id

AND role.id=role_user.role_id

AND role_user.user_id=sfuser.id

AND role_operation.object_type_id='SfMain.Project'

AND role_operation.operation_category='admin'

AND role_operation.operation_name='admin'

ORDER BY

adminFullName ASC

It has the following query plan:

QUERY PLAN

------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------------------------------

Unique (cost=1218.57..1221.26 rows=269 width=35) (actual
time=16700.332..17212.849 rows=30136 loops=1)

-> Sort (cost=1218.57..1219.24 rows=269 width=35) (actual
time=16700.306..16885.972 rows=41737 loops=1)

Sort Key: sfuser.full_name, role_user.project_id,
sfuser.username

Sort Method: quicksort Memory: 4812kB

-> Nested Loop (cost=0.00..1207.71 rows=269 width=35) (actual
time=71.173..15788.798 rows=41737 loops=1)

-> Nested Loop (cost=0.00..1118.22 rows=269 width=18)
(actual time=65.550..12440.383 rows=41737 loops=1)

-> Nested Loop (cost=0.00..256.91 rows=41
width=18) (actual time=19.312..7150.925 rows=6108 loops=1)

-> Index Scan using role_oper_obj_oper on
role_operation (cost=0.00..85.15 rows=41 width=9) (actual
time=19.196..2561.765 rows=6108 loops=1)

Index Cond: (((object_type_id)::text =
'SfMain.Project'::text) AND ((operation_category)::text = 'admin'::text)
AND ((operation_name)::text = 'admin'::text))

-> Index Scan using role_pk on role
(cost=0.00..4.18 rows=1 width=9) (actual time=0.727..0.732 rows=1
loops=6108)

Index Cond: ((role.id)::text =
(role_operation.role_id)::text)

-> Index Scan using role_user_proj_idx on
role_user (cost=0.00..20.84 rows=13 width=27) (actual time=0.301..0.795
rows=7 loops=6108)

Index Cond: ((role_user.role_id)::text =
(role_operation.role_id)::text)

-> Index Scan using sfuser_pk on sfuser
(cost=0.00..0.32 rows=1 width=35) (actual time=0.056..0.062 rows=1
loops=41737)

Index Cond: ((sfuser.id)::text =
(role_user.user_id)::text)

Total runtime: 17343.185 ms

(16 rows)

I have tried adding an index on role_operation.role_id but it didn't
seem to help or changing the query to:

SELECT
role_user.project_id AS projectId,
sfuser.username AS adminUsername,
sfuser.full_name AS adminFullName
FROM
sfuser sfuser,
role_user role_user
WHERE
role_user.role_id in (select role_operation.role_id from
role_operation where role_operation.object_type_id=
'SfMain.Project'
AND role_operation.operation_category='admin'
AND role_operation.operation_name='admin') AND
role_user.user_id=sfuser.id

ORDER BY
adminFullName ASC

None of this seemed to improve the performance.

Does anyone have a suggestion?

Thanks a lot,

Anne