Re: 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. +

On Wed, Jan 7, 2009 at 12:15 AM, Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)gmail(dot)com> writes:
> On Wed, Jan 7, 2009 at 12:15 AM, Bruce Momjian <bruce(at)momjian(dot)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(at)gmail(dot)com> writes:
> > On Wed, Jan 7, 2009 at 12:15 AM, Bruce Momjian <bruce(at)momjian(dot)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(at)commandprompt(dot)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(at)momjian(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)snowman(dot)net> writes:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)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(at)2ndQuadrant(dot)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

Simon,

* Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> 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 can, very easily. It happens whenever we stand up a new server, dump
our tables into it, create our UNION ALL views, and then try to query
them. Sure, we've gotten used to "oh, we need to go check and make sure
constraint_exclusion is on", but it can be annoying, and if we're ever
handing off our table structures/setups to other people we have to
remember to tell them "oh, and don't forget to enable
constraint_exclusion!", etc, etc..

Thanks,

Stephen

Stephen Frost wrote:
-- Start of PGP signed section.
> Simon,
>
> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> > 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 can, very easily. It happens whenever we stand up a new server, dump
> our tables into it, create our UNION ALL views, and then try to query
> them. Sure, we've gotten used to "oh, we need to go check and make sure
> constraint_exclusion is on", but it can be annoying, and if we're ever
> handing off our table structures/setups to other people we have to
> remember to tell them "oh, and don't forget to enable
> constraint_exclusion!", etc, etc..

This new change also adds the constraint exclusion overhead only for
inhertance (by default) so it should slightly improve query peformance.

--
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. +

On Wed, 7 Jan 2009, Simon Riggs wrote:

> Who can set up an inherited table structure but can't remember to turn
> on constraint_exclusion?

I thought the whole point of the WIP "Auto Partitioning Patch" was exactly
to enable larger numbers of such people in the future.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
>>> I don't really understand this. Who can set up an inherited table
>>> structure but can't remember to turn on constraint_exclusion?

> This new change also adds the constraint exclusion overhead only for
> inhertance (by default) so it should slightly improve query peformance.

Right, I think that's the real winning argument for having this: it
gets the benefit of c_e for partitioned tables without imposing overhead
for non-partitioned tables. See Josh B's remarks upthread about
actually going to the trouble of turning c_e off and on on-the-fly to
try to approximate that result.

regards, tom lane

On Wed, 2009-01-07 at 17:46 -0500, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> >> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
> >>> I don't really understand this. Who can set up an inherited table
> >>> structure but can't remember to turn on constraint_exclusion?
>
> > This new change also adds the constraint exclusion overhead only for
> > inhertance (by default) so it should slightly improve query peformance.
>
> Right, I think that's the real winning argument for having this: it
> gets the benefit of c_e for partitioned tables without imposing overhead
> for non-partitioned tables. See Josh B's remarks upthread about
> actually going to the trouble of turning c_e off and on on-the-fly to
> try to approximate that result.

OK, now that's a winning argument. Go for it.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Wed, Jan 7, 2009 at 5:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>>> * Simon Riggs (simon(at)2ndQuadrant(dot)com) wrote:
>>>> I don't really understand this. Who can set up an inherited table
>>>> structure but can't remember to turn on constraint_exclusion?
>
>> This new change also adds the constraint exclusion overhead only for
>> inhertance (by default) so it should slightly improve query peformance.
>
> Right, I think that's the real winning argument for having this: it
> gets the benefit of c_e for partitioned tables without imposing overhead
> for non-partitioned tables. See Josh B's remarks upthread about
> actually going to the trouble of turning c_e off and on on-the-fly to
> try to approximate that result.
>

what i still doesn't understand is why we need a third value at all?
why we simply can't make the new 'partition' behaviour be the default
for c_e on?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

"Jaime Casanova" <jcasanov(at)systemguards(dot)com(dot)ec> writes:
> what i still doesn't understand is why we need a third value at all?

There are cases for wanting all three.

regards, tom lane