Re: Slow query postgres 8.3
От | Anne Rosset |
---|---|
Тема | Re: Slow query postgres 8.3 |
Дата | |
Msg-id | 945629628BB0174D86709AFE6D1CDEF5017A66E3@SP-EXCHMBC.sp.corp.collab.net обсуждение исходный текст |
Ответ на | Re: Slow query postgres 8.3 (tv@fuzzy.cz) |
Ответы |
Re: Slow query postgres 8.3
("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
|
Список | pgsql-performance |
Hi Thomas, Here is the plan after explain. QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=1330.27..1333.24 rows=297 width=35) (actual time=4011.861..4526.583 rows=30136 loops=1) -> Sort (cost=1330.27..1331.01 rows=297 width=35) (actual time=4011.828..4198.006 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..1318.07 rows=297 width=35) (actual time=0.622..3107.994 rows=41737 loops=1) -> Nested Loop (cost=0.00..1219.26 rows=297 width=18) (actual time=0.426..1212.175 rows=41737 loops=1) -> Nested Loop (cost=0.00..282.11 rows=45 width=18) (actual time=0.325..371.295 rows=6108 loops=1) -> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..93.20 rows=45 width=9)(actual time=0.236..71.291 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.19 rows=1 width=9) (actual time=0.025..0.030rows=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.66 rows=13 width=27) (actual time=0.025..0.066rows=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.022..0.027 rows=1loops=41737) Index Cond: ((sfuser.id)::text = (role_user.user_id)::text) Total runtime: 4657.488 ms (16 rows) Is there anything that can be done. For instance for the 1s in the index scan on sfuser? Thanks, Anne -----Original Message----- From: tv@fuzzy.cz [mailto:tv@fuzzy.cz] Sent: Saturday, April 09, 2011 3:36 AM To: Anne Rosset Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] 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). > Hi, run ANALYZE on the tables used in the query - the stats are very off, so the db chooses a really bad execution plan. Tomas
В списке pgsql-performance по дате отправления:
Предыдущее
От: Scott MarloweДата:
Сообщение: Re: Multiple index builds on same table - in one sweep?
Следующее
От: Scott MarloweДата:
Сообщение: Re: Multiple index builds on same table - in one sweep?