Re: Do we still need constraint_exclusion?

"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