Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
Re: Tom Lane 2013-05-06 1583.1367858...@sss.pgh.pa.us The newer rowcount estimates are much further away from reality: Unique (cost=1117.67..1118.46 rows=9 width=1115) (actual time=82.646..85.695 rows=439 loops=1) Unique (cost=784205.94..796940.08 rows=145533 width=1061) (actual time=9710.683..9713.175 rows=439 loops=1) Has the new DB been analyzed? Maybe you had custom stats targets in the old DB that didn't get copied to the new one? The new DB was analyzed with various stats targets, including values that were higher that anything we would have used in 8.1. I don't think we had per-table settings in there (the actual DB is now gone for good). Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
Re: Mark Felder 2013-05-13 op.ww1gv9fd34t...@markf.office.supranet.net What version of DBIx-SearchBuilder do you have on that server? The RT guys usually recommend you have the latest possible so RT is performing the most sane/optimized queries possible for your database. I honestly don't know if it will make a difference for you, but it's worth a shot. That's a never touch a running system kind of machine there, we are happy that they let us finally upgrade at least the PostgreSQL part of the setup, so changing any perl libs there is out of the question. The version is libdbix-searchbuilder-perl 1.26-1 from Debian Sarge/3.1 *cough*. Christoph -- c...@df7cb.de | http://www.df7cb.de/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
On Tue, May 14, 2013 at 11:52:29PM -0700, Christoph Berg wrote: Re: Mark Felder 2013-05-13 op.ww1gv9fd34t...@markf.office.supranet.net What version of DBIx-SearchBuilder do you have on that server? The RT guys usually recommend you have the latest possible so RT is performing the most sane/optimized queries possible for your database. I honestly don't know if it will make a difference for you, but it's worth a shot. That's a never touch a running system kind of machine there, we are happy that they let us finally upgrade at least the PostgreSQL part of the setup, so changing any perl libs there is out of the question. The version is libdbix-searchbuilder-perl 1.26-1 from Debian Sarge/3.1 *cough*. Christoph -- Hi Christoph, I understand the sentiment but you really should consider upgrading. I think the current release is 1.63 and since it is the DB interface it could have a positive effect on your problem not to mention that they do fix bugs. :) Regards, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
On Mon, May 13, 2013 at 4:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, May 13, 2013 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: You know, of course, that the join size estimate isn't arrived at that way. Still, this point does make it seem more like a planner bug and less like bad input stats. It would be nice to see a self-contained example ... Yeah, I remember there have been examples like this that have come up before. Unfortunately, I haven't fully grokked what's actually going on here that allows this kind of thing to happen. Refresh my memory on where the relevant code is? The point is that we estimate the size of a joinrel independently of any particular input paths for it, and indeed before we've built any such paths. So this seems like a bug somewhere in selectivity estimation, but I'm not prepared to speculate as to just where. Hmm. I went looking for the relevant code and found calc_joinrel_size_estimate(). If that's actually the right place to be looking, it's hard to escape the conclusion that pselec 1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
On Tue, Apr 30, 2013 at 7:20 AM, Christoph Berg christoph.b...@credativ.de wrote: - Nested Loop (cost=24.57..844.83 rows=62335 width=4) (actual time=0.109..0.633 rows=23 loops=1) - Bitmap Heap Scan on acl acl_2 (cost=8.90..61.36 rows=33 width=10) (actual time=0.070..0.112 rows=22 loops=1) Recheck Cond: rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::System'::text)) OR (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::Queue'::text) AND (objectid = 10))) - BitmapOr (cost=8.90..8.90 rows=35 width=0) (actual time=0.064..0.064 rows=0 loops=1) - Bitmap Index Scan on acl1 (cost=0.00..4.47 rows=22 width=0) (actual time=0.036..0.036 rows=8 loops=1) Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::System'::text)) - Bitmap Index Scan on acl1 (cost=0.00..4.41 rows=13 width=0) (actual time=0.026..0.026 rows=14 loops=1) Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::Queue'::text) AND (objectid = 10)) - Bitmap Heap Scan on groups groups_3 (cost=15.67..23.73 rows=1 width=30) (actual time=0.022..0.023 rows=1 loops=22) Recheck Cond: ((acl_2.principalid = id) OR type)::text = (acl_2.principaltype)::text) AND (instance = 10) AND ((domain)::text = 'RT::Queue-Role'::text)) OR (((type)::text = (acl_2.principaltype)::text) AND (instance = 999028) AND ((domain)::text = 'RT::Ticket-Role'::text Filter: domain)::text = 'SystemInternal'::text) OR ((domain)::text = 'UserDefined'::text) OR ((domain)::text = 'ACLEquivalence'::text) OR (((domain)::text = 'RT::Queue-Role'::text) AND (instance = 10)) OR (((domain)::text = 'RT::Ticket-Role'::text) AND (instance = 999028))) AND (((acl_2.principalid = id) AND ((acl_2.principaltype)::text = 'Group'::text) AND (((domain)::text = 'SystemInternal'::text) OR ((domain)::text = 'UserDefined'::text) OR ((domain)::text = 'ACLEquivalence'::text))) OR (domain)::text = 'RT::Queue-Role'::text) AND (instance = 10)) OR (((domain)::text = 'RT::Ticket-Role'::text) AND (instance = 999028))) AND ((type)::text = (acl_2.principaltype)::text - BitmapOr (cost=15.67..15.67 rows=2 width=0) (actual time=0.019..0.019 rows=0 loops=22) - Bitmap Index Scan on groups_pkey (cost=0.00..4.76 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=22) Index Cond: (acl_2.principalid = id) - BitmapOr (cost=10.66..10.66 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=22) - Bitmap Index Scan on groups2 (cost=0.00..5.33 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=22) Index Cond: (((type)::text = (acl_2.principaltype)::text) AND (instance = 10) AND ((domain)::text = 'RT::Queue-Role'::text)) - Bitmap Index Scan on groups2 (cost=0.00..5.33 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=22) Index Cond: (((type)::text = (acl_2.principaltype)::text) AND (instance = 999028) AND ((domain)::text = 'RT::Ticket-Role'::text)) The planner is estimating this the outer side of this nested loop will produce 33 rows and that the inner side will produce 1. One would assume that the row estimate for the join product couldn't be more than 33 * 1 = 33 rows, but the planner is estimating 62335 rows, which seems like nonsense. The actual result cardinality is 23. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
On Tue, 30 Apr 2013 06:20:55 -0500, Christoph Berg christoph.b...@credativ.de wrote: Hi, this is more of a report than a question, because we thought this would be interesting to share. We recently (finally) migrated an Request Tracker 3.4 database running on 8.1.19 to 9.2.4. The queries used by rt3.4 are sometimes weird, but 8.1 coped without too much tuning. The schema looks like this: What version of DBIx-SearchBuilder do you have on that server? The RT guys usually recommend you have the latest possible so RT is performing the most sane/optimized queries possible for your database. I honestly don't know if it will make a difference for you, but it's worth a shot. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
Robert Haas robertmh...@gmail.com writes: The planner is estimating this the outer side of this nested loop will produce 33 rows and that the inner side will produce 1. One would assume that the row estimate for the join product couldn't be more than 33 * 1 = 33 rows, but the planner is estimating 62335 rows, which seems like nonsense. You know, of course, that the join size estimate isn't arrived at that way. Still, this point does make it seem more like a planner bug and less like bad input stats. It would be nice to see a self-contained example ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
On Mon, May 13, 2013 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: The planner is estimating this the outer side of this nested loop will produce 33 rows and that the inner side will produce 1. One would assume that the row estimate for the join product couldn't be more than 33 * 1 = 33 rows, but the planner is estimating 62335 rows, which seems like nonsense. You know, of course, that the join size estimate isn't arrived at that way. Still, this point does make it seem more like a planner bug and less like bad input stats. It would be nice to see a self-contained example ... Yeah, I remember there have been examples like this that have come up before. Unfortunately, I haven't fully grokked what's actually going on here that allows this kind of thing to happen. Refresh my memory on where the relevant code is? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
Robert Haas robertmh...@gmail.com writes: On Mon, May 13, 2013 at 4:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: You know, of course, that the join size estimate isn't arrived at that way. Still, this point does make it seem more like a planner bug and less like bad input stats. It would be nice to see a self-contained example ... Yeah, I remember there have been examples like this that have come up before. Unfortunately, I haven't fully grokked what's actually going on here that allows this kind of thing to happen. Refresh my memory on where the relevant code is? The point is that we estimate the size of a joinrel independently of any particular input paths for it, and indeed before we've built any such paths. So this seems like a bug somewhere in selectivity estimation, but I'm not prepared to speculate as to just where. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
Hi, this is more of a report than a question, because we thought this would be interesting to share. We recently (finally) migrated an Request Tracker 3.4 database running on 8.1.19 to 9.2.4. The queries used by rt3.4 are sometimes weird, but 8.1 coped without too much tuning. The schema looks like this: http://bestpractical.com/rt/3.4-schema.png One query that took about 80ms on 8.1.19 took 8s on 9.2.4: SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL ACL_2, Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE ((ACL_2.RightName = 'OwnTicket')) AND ((CachedGroupMembers_4.MemberId = Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND ((Principals_1.Disabled = '0') OR (Principals_1.Disabled = '0')) AND ((Principals_1.id != '1')) AND ((main.id = Principals_1.id)) AND ( ( ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = 'Group' AND ( Groups_3.Domain = 'SystemInternal' OR Groups_3.Domain = 'UserDefined' OR Groups_3.Domain = 'ACLEquivalence')) OR ( ( (Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = 10) OR ( Groups_3.Domain = 'RT::Ticket-Role' AND Groups_3.Instance = 999028) ) AND Groups_3.Type = ACL_2.PrincipalType) ) AND (ACL_2.ObjectType = 'RT::System' OR (ACL_2.ObjectType = 'RT::Queue' AND ACL_2.ObjectId = 10) ) ORDER BY main.Name ASC; 8.1 plan: (http://explain.depesz.com/s/gZ6) Unique (cost=1117.67..1118.46 rows=9 width=1115) (actual time=82.646..85.695 rows=439 loops=1) - Sort (cost=1117.67..1117.70 rows=9 width=1115) (actual time=82.645..82.786 rows=1518 loops=1) Sort Key: main.name, main.id, main.password, 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.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated - Nested Loop (cost=10.51..1117.53 rows=9 width=1115) (actual time=0.205..23.688 rows=1518 loops=1) - Nested Loop (cost=10.51..1087.81 rows=9 width=1119) (actual time=0.193..13.495 rows=1600 loops=1) - Nested Loop (cost=10.51..1060.15 rows=9 width=4) (actual time=0.175..3.307 rows=1635 loops=1) - Nested Loop (cost=10.51..536.13 rows=4 width=4) (actual time=0.161..1.057 rows=23 loops=1) Join Filter: (((outer.principalid = inner.id) AND ((outer.principaltype)::text = 'Group'::text) AND (((inner.domain)::text = 'SystemInternal'::text) OR ((inner.domain)::text = 'UserDefined'::text) OR ((inner.domain)::text = 'ACLEquivalence'::text))) OR (inner.domain)::text = 'RT::Queue-Role'::text) AND (inner.instance = 10)) OR (((inner.domain)::text = 'RT::Ticket-Role'::text) AND (inner.instance = 999028))) AND ((inner.type)::text = (outer.principaltype)::text))) - Bitmap Heap Scan on acl acl_2 (cost=4.24..61.15 rows=33 width=13) (actual time=0.107..0.141 rows=22 loops=1) Recheck Cond: rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::System'::text)) OR (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::Queue'::text) AND (objectid = 10))) - BitmapOr (cost=4.24..4.24 rows=34 width=0) (actual time=0.097..0.097 rows=0 loops=1) - Bitmap Index Scan on acl1 (cost=0.00..2.13 rows=22 width=0) (actual time=0.054..0.054 rows=8 loops=1) Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::System'::text)) - Bitmap Index Scan on acl1 (cost=0.00..2.11 rows=13 width=0) (actual time=0.041..0.041 rows=14 loops=1) Index Cond: (((rightname)::text = 'OwnTicket'::text) AND ((objecttype)::text = 'RT::Queue'::text) AND (objectid = 10)) - Bitmap Heap Scan on groups groups_3 (cost=6.27..14.32 rows=2 width=36) (actual time=0.036..0.036 rows=1 loops=22) Recheck Cond: ((outer.principalid = groups_3.id) OR groups_3.type)::text = (outer.principaltype)::text) AND (groups_3.instance = 10) AND ((groups_3.domain)::text = 'RT::Queue-Role'::text)) OR (((groups_3.type)::text = (outer.principaltype)::text) AND (groups_3.instance = 999028) AND ((groups_3.domain)::text = 'RT::Ticket-Role'::text Filter: (((domain)::text =
Re: [PERFORM] RT3.4 query needed a lot more tuning with 9.2 than it did with 8.1
Christoph Berg christoph.b...@credativ.de writes: We recently (finally) migrated an Request Tracker 3.4 database running on 8.1.19 to 9.2.4. The queries used by rt3.4 are sometimes weird, but 8.1 coped without too much tuning. The schema looks like this: The newer rowcount estimates are much further away from reality: Unique (cost=1117.67..1118.46 rows=9 width=1115) (actual time=82.646..85.695 rows=439 loops=1) Unique (cost=784205.94..796940.08 rows=145533 width=1061) (actual time=9710.683..9713.175 rows=439 loops=1) Has the new DB been analyzed? Maybe you had custom stats targets in the old DB that didn't get copied to the new one? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
