Do we still need constraint_exclusion?

Based on the comments below, are we sure constraint_exclusion still
needs to be a parameter and can't be on by default?

---------------------------------------------------------------------------

Greg Smith wrote:
> 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
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +