Re: Do we still need constraint_exclusion? - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | Re: Do we still need constraint_exclusion? |
Date | |
Msg-id | 28841.1231343991@sss.pgh.pa.us Whole thread Raw |
In response to | Re: Do we still need constraint_exclusion? ("Robert Haas" <robertmhaas@gmail.com>) |
Responses |
Re: Do we still need constraint_exclusion?
("Joshua D. Drake" <jd@commandprompt.com>)
|
List | pgsql-hackers |
"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 ofselect count(*) from tenk1 where thousand = 42; into a file and executedtime 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
pgsql-hackers by date: