Re: Simple postgresql.conf wizard

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)

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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD