Slow query postgres 8.3
От | Anne Rosset |
---|---|
Тема | Slow query postgres 8.3 |
Дата | |
Msg-id | 945629628BB0174D86709AFE6D1CDEF5017A66C1@SP-EXCHMBC.sp.corp.collab.net обсуждение исходный текст |
Ответы |
Re: Slow query postgres 8.3
(tv@fuzzy.cz)
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: