Обсуждение: large table starting sequence scan because of default_statistic_target

Hi,

we are running a large 8.3 database and had some trouble with a default
statistic target. We had set it to one special table some time ago, when we
got a problem with a growing table starting with sequence scans.

Last week we did manually cluster this table (create table as ... order by;
drop table orig, rename table temp to orig ). Of course the statistic target
was dropped and we did not remember to set it again.

Why does default_statistic_target defaults to 10?

The documentation tells me, the only drawback is a longer ANALYZE run. we are
setting it to 100 in postgresql.conf and we did not see a much longer run of
ANALYZE. Of course, smaller tables won't need a setting of 100. But small
tables are usually not very interesting when it comes to performance.

With a setting of 10 you run into difficult problems if your table grows.
Suddenly an execution plan changes and you get sequence scans on your largest
table! We had such problems and it was annoying to have a real slow down just
because of this minor configuration parameter.

I suggest to setting it to 100 by default:
- no problems for small installations
- no problems for DBA who always adjust their system in every possible way.
- no problems for growing databases with unequal distributed data

But maybe there are some other reasons not setting it to a higher value. If
so, please tell me.

kind regards
Janning

On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen <vygen@kicktipp.de> wrote:
> Hi,
>
> Why does default_statistic_target defaults to 10?

> I suggest to setting it to 100 by default:

Already done in 8.4

On Monday 16 March 2009 15:13:51 Scott Marlowe wrote:
> On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen <vygen@kicktipp.de> wrote:
> > Hi,
> >
> > Why does default_statistic_target defaults to 10?
> >
> > I suggest to setting it to 100 by default:
>
> Already done in 8.4

GREAT! sorry for not searching the archives or changelog before.

Janning

On Mon, Mar 16, 2009 at 8:24 AM, Janning Vygen <vygen@kicktipp.de> wrote:
> On Monday 16 March 2009 15:13:51 Scott Marlowe wrote:
>> On Mon, Mar 16, 2009 at 7:15 AM, Janning Vygen <vygen@kicktipp.de> wrote:
>> > Hi,
>> >
>> > Why does default_statistic_target defaults to 10?
>> >
>> > I suggest to setting it to 100 by default:
>>
>> Already done in 8.4
>
> GREAT! sorry for not searching the archives or changelog before.

Hey, no problem, there's plenty of new stuff coming up in 8.4, and a
lot of it doesn't show up on the general list anyway.  This just makes
sure a few more people know about this change.