Re: Simple postgresql.conf wizard

On Nov 19, 2008, at 11:51 PM, Tom Lane wrote:
> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>>> I think the idea that there IS a magic number is the problem.
>>>
>>> No amount of testing is ever going to refute the argument that,
>>> under
>>> some other workload, a different value might better.
>>>
>>> But that doesn't amount to a reason to leave it the way it is.
>
>> Perhaps a table of experimental data could serve as a rough
>> guideline.
>
> The problem is not that anyone wants to leave it the way it is.
> The problem is that no one has done even a lick of work to identify
> a specific number that is demonstrably better than others -- on *any*
> scale. How about fewer complaints and more effort?

Is there even a good way to find out what planning time was? Is there
a way to gather that stat for every query a session runs?

The thought occurs to me that we're looking at this from the wrong
side of the coin. I've never, ever seen query plan time pose a
problem with Postgres, even without using prepared statements. Anyone
who actually cares that much about plan time is certainly going to
use prepared statements, which makes the whole plan time argument
moot (plan time, not parse time, but of course stats_target doesn't
impact parsing at all).

What I *have* seen, on many different databases, was problems with
bad plans due to default_stats_target being too low. Most of the time
this was solved by simply setting them to 1000. The only case where I
backed down from that and went with like 100 was a database that had
150k tables.

We've been talking about changing default_stats_target for at least 2
or 3 years now. We know that the current value is causing problems.
Can we at least start increasing it? 30 is pretty much guaranteed to
be better than 10, even if it's nowhere close to an ideal value. If
we start slowly increasing it then at least we can start seeing where
people start having issues with query plan time.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828