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 <gsm...@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/a301bfd90810310750pf108c69x36499546f4066...@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 gsm...@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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
