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 по дате отправления:

Предыдущее
От: Whatever Deep
Дата:
Сообщение:
Следующее
От: tv@fuzzy.cz
Дата:
Сообщение: Re: Slow query postgres 8.3