Re: Slow query postgres 8.3

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.030 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.66 rows=13 width=27) (actual time=0.025..0.066 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.022..0.027 rows=1 loops=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(at)fuzzy(dot)cz [mailto:tv(at)fuzzy(dot)cz]
Sent: Saturday, April 09, 2011 3:36 AM
To: Anne Rosset
Cc: pgsql-performance(at)postgresql(dot)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