Re: Simple postgresql.conf wizard

On Thu, 4 Dec 2008, Gregory Stark wrote:

> Greg Smith <gsmith@gregsmith.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)

Wait, what list am I on?  pgsql-hackers?  Oh, crap, that can't be right at 
all then.  This one is actually an interesting example of how this stuff 
ends up ossified without being revisited, I'm glad you brought it up.

First we have to visit the 8.1 and 8.2 documentation.  There we find the 
real reason it originally defaulted to off:

http://www.postgresql.org/docs/8.1/static/runtime-config-query.html 
"Currently, constraint_exclusion is disabled by default because it risks 
incorrect results if query plans are cached if a table constraint is 
changed or dropped, the previously generated plan might now be wrong, and 
there is no built-in mechanism to force re-planning."  It stayed off for 
that reason for years.

Then the plan invalidation stuff went into 8.3 that made this no longer 
true.  Bruce even removed the item from the TODO list that used to say 
that constraint_exclusion should be improved to "allow it to be used for 
all statements with little performance impact".  Then a couple of months 
later, when the 8.3 docs were being worked on, Tom updated the text to 
remove the obsolete warning about the plan risks:

http://archives.postgresql.org/pgsql-committers/2007-03/msg00372.php

Leaving only the leftovers of the original caveat about how it can also 
cause some overhead as the reason for why it was still off--a concern 
which was certainly more serious when that text was written in 2005 than 
it is today for multiple reasons.

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.  The only 
thing I found when poking around looking for it is that Tom had expressed 
some concerns that the proof overhead was too still large back in 2006: 
http://archives.postgresql.org/pgsql-committers/2006-02/msg00035.php

But you know what?  The cached proof comparison bit Tom commited a couple 
of weeks ago shifted the mechanics of the overhead for this specific case 
around, so even if we did have 8.3 results they'd need to get re-run at 
this point anyway.  See below for more on what might be different soon.

So, if you want to say that turning on constraint_exclusion by default is 
a horrible idea because it adds significant overhead, and you have any 
sort of evidence that will still be true for 8.4 on the kind of hardware 
8.4 is likely to run on, I would greatly appreciate that information.

But presuming that serious thought must have went into every decision made 
about what the defaults for all the performance-related parameter in the 
postgresql.conf is something we all know just ain't so.  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, one 
that's set to off only by historical accident combined with basic 
conservatism (mainly from Tom far as I can tell, he's a nice reliable 
source for that).  Whereas if it's accidentally set wrong, it can lead to 
massively wrong plans.  I'm not sure what the right move here is, but the 
appeal to authority approach for defending the default here isn't going to 
work on me.

> That and the unstated other question "Is someone more likely to use partitions
> without reading the manual or not use partitions without reading the manual
> about the down-sides of constraint_exclusion (in the partitioning
> section....)"

Have you started thinking about the implications of 
http://archives.postgresql.org/message-id/a301bfd90810310750pf108c69x36499546f406650f@mail.gmail.com 
yet?  It is a bold new world of people who partition with less time stuck 
in the manual first we approach, and I was very much thinking about that 
when mulling over whether I agreed with Josh's suggestion to put that into 
the default mixed settings before I went with it (that's right--I wrote 
all the above and it wasn't even my idea originally).  If that doesn't 
make it into 8.4 I will yield to your statement of the boring, 
manual-reading status quo still being on target.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD