Re: Simple postgresql.conf wizard

Greg Smith <gsmith(at)gregsmith(dot)com> writes:

> On Thu, 4 Dec 2008, Gregory Stark wrote:
>
>> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>>
>>> Is it worse to suffer from additional query overhead if you're sloppy with
>>> the tuning tool, or to discover addition partitions didn't work as you
>>> expected?
>>
>> Surely that's the same question we faced when deciding what the Postgres
>> default should be?
>
> Gosh, you're right. I'm really new here, and I just didn't understand how
> things work. I should have known that there was lots of thorough research into
> that setting before the default was set. (hangs head in shame)

Oh no, in this case I meant just that if we want to change it we should change
it in *both* places. That the argument you're making applies just as much to
the Postgres default as it does to the "mixed" workload default in the tuner.

But I admit I totally didn't remember that the main reason it was originally
off was the lack of plan invalidation. That does rather change things. Perhaps
we should be enabling it now.

If we do though, it shouldn't default one way and then get randomly flipped by
a tool that has the same information to make its decision on. What I'm saying
is that "mixed" is the same information that initdb had about the workload.

If we do change this then I wonder if we need the parameter at all. I mean, we
don't generally have parameters to turn off random parts of the optimizer...

> How much was that overhead lowered by the work done in 8.3? I can't find any
> public information suggesting that was ever even discussed.

Well it does have to compare every constraint with every clause and do a
moderately complex analysis. It's never going to be super-fast for complex
queries. But on the other hand it should drop out pretty fast if the tables
haven't got any constraints so it does seem like it's only hurting people when
they would want it on anyways.

> What I see is a parameter that doesn't add enough overhead relative to query
> execution time on today's systems that I've noticed whether it was on or off

There's a danger in this. There's some famous, probably apocryphal, example of
a fast food restaurant that taste tested their menu and got great results.
Then they cheapened an ingredient and their testers couldn't taste the
difference. Then they did that with another ingredient and another and so on
and each time the testers couldn't taste a difference. And in the end they
ended up rolling out a dramatically inferior menu which people panned compared
to the original...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!