Skip site navigation (1) Skip section navigation (2)

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
Message-ID: dda58892d32fdbc418e1639a04f3c9b4@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
-----BEGIN PGP SIGNED MESSAGE-----
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.

Exactly.

> 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
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


-----BEGIN PGP SIGNATURE-----

iD8DBQFHVrSivJuQZxSWSsgRAyDNAKCInH9SJRO8ly1L1MomJUPlBslBlgCeLQ1v
+w4ZumRcB5U5L3SGT0rk4AE=
=I8Ur
-----END PGP SIGNATURE-----



In response to

Responses

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2007-12-05 15:47:30
Subject: Re: buildenv.pl/buildenv.bat
Previous:From: Peter EisentrautDate: 2007-12-05 11:08:09
Subject: Re: Is postgres.gif missing in cvs?

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group