SEO

#1 (**permalink**) 04-15-2008, 10:42 PM

>>> On Wed, Jan 30, 2008 at 8:13 PM, in message
<d6d6637f0801301813n64fa58eu76385cf8a621907@mail.g mail.com>, "Christopher
Browne" <cbbrowne@gmail.com> wrote:

> There seems to be *plenty* of evidence out there that the performance
> penalty would NOT be "essentially zero."

I can confirm that I have had performance tank because of boosting
the statistics target for selected columns. It appeared to be time
spent in the planning phase, not a bad plan choice. Reducing the
numbers restored decent performance.

-Kevin

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

#2 (**permalink**) 04-15-2008, 10:42 PM

Kevin Grittner wrote:
>>>> On Wed, Jan 30, 2008 at 8:13 PM, in message
> <d6d6637f0801301813n64fa58eu76385cf8a621907@mail.g mail.com>, "Christopher
> Browne" <cbbrowne@gmail.com> wrote:
>
>> There seems to be *plenty* of evidence out there that the performance
>> penalty would NOT be "essentially zero."
>
> I can confirm that I have had performance tank because of boosting
> the statistics target for selected columns. It appeared to be time
> spent in the planning phase, not a bad plan choice. Reducing the
> numbers restored decent performance.

One idea I've been thinking about is to add a step after the analyze, to
look at the statistics that was gathered. If it looks like the the
distribution is pretty flat, reduce the data to a smaller set before
storing it in pg_statistic.

You would still get the hit of longer ANALYZE time, but at least you
would avoid the hit on query performance where the higher statistics are
not helpful. We could also print an INFO line along the lines of "you
might as well lower the statistics target for this table, because it's
not helping".

No, I don't know how to determine when you could reduce the data, or how
to reduce it...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#3 (**permalink**) 04-15-2008, 10:42 PM

On Thursday 31 January 2008 09:55, Kevin Grittner wrote:
> >>> On Wed, Jan 30, 2008 at 8:13 PM, in message
>
> <d6d6637f0801301813n64fa58eu76385cf8a621907@mail.g mail.com>, "Christopher
>
> Browne" <cbbrowne@gmail.com> wrote:
> > There seems to be *plenty* of evidence out there that the performance
> > penalty would NOT be "essentially zero."
>
> I can confirm that I have had performance tank because of boosting
> the statistics target for selected columns. It appeared to be time
> spent in the planning phase, not a bad plan choice. Reducing the
> numbers restored decent performance.
>

Bad plans from boosting to 100 or less? Or something much higher?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#4 (**permalink**) 04-15-2008, 10:42 PM

>>> On Thu, Jan 31, 2008 at 10:19 PM, in message
<200801312319.59723.xzilla@users.sourceforge.net >, Robert Treat
<xzilla@users.sourceforge.net> wrote:
> On Thursday 31 January 2008 09:55, Kevin Grittner wrote:
>>
>> I can confirm that I have had performance tank because of boosting
>> the statistics target for selected columns. It appeared to be time
>> spent in the planning phase, not a bad plan choice. Reducing the
>> numbers restored decent performance.
>
> Bad plans from boosting to 100 or less? Or something much higher?

I boosted on a large number of columns based on domains. County
number columns (present in most tables) were set to 80. Some
columns were set all the way to 1000. When performance tanked, we
didn't have time to experiment, so we just backed it all out.
Perhaps I could do some more controlled testing soon against 8.3,
to narrow it down and confirm the current status of the issue. I
do seem to recall that simple queries weren't suffering, it was
those which joined many tables which had multiple indexes.

-Kevin

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#5 (**permalink**) 04-15-2008, 10:42 PM

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> On Thu, Jan 31, 2008 at 10:19 PM, in message
> <200801312319.59723.xzilla@users.sourceforge.net >, Robert Treat
> <xzilla@users.sourceforge.net> wrote:
>> Bad plans from boosting to 100 or less? Or something much higher?

> I boosted on a large number of columns based on domains. County
> number columns (present in most tables) were set to 80. Some
> columns were set all the way to 1000. When performance tanked, we
> didn't have time to experiment, so we just backed it all out.
> Perhaps I could do some more controlled testing soon against 8.3,
> to narrow it down and confirm the current status of the issue. I
> do seem to recall that simple queries weren't suffering, it was
> those which joined many tables which had multiple indexes.

That fits with the idea that eqjoinsel() is a main culprit.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Re: [PATCHES] Better default_statistics_target