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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin