Do we still need constraint_exclusion? - Mailing list pgsql-hackers

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

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +