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