pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedEstimation issue with partitioned tables
6+ messages / 2 participants
[nested] [flat]
* Estimation issue with partitioned tables
@ 2010-02-28 20:19 Josh Berkus <josh@agliodbs.com>
2010-03-03 17:12 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2010-02-28 20:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Estimation issue with partitioned tables
2010-02-28 20:19 Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
@ 2010-03-03 17:12 ` Robert Haas <robertmhaas@gmail.com>
2010-03-03 21:45 ` Re: Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robert Haas @ 2010-03-03 17:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Estimation issue with partitioned tables
2010-02-28 20:19 Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-03 17:12 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
@ 2010-03-03 21:45 ` Josh Berkus <josh@agliodbs.com>
2010-03-04 02:07 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2010-03-03 21:45 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Estimation issue with partitioned tables
2010-02-28 20:19 Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-03 17:12 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
2010-03-03 21:45 ` Re: Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
@ 2010-03-04 02:07 ` Robert Haas <robertmhaas@gmail.com>
2010-03-07 17:57 ` Re: Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 6+ messages in thread
From: Robert Haas @ 2010-03-04 02:07 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Estimation issue with partitioned tables
2010-02-28 20:19 Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-03 17:12 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
2010-03-03 21:45 ` Re: Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-04 02:07 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
@ 2010-03-07 17:57 ` Josh Berkus <josh@agliodbs.com>
2010-03-08 21:56 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2010-03-07 17:57 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Estimation issue with partitioned tables
2010-02-28 20:19 Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-03 17:12 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
2010-03-03 21:45 ` Re: Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-04 02:07 ` Re: Estimation issue with partitioned tables Robert Haas <robertmhaas@gmail.com>
2010-03-07 17:57 ` Re: Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
@ 2010-03-08 21:56 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Robert Haas @ 2010-03-08 21:56 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-03-08 21:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-28 20:19 Estimation issue with partitioned tables Josh Berkus <josh@agliodbs.com>
2010-03-03 17:12 ` Robert Haas <robertmhaas@gmail.com>
2010-03-03 21:45 ` Josh Berkus <josh@agliodbs.com>
2010-03-04 02:07 ` Robert Haas <robertmhaas@gmail.com>
2010-03-07 17:57 ` Josh Berkus <josh@agliodbs.com>
2010-03-08 21:56 ` Robert Haas <robertmhaas@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox