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