Thread: Estimation issue with partitioned tables
All, I'm seeing in a production database two problems with query rowcount estimation: (1) Estimates for the number of rows in an outer join do not take into account any constraint exclusion (CE) in operation. (2) Row estimates do not take into account if the unique indexes on the child partitions are different from the master partition (the append node). This is often true, because the key to the master is ( key, ce column) and for the children is just ( key ). The result is that if you do a series of outer joins using the CE criterion against partitioned tables, the row estimates you get will be several orders of magnitude too high ... and the subsequent query plan far too pessimistic. Anyone else seeing this? Do any of the 9.0 patches address the above issues? --Josh Berkus
On Sun, Feb 28, 2010 at 3:19 PM, Josh Berkus <josh@agliodbs.com> wrote: > All, > > I'm seeing in a production database two problems with query rowcount > estimation: > > (1) Estimates for the number of rows in an outer join do not take into > account any constraint exclusion (CE) in operation. > > (2) Row estimates do not take into account if the unique indexes on the > child partitions are different from the master partition (the append > node). This is often true, because the key to the master is ( key, ce > column) and for the children is just ( key ). > > The result is that if you do a series of outer joins using the CE > criterion against partitioned tables, the row estimates you get will be > several orders of magnitude too high ... and the subsequent query plan > far too pessimistic. > > Anyone else seeing this? Do any of the 9.0 patches address the above > issues? I feel like I've seen these way-too-high row estimates in some other postings to -performance, but I'm not sure if it was the same issue. You don't by chance have a RTC? I don't think it's likely fixed in 9.0 but it would be interesting to investigate. ...Robert
> I feel like I've seen these way-too-high row estimates in some other > postings to -performance, but I'm not sure if it was the same issue. > You don't by chance have a RTC? I don't think it's likely fixed in 9.0 > but it would be interesting to investigate. Yeah, I can generate one pretty easily; the behavior is readily observable and repeatable. Will get on it RSN, but at you said, we're not doing anything about it for 9.0. I've a feeling that this will become one of the list of issues to be fixed with 'real partitioning'. --Josh Berkus
On Wed, Mar 3, 2010 at 4:45 PM, Josh Berkus <josh@agliodbs.com> wrote: >> I feel like I've seen these way-too-high row estimates in some other >> postings to -performance, but I'm not sure if it was the same issue. >> You don't by chance have a RTC? I don't think it's likely fixed in 9.0 >> but it would be interesting to investigate. > > Yeah, I can generate one pretty easily; the behavior is readily > observable and repeatable. Will get on it RSN, but at you said, we're > not doing anything about it for 9.0. > > I've a feeling that this will become one of the list of issues to be > fixed with 'real partitioning'. I can believe it. I can't promise anything, but like I say I'm willing to take a poke at it if you can provide me a test case. ...Robert
Robert, >> Yeah, I can generate one pretty easily; the behavior is readily >> observable and repeatable. Will get on it RSN, but at you said, we're >> not doing anything about it for 9.0. Well, I can generate a test case, but on examination it turns out to be nothing to do with partitioning; it's just good old "n-distinct underestimation", and not really related to partitioning or joins. --Josh Berkus
On Sun, Mar 7, 2010 at 12:57 PM, Josh Berkus <josh@agliodbs.com> wrote: >>> Yeah, I can generate one pretty easily; the behavior is readily >>> observable and repeatable. Will get on it RSN, but at you said, we're >>> not doing anything about it for 9.0. > > Well, I can generate a test case, but on examination it turns out to be > nothing to do with partitioning; it's just good old "n-distinct > underestimation", and not really related to partitioning or joins. Ah. Well in 9.0 there will be a work around, by me as it happens. :-) ...Robert