From: | "Dai, Tino" <tdai(at)loc(dot)gov> |
---|---|
To: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Disparity between 8.1.18 and 8.2.14 performance wise |
Date: | 2010-03-22 21:58:41 |
Message-ID: | 1CA7FF980DA3824F9A5C31532B7A40DCC4B87E10@LCXCLMB01.LCDS.LOC.GOV |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
After many days of googling and referring to different web pages about performance, I'm
turning to this list for help. We have a third party application that is running on 8.1.11 and the
vendor has told us not to upgrade the database to 8.2.
I have gone with the default values in postgresql.conf of 8.1.18 (the 8.1.11 was unavailable
from the postgresql site so this is the closest one) and 8.2.14. I execute the same query with
EXPLAIN ANALYZE on 8.1.18 and the 8.2.14. The 8.1.18 comes back with about 1000ms -1100ms
while the 8.2.14 comes back with 5ms - 6ms.
I have tried up the work_mem, effective_cache_size, and the shared_buffers with no noticeable effect.
I'm not looking for a magic wand to allow 8.1.18 to become 8.2.14, but some performance gain would be
nice. Can any shed some light on this?
Thanks,
Tino
HashAggregate (cost=995.99..996.01 rows=1 width=66) (actual time=1042.850..1042.892 rows=21 loops=1)
-> Hash Join (cost=22.99..995.99 rows=1 width=66) (actual time=204.591..1042.745 rows=21 loops=1)
Hash Cond: ("outer".user_project_id = "inner".user_project_id)
-> Nested Loop (cost=2.03..974.97 rows=8 width=70) (actual time=6.976..969.505 rows=52202 loops=1)
-> Seq Scan on role_setting (cost=0.00..964.50 rows=1 width=70) (actual time=0.036..121.443 rows=43833 loops=1)
Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
-> Bitmap Heap Scan on user_project_role (cost=2.03..10.38 rows=8 width=8) (actual time=0.009..0.011 rows=1 loops=43833)
Recheck Cond: ("outer".role_id = user_project_role.role_id)
-> Bitmap Index Scan on userprojectrole_roleiduserprojectid (cost=0.00..2.03 rows=8 width=0) (actual time=0.006..0.006 rows=1 loops=43833)
Index Cond: ("outer".role_id = user_project_role.role_id)
-> Hash (cost=20.96..20.96 rows=3 width=4) (actual time=0.365..0.365 rows=1 loops=1)
-> Bitmap Heap Scan on user_project (cost=11.99..20.96 rows=3 width=4) (actual time=0.341..0.347 rows=1 loops=1)
Recheck Cond: (project_id = 67)
Filter: ((user_id = 102) OR (hashed subplan))
-> Bitmap Index Scan on user_project_pkey (cost=0.00..10.77 rows=7 width=0) (actual time=0.207..0.207 rows=6 loops=1)
Index Cond: (project_id = 67)
SubPlan
-> Seq Scan on usergroup_user (cost=0.00..1.21 rows=1 width=4) (actual time=0.020..0.036 rows=1 loops=1)
Filter: (member_user_id = 102)
Total runtime: 1043.493 ms
HashAggregate (cost=77.51..77.52 rows=1 width=20) (actual time=6.172..6.217 rows=21 loops=1)
-> Nested Loop (cost=34.15..77.50 rows=1 width=20) (actual time=1.972..6.106 rows=21 loops=1)
-> Hash Join (cost=34.15..64.18 rows=6 width=4) (actual time=1.884..5.847 rows=1 loops=1)
Hash Cond: (user_project_role.user_project_id = user_project.user_project_id)
-> Seq Scan on user_project_role (cost=0.00..23.98 rows=1598 width=8) (actual time=0.028..2.349 rows=1598 loops=1)
-> Hash (cost=34.09..34.09 rows=5 width=4) (actual time=0.752..0.752 rows=1 loops=1)
-> Seq Scan on user_project (cost=1.21..34.09 rows=5 width=4) (actual time=0.327..0.744 rows=1 loops=1)
Filter: ((project_id = 67) AND ((user_id = 102) OR (hashed subplan)))
SubPlan
-> Seq Scan on usergroup_user (cost=0.00..1.21 rows=1 width=4) (actual time=0.050..0.056 rows=1 loops=1)
Filter: (member_user_id = 102)
-> Index Scan using role_setting_pkey on role_setting (cost=0.00..2.21 rows=1 width=24) (actual time=0.081..0.194 rows=21 loops=1)
Index Cond: (role_setting.role_id = user_project_role.role_id)
Filter: (((section)::text = (section)::text) AND (ref_id = ref_id))
Total runtime: 6.905 ms
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-03-22 22:05:44 | Re: Bad encoded chars in being inserted into database |
Previous Message | Gabriele Bartolini | 2010-03-22 21:50:42 | Re: Bad encoded chars in being inserted into database |