Performance problems with 9.2.15

Hello!

I am just about to upgrade from PostgreSQL 8.4.20 to 9.2.15, but I'v run
into some huge performance issues. Both databases are configured the
same way (shared_buffers = 2GB, temp_buffers = 32MB). I have increased
work_mem on the 9.2 from 4MB to 64MB, but to no avail.

Now, the query on 8.4:
rt4=# EXPLAIN ANALYZE VERBOSE SELECT DISTINCT main.* FROM Users main
CROSS JOIN ACL ACL_3 JOIN Principals Principals_1  ON ( Principals_1.id
= main.id ) JOIN CachedGroupMembers CachedGroupMembers_2  ON
( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN
CachedGroupMembers CachedGroupMembers_4  ON
( CachedGroupMembers_4.MemberId = Principals_1.id )  WHERE
((ACL_3.ObjectType = 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System'
AND ACL_3.ObjectId   = 1)) AND (ACL_3.PrincipalId =
CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND
(ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND
(CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId
= '4') AND (CachedGroupMembers_4.Disabled = '0') AND
(Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User')
AND (Principals_1.id != '1')  ORDER BY main.Name ASC;

                                                                                          

QUERY
PLAN

                                                                                          


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------
 Unique  (cost=19822.31..19843.46 rows=235 width=1084) (actual
time=6684.054..7118.015 rows=571 loops=1)
   Output: main.id, main.name, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, main.emailenc
oding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, ma
in.address2, main.city, main.state, main.zip, main.country,
main.timezone, main.pgpkey, main.creator, main.created,
main.lastupdatedby, main.lastupdated
   ->  Sort  (cost=19822.31..19822.90 rows=235 width=1084) (actual
time=6684.052..7085.835 rows=33310 loops=1)
         Output: main.id, main.name, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, main.em
ailencoding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.addres
s1, main.address2, main.city, main.state, main.zip, main.country,
main.timezone, main.pgpkey, main.creator, main.created,
main.lastupdatedby, main.lastupdated
         Sort Key: main.name, main.id, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, main.
emailencoding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.addr
ess1, main.address2, main.city, main.state, main.zip, main.country,
main.timezone, main.pgpkey, main.creator, main.created,
main.lastupdatedby, main.lastupdated
         Sort Method:  external merge  Disk: 7408kB
         ->  Hash Join  (cost=19659.66..19813.05 rows=235 width=1084)
(actual time=3362.897..4080.600 rows=33310 loops=1)
               Output: main.id, main.name, main.password,
main.authtoken, main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, m
ain.emailencoding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.
address1, main.address2, main.city, main.state, main.zip, main.country,
main.timezone, main.pgpkey, main.creator, main.created,
main.lastupdatedby, main.lastupdated
               Hash Cond: (acl_3.principalid =
cachedgroupmembers_4.groupid)
               ->  Bitmap Heap Scan on acl acl_3  (cost=30.04..145.27
rows=494 width=4) (actual time=0.339..1.790 rows=528 loops=1)
                     Output: acl_3.id, acl_3.principaltype,
acl_3.principalid, acl_3.rightname, acl_3.objecttype, acl_3.objectid,
acl_3.creator, acl_3.created, acl_3.lastupdatedby, acl_3.lastupdated
                     Recheck Cond: ((((rightname)::text =
'OwnTicket'::text) AND ((principaltype)::text = 'Group'::text)) OR
(((rightname)::text = 'SuperUser'::text) AND ((principaltype)::text =
'Group'::text)))
                     Filter: (((objecttype)::text = 'RT::Queue'::text)
OR (((objecttype)::text = 'RT::System'::text) AND (objectid = 1)))
                     ->  BitmapOr  (cost=30.04..30.04 rows=529 width=0)
(actual time=0.303..0.303 rows=0 loops=1)
                           ->  Bitmap Index Scan on acl1
(cost=0.00..25.43 rows=518 width=0) (actual time=0.283..0.283 rows=524
loops=1)
                                 Index Cond: (((rightname)::text =
'OwnTicket'::text) AND ((principaltype)::text = 'Group'::text))
                           ->  Bitmap Index Scan on acl1
(cost=0.00..4.36 rows=11 width=0) (actual time=0.020..0.020 rows=4
loops=1)
                                 Index Cond: (((rightname)::text =
'SuperUser'::text) AND ((principaltype)::text = 'Group'::text))
               ->  Hash  (cost=19615.48..19615.48 rows=1131 width=1088)
(actual time=3301.001..3301.001 rows=949843 loops=1)
                     Output: main.id, main.name, main.password,
main.authtoken, main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.l
ang, main.emailencoding, main.webencoding, main.externalcontactinfoid,
main.contactinfosystem, main.externalauthid, main.authsystem,
main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone,
 main.address1, main.address2, main.city, main.state, main.zip,
main.country, main.timezone, main.pgpkey, main.creator, main.created,
main.lastupdatedby, main.lastupdated, cachedgroupmembers_4.groupid
                     ->  Nested Loop  (cost=24.59..19615.48 rows=1131
width=1088) (actual time=0.540..1835.831 rows=949843 loops=1)
                           Output: main.id, main.name, main.password,
main.authtoken, main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname,
main.lang, main.emailencoding, main.webencoding,
main.externalcontactinfoid, main.contactinfosystem, main.externalauthid,
main.authsystem, main.gecos, main.homephone, main.workphone,
main.mobilephone, main.pager
phone, main.address1, main.address2, main.city, main.state, main.zip,
main.country, main.timezone, main.pgpkey, main.creator, main.created,
main.lastupdatedby, main.lastupdated, cachedgroupmembers_4.groupid
                           ->  Nested Loop  (cost=18.63..8795.62 rows=41
width=1092) (actual time=0.438..22.198 rows=674 loops=1)
                                 Output: main.id, main.name,
main.password, main.authtoken, main.comments, main.signature,
main.emailaddress, main.freeformcontactinfo, main.organization,
main.realname, main.nick
name, main.lang, main.emailencoding, main.webencoding,
main.externalcontactinfoid, main.contactinfosystem, main.externalauthid,
main.authsystem, main.gecos, main.homephone, main.workphone,
main.mobilephone, main
.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.pgpkey, main.creator,
main.created, main.lastupdatedby, main.lastupdated,
cachedgroupmembers_2.member
id, principals_1.id
                                 ->  Nested Loop  (cost=18.63..8459.04
rows=41 width=8) (actual time=0.381..13.384 rows=674 loops=1)
                                       Output: principals_1.id,
cachedgroupmembers_2.memberid
                                       ->  Bitmap Heap Scan on
cachedgroupmembers cachedgroupmembers_2  (cost=18.63..2436.95 rows=669
width=4) (actual time=0.308..1.973 rows=675 loops=1)
                                             Output:
cachedgroupmembers_2.id, cachedgroupmembers_2.groupid,
cachedgroupmembers_2.memberid, cachedgroupmembers_2.via,
cachedgroupmembers_2.immediateparentid, cached
groupmembers_2.disabled
                                             Recheck Cond: (groupid = 4)
                                             Filter: (disabled = 0)
                                             ->  Bitmap Index Scan on
cachedgroupmembers3  (cost=0.00..18.46 rows=669 width=0) (actual
time=0.223..0.223 rows=675 loops=1)
                                                   Index Cond: (groupid
= 4)
                                       ->  Index Scan using
principals_pkey on principals principals_1  (cost=0.00..8.99 rows=1
width=4) (actual time=0.015..0.016 rows=1 loops=675)
                                             Output: principals_1.id,
principals_1.principaltype, principals_1.objectid, principals_1.disabled
                                             Index Cond:
(principals_1.id = cachedgroupmembers_2.memberid)
                                             Filter: ((principals_1.id
<> 1) AND (principals_1.disabled = 0) AND
((principals_1.principaltype)::text = 'User'::text))
                                 ->  Index Scan using users_pkey on
users main  (cost=0.00..8.20 rows=1 width=1084) (actual
time=0.010..0.011 rows=1 loops=674)
                                       Output: main.id, main.name,
main.password, main.authtoken, main.comments, main.signature,
main.emailaddress, main.freeformcontactinfo, main.organization,
main.realname, mai
n.nickname, main.lang, main.emailencoding, main.webencoding,
main.externalcontactinfoid, main.contactinfosystem, main.externalauthid,
main.authsystem, main.gecos, main.homephone, main.workphone,
main.mobilephone
, main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.pgpkey, main.creator,
main.created, main.lastupdatedby, main.lastupdated
                                       Index Cond: (main.id =
principals_1.id)
                           ->  Bitmap Heap Scan on cachedgroupmembers
cachedgroupmembers_4  (cost=5.96..263.05 rows=68 width=8) (actual
time=0.464..2.233 rows=1409 loops=674)
                                 Output: cachedgroupmembers_4.id,
cachedgroupmembers_4.groupid, cachedgroupmembers_4.memberid,
cachedgroupmembers_4.via, cachedgroupmembers_4.immediateparentid,
cachedgroupmembers
_4.disabled
                                 Recheck Cond:
(cachedgroupmembers_4.memberid = main.id)
                                 Filter: (cachedgroupmembers_4.disabled
= 0)
                                 ->  Bitmap Index Scan on
cachedgroupmembers2  (cost=0.00..5.95 rows=68 width=0) (actual
time=0.286..0.286 rows=1410 loops=674)
                                       Index Cond:
(cachedgroupmembers_4.memberid = main.id)
 Total runtime: 7120.012 ms

Same query on 9.2:
                                                   QUERY
PLAN
                                  


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
 Unique  (cost=8922.03..8922.11 rows=1 width=348) (actual
time=259143.677..259180.526 rows=572 loops=1)
   Output: main.id, main.name, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, main.gecos, m
ain.homephone, main.workphone, main.mobilephone, main.pagerphone,
main.address1, main.address2, main.city, main.state, main.zip,
main.country, main.timezone, main.creator, main.created,
main.lastupdatedby, main.
lastupdated, main.smimecertificate
   ->  Sort  (cost=8922.03..8922.04 rows=1 width=348) (actual
time=259143.674..259145.919 rows=33209 loops=1)
         Output: main.id, main.name, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, main.ge
cos, main.homephone, main.workphone, main.mobilephone, main.pagerphone,
main.address1, main.address2, main.city, main.state, main.zip,
main.country, main.timezone, main.creator, main.created,
main.lastupdatedby,
 main.lastupdated, main.smimecertificate
         Sort Key: main.name, main.id, main.password, main.authtoken,
main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, main.
gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.creator, main.created,
main.lastupdatedb
y, main.lastupdated, main.smimecertificate
         Sort Method: quicksort  Memory: 13143kB
         ->  Nested Loop  (cost=47.83..8922.02 rows=1 width=348) (actual
time=388.225..258422.830 rows=33209 loops=1)
               Output: main.id, main.name, main.password,
main.authtoken, main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.lang, m
ain.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.creator, main.created,
main.lastupda
tedby, main.lastupdated, main.smimecertificate
               Join Filter: (cachedgroupmembers_4.groupid =
acl_3.principalid)
               Rows Removed by Join Filter: 495425041
               ->  Bitmap Heap Scan on public.acl acl_3
(cost=30.07..144.35 rows=497 width=4) (actual time=0.305..9.489 rows=525
loops=1)
                     Output: acl_3.id, acl_3.principaltype,
acl_3.principalid, acl_3.rightname, acl_3.objecttype, acl_3.objectid,
acl_3.creator, acl_3.created, acl_3.lastupdatedby, acl_3.lastupdated
                     Recheck Cond: ((((acl_3.rightname)::text =
'OwnTicket'::text) AND ((acl_3.principaltype)::text = 'Group'::text)) OR
(((acl_3.rightname)::text = 'SuperUser'::text) AND
((acl_3.principaltype):
:text = 'Group'::text)))
                     Filter: (((acl_3.objecttype)::text =
'RT::Queue'::text) OR (((acl_3.objecttype)::text = 'RT::System'::text)
AND (acl_3.objectid = 1)))
                     ->  BitmapOr  (cost=30.07..30.07 rows=531 width=0)
(actual time=0.270..0.270 rows=0 loops=1)
                           ->  Bitmap Index Scan on acl1
(cost=0.00..25.46 rows=521 width=0) (actual time=0.248..0.248 rows=521
loops=1)
                                 Index Cond: (((acl_3.rightname)::text =
'OwnTicket'::text) AND ((acl_3.principaltype)::text = 'Group'::text))
                           ->  Bitmap Index Scan on acl1
(cost=0.00..4.36 rows=11 width=0) (actual time=0.020..0.020 rows=4
loops=1)
                                 Index Cond: (((acl_3.rightname)::text =
'SuperUser'::text) AND ((acl_3.principaltype)::text = 'Group'::text))
               ->  Materialize  (cost=17.76..8740.41 rows=5 width=352)
(actual time=0.004..179.471 rows=943730 loops=525)
                     Output: main.id, main.name, main.password,
main.authtoken, main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname, main.l
ang, main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.creator, main.created,
main.la
stupdatedby, main.lastupdated, main.smimecertificate,
cachedgroupmembers_4.groupid
                     ->  Nested Loop  (cost=17.76..8740.38 rows=5
width=352) (actual time=0.436..1595.962 rows=943730 loops=1)
                           Output: main.id, main.name, main.password,
main.authtoken, main.comments, main.signature, main.emailaddress,
main.freeformcontactinfo, main.organization, main.realname,
main.nickname,
main.lang, main.gecos, main.homephone, main.workphone, main.mobilephone,
main.pagerphone, main.address1, main.address2, main.city, main.state,
main.zip, main.country, main.timezone, main.creator, main.created, m
ain.lastupdatedby, main.lastupdated, main.smimecertificate,
cachedgroupmembers_4.groupid
                           ->  Nested Loop  (cost=14.17..8325.31 rows=3
width=356) (actual time=0.392..27.201 rows=675 loops=1)
                                 Output: main.id, main.name,
main.password, main.authtoken, main.comments, main.signature,
main.emailaddress, main.freeformcontactinfo, main.organization,
main.realname, main.nick
name, main.lang, main.gecos, main.homephone, main.workphone,
main.mobilephone, main.pagerphone, main.address1, main.address2,
main.city, main.state, main.zip, main.country, main.timezone,
main.creator, main.crea
ted, main.lastupdatedby, main.lastupdated, main.smimecertificate,
principals_1.id, cachedgroupmembers_2.memberid
                                 ->  Nested Loop  (cost=14.17..8160.17
rows=43 width=8) (actual time=0.351..16.568 rows=675 loops=1)
                                       Output: principals_1.id,
cachedgroupmembers_2.memberid
                                       ->  Bitmap Heap Scan on
public.cachedgroupmembers cachedgroupmembers_2  (cost=14.17..2431.45
rows=669 width=4) (actual time=0.303..2.098 rows=676 loops=1)
                                             Output:
cachedgroupmembers_2.id, cachedgroupmembers_2.groupid,
cachedgroupmembers_2.memberid, cachedgroupmembers_2.via,
cachedgroupmembers_2.immediateparentid, cached
groupmembers_2.disabled
                                             Recheck Cond:
(cachedgroupmembers_2.groupid = 4)
                                             Filter:
(cachedgroupmembers_2.disabled = 0)
                                             ->  Bitmap Index Scan on
cachedgroupmembers2  (cost=0.00..14.00 rows=669 width=0) (actual
time=0.215..0.215 rows=676 loops=1)
                                                   Index Cond:
(cachedgroupmembers_2.groupid = 4)
                                       ->  Index Scan using
principals_pkey on public.principals principals_1  (cost=0.00..8.55
rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=676)
                                             Output: principals_1.id
                                             Index Cond:
(principals_1.id = cachedgroupmembers_2.memberid)
                                             Filter: ((principals_1.id
<> 1) AND (principals_1.disabled = 0) AND
((principals_1.principaltype)::text = 'User'::text))
                                             Rows Removed by Filter: 0
                                 ->  Index Scan using users_pkey on
public.users main  (cost=0.00..3.83 rows=1 width=348) (actual
time=0.014..0.015 rows=1 loops=675)
                                       Output: main.id, main.name,
main.password, main.authtoken, main.comments, main.signature,
main.emailaddress, main.freeformcontactinfo, main.organization,
main.realname, mai
n.nickname, main.lang, main.gecos, main.homephone, main.workphone,
main.mobilephone, main.pagerphone, main.address1, main.address2,
main.city, main.state, main.zip, main.country, main.timezone,
main.creator, mai
n.created, main.lastupdatedby, main.lastupdated, main.smimecertificate
                                       Index Cond: (main.id =
principals_1.id)
                           ->  Bitmap Heap Scan on
public.cachedgroupmembers cachedgroupmembers_4  (cost=3.59..137.70
rows=66 width=8) (actual time=0.340..2.016 rows=1398 loops=675)
                                 Output: cachedgroupmembers_4.id,
cachedgroupmembers_4.groupid, cachedgroupmembers_4.memberid,
cachedgroupmembers_4.via, cachedgroupmembers_4.immediateparentid,
cachedgroupmembers
_4.disabled
                                 Recheck Cond:
(cachedgroupmembers_4.memberid = principals_1.id)
                                 Filter: (cachedgroupmembers_4.disabled
= 0)
                                 Rows Removed by Filter: 0
                                 ->  Bitmap Index Scan on
cachedgroupmembers1  (cost=0.00..3.58 rows=66 width=0) (actual
time=0.210..0.210 rows=1398 loops=675)
                                       Index Cond:
(cachedgroupmembers_4.memberid = principals_1.id)
 Total runtime: 259230.400 ms


The 9.2 chose "nesed loop" in stead of "hash join" with a massive
penalty of 250 seconds in stead of 7-8 seconds.

I have tried to set "enable_nestloop = off" and then this query is down
to 4-5 seconds, but all other queries are slower.

How can I make PostgreSQL 9.2 run this query just as fast as 8.4 did?


          / Eskil