Re: Better default_statistics_target

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Better default_statistics_target
Date: 2007-12-05 14:26:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-patches

Hash: RIPEMD160

Simon spoke:
> The choice of 100 is because of the way the LIKE estimator is
> configured. Greg is not suggesting he measured it and found 100 to be
> best, he is saying that the LIKE operator is hard-coded at 100 and so
> the stats_target should reflect that.


> Setting it to 100 for all columns because of LIKE doesn't make much
> sense. I think we should set stats target differently depending upon the
> data type, but thats probably an 8.4 thing. Long text fields that might
> use LIKE should be set to 100. CHAR(1) and general fields should be set
> to 10.

Agreed, this would be a nice 8.4 thing. But what about 8.3 and 8.2? Is
there a reason not to make this change? I know I've been lazy and not run
any absolute figures, but rough tests show that raising it (from 10 to
100) results in a very minor increase in analyze time, even for large
databases. I think the burden of a slightly slower analyze time, which
can be easily adjusted, both in postgresql.conf and right before running
an analyze, is very small compared to the pain of some queries - which worked
before - suddenly running much, much slower for no apparent reason at all.
Sure, 100 may have been chosen somewhat arbitrarily for the LIKE thing,
but this is a current real-world performance regression (aka a bug,
according to a nearby thread). Almost everyone agrees that 10 is too low,
so why not make it 100, throw a big warning in the release notes, and
then start some serious re-evaluation for 8.4?

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200712050920



In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2007-12-05 15:47:30 Re:
Previous Message Peter Eisentraut 2007-12-05 11:08:09 Re: Is postgres.gif missing in cvs?

Browse pgsql-patches by date

  From Date Subject
Next Message Guillaume Smet 2007-12-05 17:49:00 Re: Better default_statistics_target
Previous Message Peter Eisentraut 2007-12-05 11:08:09 Re: Is postgres.gif missing in cvs?