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@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.phpBut 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. +
On Wed, Jan 7, 2009 at 12:15 AM, Bruce Momjian <bruce@momjian.us> wrote:
Based on the comments below, are we sure constraint_exclusion still
needs to be a parameter and can't be on by default?
The benchmarking we did to determine the impact of raising
default_statistics_target was pretty interesting and informative. It
seems like a similar approach would be valuable here, though I'm not
exactly sure what to test.
...Robert
* Bruce Momjian (bruce@momjian.us) wrote:
Based on the comments below, are we sure constraint_exclusion still
needs to be a parameter and can't be on by default?
I'd like to get rid of the option and have it on by default. It's a bit
frustrating to have to remember to turn it on with new installs, and if
it doesn't add much overhead or cause problems then I don't see the
value in having it be configurable.
Thanks,
Stephen
"Robert Haas" <robertmhaas@gmail.com> writes:
On Wed, Jan 7, 2009 at 12:15 AM, Bruce Momjian <bruce@momjian.us> wrote:
Based on the comments below, are we sure constraint_exclusion still
needs to be a parameter and can't be on by default?
The benchmarking we did to determine the impact of raising
default_statistics_target was pretty interesting and informative.
Here's a quick and dirty benchmark. I put 10000 copies of
select count(*) from tenk1 where thousand = 42;
into a file and executed
time psql -q -f z10000.sql regression >/dev/null
several times (just to check how much noise there was in the results).
This is this morning's CVS HEAD, debug/cassert enabled, default
configuration parameters except for turning off autovacuum to reduce
the noise.
Stock table definition (ie, no constraints)
c_e off
real 0m7.828s
real 0m8.051s
real 0m7.871s
real 0m7.960s
total: 31.710 sec
c_e on
real 0m7.991s
real 0m8.149s
real 0m7.905s
real 0m7.910s
total: 31.955 sec
then
alter table tenk1 add constraint c1 check (thousand between 0 and 1000);
c_e off
real 0m7.868s
real 0m8.061s
real 0m7.759s
real 0m7.988s
total: 31.676 sec
c_e on
real 0m8.601s
real 0m8.551s
real 0m8.571s
real 0m8.772s
total: 34.495
then
alter table tenk1 add constraint c2 check (tenthous between 0 and 10000);
c_e off
real 0m7.922s
real 0m7.936s
real 0m7.901s
real 0m7.866s
total: 31.625 sec
c_e on
real 0m8.723s
real 0m8.865s
real 0m8.838s
real 0m8.747s
total: 35.173 sec
The measured difference between CE off and CE on without any actual
constraints to test is less than 1%, and it's not clear that that's
above the noise threshold in this test. But the penalty when there is
a relevant constraint is very measurable (about 9% here) and even a
constraint that is not relevant to the query takes a measurable amount
of time to discard (about 2% here). Again note that these are overall
numbers using a psql script; an application with less per-query overhead
would see worse degradation.
In installations whose average query is significantly heavier-weight
than this one, and where constraint exclusion actually improves matters
on a routine basis, it makes sense to turn it on by default. I will
continue to resist having it on as a factory default, because I continue
to believe that it's 99% useless to most people. As for removing the
option, no way.
regards, tom lane
On Wed, 2009-01-07 at 10:59 -0500, Tom Lane wrote:
"Robert Haas" <robertmhaas@gmail.com> writes:
On Wed, Jan 7, 2009 at 12:15 AM, Bruce Momjian <bruce@momjian.us> wrote:
Based on the comments below, are we sure constraint_exclusion still
needs to be a parameter and can't be on by default?
In installations whose average query is significantly heavier-weight
than this one, and where constraint exclusion actually improves matters
on a routine basis, it makes sense to turn it on by default. I will
continue to resist having it on as a factory default, because I continue
to believe that it's 99% useless to most people. As for removing the
I believe are correct in that it is 99% useless to most people. If it
was turned on by default, it would also not be noticed by 99% of those
people.
So why not help the 1% that it actually would?
Joshua D. Drake
regards, tom lane
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
"Joshua D. Drake" <jd@commandprompt.com> writes:
On Wed, 2009-01-07 at 10:59 -0500, Tom Lane wrote:
In installations whose average query is significantly heavier-weight
than this one, and where constraint exclusion actually improves matters
on a routine basis, it makes sense to turn it on by default. I will
continue to resist having it on as a factory default, because I continue
to believe that it's 99% useless to most people. As for removing the
I believe are correct in that it is 99% useless to most people. If it
was turned on by default, it would also not be noticed by 99% of those
people.
~ 10% slowdown on trivial queries will get noticed.
I just thought of a possible compromise though: maybe we could invent an
intermediate constraint_exclusion setting that makes the checks only for
inheritance-child tables. This would avoid the overhead for simple
queries and still get the benefit for most of the cases where it's
actually useful. I'm not sure how hard this'd be to shoehorn into the
planner, but if it's doable it might satisfy both camps.
regards, tom lane
~ 10% slowdown on trivial queries will get noticed.
Agreed.
I just thought of a possible compromise though: maybe we could invent an
intermediate constraint_exclusion setting that makes the checks only for
inheritance-child tables. This would avoid the overhead for simple
queries and still get the benefit for most of the cases where it's
actually useful. I'm not sure how hard this'd be to shoehorn into the
planner, but if it's doable it might satisfy both camps.
+1
...Robert
On Wed, 2009-01-07 at 12:26 -0500, Robert Haas wrote:
~ 10% slowdown on trivial queries will get noticed.
I just thought of a possible compromise though: maybe we could invent an
intermediate constraint_exclusion setting that makes the checks only for
inheritance-child tables. This would avoid the overhead for simple
queries and still get the benefit for most of the cases where it's
actually useful. I'm not sure how hard this'd be to shoehorn into the
planner, but if it's doable it might satisfy both camps.
I can buy into this.
Joshua D. Drake
+1
...Robert
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
I wrote:
I just thought of a possible compromise though: maybe we could invent an
intermediate constraint_exclusion setting that makes the checks only for
inheritance-child tables. This would avoid the overhead for simple
queries and still get the benefit for most of the cases where it's
actually useful. I'm not sure how hard this'd be to shoehorn into the
planner, ...
Actually, it looks like it'd be totally trivial to implement: just check
rel->reloptkind == RELOPT_OTHER_MEMBER_REL to detect whether we're
looking at an inheritance child. (Actually this would also succeed
for a UNION ALL member, but that's good because that's the other case
where constraint exclusion is more likely to be useful.)
So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.
regards, tom lane
Tom Lane wrote:
I wrote:
I just thought of a possible compromise though: maybe we could invent an
intermediate constraint_exclusion setting that makes the checks only for
inheritance-child tables. This would avoid the overhead for simple
queries and still get the benefit for most of the cases where it's
actually useful. I'm not sure how hard this'd be to shoehorn into the
planner, ...Actually, it looks like it'd be totally trivial to implement: just check
rel->reloptkind == RELOPT_OTHER_MEMBER_REL to detect whether we're
looking at an inheritance child. (Actually this would also succeed
for a UNION ALL member, but that's good because that's the other case
where constraint exclusion is more likely to be useful.)So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.
Wow, this will be a great leap forward for usability.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.
"inherit"?
...Robert
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Actually, it looks like it'd be totally trivial to implement: just check
rel->reloptkind == RELOPT_OTHER_MEMBER_REL to detect whether we're
looking at an inheritance child. (Actually this would also succeed
for a UNION ALL member, but that's good because that's the other case
where constraint exclusion is more likely to be useful.)
Covering the UNION ALL case would be terrific! I was a bit concerned
since we just have UNION ALL views and don't use inheritance generally.
So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.
Not a huge fan of 'child' since it implies inheritance. 'union' doesn't
work for a similar reason. What about 'partitioned'?
Thanks,
Stephen
So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.
This is terrific. I've actually been turning c_e on and off by ROLE
property at some sites because of the penalty on one-liner web queries.
This would solve that.
I don't like "child", though, which is not a keyword we use definitively
elsewhere. I'd suggest "INHERITED" or something based on "inherit",
because that's the actual keyword we use when we create a partition.
--Josh
Stephen Frost <sfrost@snowman.net> writes:
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.
Not a huge fan of 'child' since it implies inheritance. 'union' doesn't
work for a similar reason. What about 'partitioned'?
Hm, how about just 'partition'? Your argument is fair, and another
point in its favor is that someday we'll probably have an explicit
notion of partitioned tables and both the inheritance and union-view
approaches would become legacy methods. We'd certainly want constraint
exclusion to apply to all three by default.
regards, tom lane
Tom,
Hm, how about just 'partition'? Your argument is fair, and another
point in its favor is that someday we'll probably have an explicit
notion of partitioned tables and both the inheritance and union-view
approaches would become legacy methods. We'd certainly want constraint
exclusion to apply to all three by default.
I think that's probably the best compromise.
--Josh
So, barring objections, I'll go make this happen. What do we want to
call the intermediate constraint_exclusion value? The first thing
that comes to mind is constraint_exclusion = 'child', but perhaps
someone has a better idea.Not a huge fan of 'child' since it implies inheritance. 'union' doesn't
work for a similar reason. What about 'partitioned'?Hm, how about just 'partition'?
+1
Andreas
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
Hm, how about just 'partition'? Your argument is fair, and another
point in its favor is that someday we'll probably have an explicit
notion of partitioned tables and both the inheritance and union-view
approaches would become legacy methods. We'd certainly want constraint
exclusion to apply to all three by default.
'partition' works for me.
Thanks!
Stephen
On Wed, 2009-01-07 at 12:54 -0500, Tom Lane wrote:
So, barring objections, I'll go make this happen.
I don't really understand this. Who can set up an inherited table
structure but can't remember to turn on constraint_exclusion? That is
the easiest part of the whole process by a long way. Nobody has this
table design by accident, they've all been told how or read the docs.
I'm not against the change so much as bemused by it.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Simon Riggs wrote:
On Wed, 2009-01-07 at 12:54 -0500, Tom Lane wrote:
So, barring objections, I'll go make this happen.
I don't really understand this. Who can set up an inherited table
structure but can't remember to turn on constraint_exclusion? That is
the easiest part of the whole process by a long way. Nobody has this
table design by accident, they've all been told how or read the docs.I'm not against the change so much as bemused by it.
The idea is that it is "one less thing to do".
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Le 7 janv. 09 à 22:21, Simon Riggs <simon@2ndQuadrant.com> a écrit :
On Wed, 2009-01-07 at 12:54 -0500, Tom Lane wrote:
So, barring objections, I'll go make this happen.
I don't really understand this. Who can set up an inherited table
structure but can't remember to turn on constraint_exclusion? That is
the easiest part of the whole process by a long way. Nobody has this
table design by accident, they've all been told how or read the docs.I'm not against the change so much as bemused by it.
I think the improvement is not in the usability part but the
performance benefit of avoiding lots of cpu cycles when there's no
gain to hope.
And I like the new option!
--
dim
