Re: Performance problems with 9.2.15 - Mailing list pgsql-performance

From Johan Fredriksson
Subject Re: Performance problems with 9.2.15
Date
Msg-id 1469112481.11501.12.camel@kth.se
Whole thread Raw
In response to Re: Performance problems with 9.2.15  (Johan Fredriksson <eskil@kth.se>)
Responses Re: Performance problems with 9.2.15  (Claudio Freire <klaussfreire@gmail.com>)
List pgsql-performance
> > > The rowcount estimates from 9.2 seem greatly different from the 8.4 plan.
> > > Did you remember to ANALYZE all the tables after migrating?  Maybe there
> > > were some table-specific statistics targets that you forgot to transfer
> > > over?  In any case, the 9.2 plan looks like garbage-in-garbage-out to
> > > me :-( ... without estimates at least a little closer to reality, the
> > > planner is unlikely to do anything very sane.
> > >
> > > (BTW, I wonder why you are moving only to 9.2 and not something more
> > > recent.)
> >
> > You put me on the right track with your conclusion that the estimates
> > were off the chart. The quick-and-dirty fix "DELETE FROM pg_statistic;"
> > solved this problem. This database now have to build up sane estimates
> > from scratch.
>
> Actually it took a VACUUM FULL; and DELETE FROM pg_statistic; followed
> by ANALYZE on all tables to get it right.

It worked last time, but this time it does not work. I have deleted all
data in the table pg_statistic and run ANALYZE on all tables but the
planner still make crappy optimizations. How can I adjust the estimates
to make the planner work better?

Last time it was in testing, this time it is in production, so urgent
help is needed, please!

This query now takes 90 seconds and it should not take more than 4-5
seconds.

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' AND ACL_3.ObjectId   = 85) 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') 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=8907.68..8907.76 rows=1 width=336) (actual
time=92075.721..92076.336 rows=176 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=8907.68..8907.69 rows=1 width=336) (actual
time=92075.720..92075.748 rows=607 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: 243kB
         ->  Nested Loop  (cost=20.37..8907.67 rows=1 width=336) (actual
time=540.971..92062.584 rows=607 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
               ->  Nested Loop  (cost=20.37..8845.47 rows=3 width=340)
(actual time=0.188..1204.040 rows=972439 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.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=20.37..8568.24 rows=2
width=344) (actual time=0.179..11.075 rows=688 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,
principals_1.id, cachedgroupmembers_2.memberid
                           ->  Nested Loop  (cost=20.37..8411.79 rows=41
width=8) (actual time=0.170..6.551 rows=688 loops=1)
                                 Output: principals_1.id,
cachedgroupmembers_2.memberid
                                 ->  Bitmap Heap Scan on
public.cachedgroupmembers cachedgroupmembers_2  (cost=20.37..2510.57
rows=689 width=4) (actual time=0.156..1.362 rows=689 loops=1)
                                       Output: cachedgroupmembers_2.id,
cachedgroupmembers_2.groupid, cachedgroupmembers_2.memberid,
cachedgroupmembers_2.via, cachedgroupmembers_2.immediateparentid,
cachedgroupm
embers_2.disabled
                                       Recheck Cond:
((cachedgroupmembers_2.groupid = 4) AND (cachedgroupmembers_2.disabled =
0))
                                       ->  Bitmap Index Scan on
disgroumem  (cost=0.00..20.20 rows=689 width=0) (actual
time=0.107..0.107 rows=689 loops=1)
                                             Index Cond:
((cachedgroupmembers_2.groupid = 4) AND (cachedgroupmembers_2.disabled =
0))
                                 ->  Index Scan using principals_pkey on
public.principals principals_1  (cost=0.00..8.55 rows=1 width=4) (actual
time=0.006..0.007 rows=1 loops=689)
                                       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.81 rows=1 width=336) (actual
time=0.005..0.006 rows=1 loops=688)
                                 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
                                 Index Cond: (main.id = principals_1.id)
                     ->  Index Scan using cachedgroupmembers1 on
public.cachedgroupmembers cachedgroupmembers_4  (cost=0.00..137.96
rows=65 width=8) (actual time=0.008..1.434 rows=1413 loops=688)
                           Output: cachedgroupmembers_4.id,
cachedgroupmembers_4.groupid, cachedgroupmembers_4.memberid,
cachedgroupmembers_4.via, cachedgroupmembers_4.immediateparentid,
cachedgroupmembers_4.dis
abled
                           Index Cond: (cachedgroupmembers_4.memberid =
principals_1.id)
                           Filter: (cachedgroupmembers_4.disabled = 0)
                           Rows Removed by Filter: 0
               ->  Index Only Scan using acl1 on public.acl acl_3
(cost=0.00..20.72 rows=1 width=4) (actual time=0.093..0.093 rows=0
loops=972439)
                     Output: acl_3.rightname, acl_3.objecttype,
acl_3.objectid, acl_3.principaltype, acl_3.principalid
                     Index Cond: ((acl_3.rightname = 'OwnTicket'::text)
AND (acl_3.principaltype = 'Group'::text) AND (acl_3.principalid =
cachedgroupmembers_4.groupid))
                     Filter: ((((acl_3.objecttype)::text =
'RT::Queue'::text) AND (acl_3.objectid = 85)) OR
(((acl_3.objecttype)::text = 'RT::System'::text) AND (acl_3.objectid =
1)))
                     Rows Removed by Filter: 0
                     Heap Fetches: 33532
 Total runtime: 92076.507 ms
(39 rows)



        / Eskil




pgsql-performance by date:

Previous
From: Jim Nasby
Date:
Subject: Poor choice of backward scan
Next
From: Claudio Freire
Date:
Subject: Re: Performance problems with 9.2.15