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
