Re: Performance problems with 9.2.15

От: Johan Fredriksson
Тема: Re: Performance problems with 9.2.15
Дата: ,
Msg-id: 1469112481.11501.12.camel@kth.se
(см: обсуждение, исходный текст)
Ответ на: Re: Performance problems with 9.2.15  (Johan Fredriksson)
Ответы: Re: Performance problems with 9.2.15  (Claudio Freire)
Список: pgsql-performance

Скрыть дерево обсуждения

Performance problems with 9.2.15  (Johan Fredriksson, )
 Re: Performance problems with 9.2.15  (Tom Lane, )
  Re: Performance problems with 9.2.15  (Johan Fredriksson, )
   Re: Performance problems with 9.2.15  (Steve Crawford, )
  Re: Performance problems with 9.2.15  (Johan Fredriksson, )
   Re: Performance problems with 9.2.15  (Johan Fredriksson, )
    Re: Performance problems with 9.2.15  (Johan Fredriksson, )
     Re: Performance problems with 9.2.15  (Claudio Freire, )
      Re: Performance problems with 9.2.15  ("David G. Johnston", )
       Re: Performance problems with 9.2.15  (Claudio Freire, )
        Re: Performance problems with 9.2.15  (Johan Fredriksson, )
         Re: Performance problems with 9.2.15  (Johan Fredriksson, )
          Re: Performance problems with 9.2.15  (Mark Kirkwood, )
           Re: Performance problems with 9.2.15  (Johan Fredriksson, )

> > > 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 по дате сообщения:

От: "David G. Johnston"
Дата:
Сообщение: Re: Performance problems with 9.2.15
От: Claudio Freire
Дата:
Сообщение: Re: Performance problems with 9.2.15