Skip to content

Commit 445ce15

Browse files
tglsfdctglsfdc
committed
Create a third option named "partition" for constraint_exclusion, and make it
the default. This setting enables constraint exclusion checks only for appendrel members (ie, inheritance children and UNION ALL arms), which are the cases in which constraint exclusion is most likely to be useful. Avoiding the overhead for simple queries that are unlikely to benefit should bring the cost down to the point where this is a reasonable default setting. Per today's discussion.
1 parent 12dcf7b commit 445ce15

File tree

7 files changed

+92
-45
lines changed

7 files changed

+92
-45
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 21 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.202 2009/01/07 12:21:47 mha Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.203 2009/01/07 22:40:48 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -2145,7 +2145,7 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
21452145
</varlistentry>
21462146

21472147
<varlistentry id="guc-constraint-exclusion" xreflabel="constraint_exclusion">
2148-
<term><varname>constraint_exclusion</varname> (<type>boolean</type>)</term>
2148+
<term><varname>constraint_exclusion</varname> (<type>enum</type>)</term>
21492149
<indexterm>
21502150
<primary>constraint exclusion</primary>
21512151
</indexterm>
@@ -2155,14 +2155,20 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
21552155
<listitem>
21562156
<para>
21572157
Enables or disables the query planner's use of table constraints to
2158-
optimize queries. The default is <literal>off</>.
2158+
optimize queries.
2159+
The allowed values of <varname>constraint_exclusion</> are
2160+
<literal>on</> (examine constraints for all tables),
2161+
<literal>off</> (never examine constraints), and
2162+
<literal>partition</> (examine constraints only for inheritance child
2163+
tables and <literal>UNION ALL</> subqueries).
2164+
<literal>partition</> is the default setting.
21592165
</para>
21602166

21612167
<para>
2162-
When this parameter is <literal>on</>, the planner compares
2163-
query conditions with table <literal>CHECK</> constraints, and
2164-
omits scanning tables for which the conditions contradict the
2165-
constraints. For example:
2168+
When this parameter allows it for a particular table, the planner
2169+
compares query conditions with the table's <literal>CHECK</>
2170+
constraints, and omits scanning tables for which the conditions
2171+
contradict the constraints. For example:
21662172

21672173
<programlisting>
21682174
CREATE TABLE parent(key integer, ...);
@@ -2179,17 +2185,17 @@ SELECT * FROM parent WHERE key = 2400;
21792185
</para>
21802186

21812187
<para>
2182-
Currently, <varname>constraint_exclusion</> is disabled by
2183-
default because the constraint checks are relatively
2184-
expensive, and in many circumstances will yield no savings.
2185-
It is recommended to turn this on only if you are actually
2186-
using partitioned tables designed to take advantage of the
2187-
feature.
2188+
Currently, constraint exclusion is enabled by default
2189+
only for cases that are often used to implement table partitioning.
2190+
Turning it on for all tables imposes extra planning overhead that is
2191+
quite noticeable on simple queries, and most often will yield no
2192+
benefit for simple queries. If you have no partitioned tables
2193+
you might prefer to turn it off entirely.
21882194
</para>
21892195

21902196
<para>
2191-
Refer to <xref linkend="ddl-partitioning"> for more information
2192-
on using constraint exclusion and partitioning.
2197+
Refer to <xref linkend="ddl-partitioning-constraint-exclusion"> for
2198+
more information on using constraint exclusion and partitioning.
21932199
</para>
21942200
</listitem>
21952201
</varlistentry>

‎doc/src/sgml/ddl.sgml

Lines changed: 19 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.83 2008/09/08 00:47:40 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.84 2009/01/07 22:40:49 tgl Exp $ -->
22

33
<chapter id="ddl">
44
<title>Data Definition</title>
@@ -2135,7 +2135,7 @@ VALUES ('New York', NULL, NULL, 'NY');
21352135
an existing inheritance hierarchy, be careful to grant all the needed
21362136
permissions on it.
21372137
</para>
2138-
2138+
21392139
<para>
21402140
A serious limitation of the inheritance feature is that indexes (including
21412141
unique constraints) and foreign key constraints only apply to single
@@ -2394,9 +2394,9 @@ CHECK ( outletID BETWEEN 200 AND 300 )
23942394
<listitem>
23952395
<para>
23962396
Ensure that the <xref linkend="guc-constraint-exclusion">
2397-
configuration
2398-
parameter is enabled in <filename>postgresql.conf</>. Without
2399-
this, queries will not be optimized as desired.
2397+
configuration parameter is not disabled in
2398+
<filename>postgresql.conf</>.
2399+
If it is, queries will not be optimized as desired.
24002400
</para>
24012401
</listitem>
24022402

@@ -2698,7 +2698,7 @@ SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
26982698
<para>
26992699
You can use the <command>EXPLAIN</> command to show the difference
27002700
between a plan with <varname>constraint_exclusion</> on and a plan
2701-
with it off. A typical default plan for this type of table setup is:
2701+
with it off. A typical unoptimized plan for this type of table setup is:
27022702

27032703
<programlisting>
27042704
SET constraint_exclusion = off;
@@ -2725,7 +2725,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
27252725
full-table sequential scans, but the point here is that there
27262726
is no need to scan the older partitions at all to answer this query.
27272727
When we enable constraint exclusion, we get a significantly
2728-
reduced plan that will deliver the same answer:
2728+
cheaper plan that will deliver the same answer:
27292729

27302730
<programlisting>
27312731
SET constraint_exclusion = on;
@@ -2751,6 +2751,17 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
27512751
be helpful in the latter case but not the former.
27522752
</para>
27532753

2754+
<para>
2755+
The default (and recommended) setting of
2756+
<xref linkend="guc-constraint-exclusion"> is actually neither
2757+
<literal>on</> nor <literal>off</>, but an intermediate setting
2758+
called <literal>partition</>, which causes the technique to be
2759+
applied only to queries that are likely to be working on partitioned
2760+
tables. The <literal>on</> setting causes the planner to examine
2761+
<literal>CHECK</> constraints in all queries, even simple ones that
2762+
are unlikely to benefit.
2763+
</para>
2764+
27542765
</sect2>
27552766

27562767
<sect2 id="ddl-partitioning-alternatives">
@@ -2817,7 +2828,7 @@ UNION ALL SELECT * FROM measurement_y2008m01;
28172828

28182829
<sect2 id="ddl-partitioning-caveats">
28192830
<title>Caveats</title>
2820-
2831+
28212832
<para>
28222833
The following caveats apply to partitioned tables:
28232834
<itemizedlist>

‎doc/src/sgml/textsearch.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/textsearch.sgml,v 1.46 2008/10/17 18:05:19 teodor Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/textsearch.sgml,v 1.47 2009/01/07 22:40:49 tgl Exp $ -->
22

33
<chapter id="textsearch">
44
<title id="textsearch-title">Full Text Search</title>
@@ -3254,8 +3254,8 @@ SELECT plainto_tsquery('supernovae stars');
32543254
<para>
32553255
Partitioning of big collections and the proper use of GiST and GIN indexes
32563256
allows the implementation of very fast searches with online update.
3257-
Partitioning can be done at the database level using table inheritance
3258-
and <varname>constraint_exclusion</>, or by distributing documents over
3257+
Partitioning can be done at the database level using table inheritance,
3258+
or by distributing documents over
32593259
servers and collecting search results using the <filename>contrib/dblink</>
32603260
extension module. The latter is possible because ranking functions use
32613261
only local information.

‎src/backend/optimizer/util/plancat.c

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.153 2009/01/01 17:23:45 momjian Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.154 2009/01/07 22:40:49 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -27,6 +27,7 @@
2727
#include "nodes/makefuncs.h"
2828
#include "nodes/nodeFuncs.h"
2929
#include "optimizer/clauses.h"
30+
#include "optimizer/cost.h"
3031
#include "optimizer/plancat.h"
3132
#include "optimizer/predtest.h"
3233
#include "optimizer/prep.h"
@@ -43,7 +44,7 @@
4344

4445

4546
/* GUC parameter */
46-
bool constraint_exclusion = false;
47+
int constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION;
4748

4849
/* Hook for plugins to get control in get_relation_info() */
4950
get_relation_info_hook_type get_relation_info_hook = NULL;
@@ -561,8 +562,9 @@ get_relation_constraints(PlannerInfo *root,
561562
* self-inconsistent restrictions, or restrictions inconsistent with the
562563
* relation's CHECK constraints.
563564
*
564-
* Note: this examines only rel->relid and rel->baserestrictinfo; therefore
565-
* it can be called before filling in other fields of the RelOptInfo.
565+
* Note: this examines only rel->relid, rel->reloptkind, and
566+
* rel->baserestrictinfo; therefore it can be called before filling in
567+
* other fields of the RelOptInfo.
566568
*/
567569
bool
568570
relation_excluded_by_constraints(PlannerInfo *root,
@@ -573,8 +575,10 @@ relation_excluded_by_constraints(PlannerInfo *root,
573575
List *safe_constraints;
574576
ListCell *lc;
575577

576-
/* Skip the test if constraint exclusion is disabled */
577-
if (!constraint_exclusion)
578+
/* Skip the test if constraint exclusion is disabled for the rel */
579+
if (constraint_exclusion == CONSTRAINT_EXCLUSION_OFF ||
580+
(constraint_exclusion == CONSTRAINT_EXCLUSION_PARTITION &&
581+
rel->reloptkind != RELOPT_OTHER_MEMBER_REL))
578582
return false;
579583

580584
/*

‎src/backend/utils/misc/guc.c

Lines changed: 29 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Written by Peter Eisentraut <peter_e@gmx.net>.
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.490 2009/01/06 16:39:52 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.491 2009/01/07 22:40:49 tgl Exp $
1414
*
1515
*--------------------------------------------------------------------
1616
*/
@@ -311,6 +311,23 @@ static const struct config_enum_entry backslash_quote_options[] = {
311311
{NULL, 0, false}
312312
};
313313

314+
/*
315+
* Although only "on", "off", and "partition" are documented, we
316+
* accept all the likely variants of "on" and "off".
317+
*/
318+
static const struct config_enum_entry constraint_exclusion_options[] = {
319+
{"partition", CONSTRAINT_EXCLUSION_PARTITION, false},
320+
{"on", CONSTRAINT_EXCLUSION_ON, false},
321+
{"off", CONSTRAINT_EXCLUSION_OFF, false},
322+
{"true", CONSTRAINT_EXCLUSION_ON, true},
323+
{"false", CONSTRAINT_EXCLUSION_OFF, true},
324+
{"yes", CONSTRAINT_EXCLUSION_ON, true},
325+
{"no", CONSTRAINT_EXCLUSION_OFF, true},
326+
{"1", CONSTRAINT_EXCLUSION_ON, true},
327+
{"0", CONSTRAINT_EXCLUSION_OFF, true},
328+
{NULL, 0, false}
329+
};
330+
314331
/*
315332
* Options for enum values stored in other modules
316333
*/
@@ -635,15 +652,6 @@ static struct config_bool ConfigureNamesBool[] =
635652
&enable_hashjoin,
636653
true, NULL, NULL
637654
},
638-
{
639-
{"constraint_exclusion", PGC_USERSET, QUERY_TUNING_OTHER,
640-
gettext_noop("Enables the planner to use constraints to optimize queries."),
641-
gettext_noop("Child table scans will be skipped if their "
642-
"constraints guarantee that no rows match the query.")
643-
},
644-
&constraint_exclusion,
645-
false, NULL, NULL
646-
},
647655
{
648656
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
649657
gettext_noop("Enables genetic query optimization."),
@@ -2521,6 +2529,17 @@ static struct config_enum ConfigureNamesEnum[] =
25212529
NOTICE, client_message_level_options, NULL, NULL
25222530
},
25232531

2532+
{
2533+
{"constraint_exclusion", PGC_USERSET, QUERY_TUNING_OTHER,
2534+
gettext_noop("Enables the planner to use constraints to optimize queries."),
2535+
gettext_noop("Table scans will be skipped if their constraints"
2536+
" guarantee that no rows match the query.")
2537+
},
2538+
&constraint_exclusion,
2539+
CONSTRAINT_EXCLUSION_PARTITION, constraint_exclusion_options,
2540+
NULL, NULL
2541+
},
2542+
25242543
{
25252544
{"default_transaction_isolation", PGC_USERSET, CLIENT_CONN_STATEMENT,
25262545
gettext_noop("Sets the transaction isolation level of each new transaction."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -211,7 +211,7 @@
211211
# - Other Planner Options -
212212

213213
#default_statistics_target = 100 # range 1-10000
214-
#constraint_exclusion = off
214+
#constraint_exclusion = partition # on, off, or partition
215215
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
216216
#from_collapse_limit = 8
217217
#join_collapse_limit = 8 # 1 disables collapsing of explicit

‎src/include/optimizer/cost.h

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.95 2009/01/01 17:24:00 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/cost.h,v 1.96 2009/01/07 22:40:49 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -29,6 +29,13 @@
2929

3030
#define DEFAULT_EFFECTIVE_CACHE_SIZE 16384 /* measured in pages */
3131

32+
typedef enum
33+
{
34+
CONSTRAINT_EXCLUSION_OFF, /* do not use c_e */
35+
CONSTRAINT_EXCLUSION_ON, /* apply c_e to all rels */
36+
CONSTRAINT_EXCLUSION_PARTITION /* apply c_e to otherrels only */
37+
} ConstraintExclusionType;
38+
3239

3340
/*
3441
* prototypes for costsize.c
@@ -52,7 +59,7 @@ extern bool enable_hashagg;
5259
extern bool enable_nestloop;
5360
extern bool enable_mergejoin;
5461
extern bool enable_hashjoin;
55-
extern bool constraint_exclusion;
62+
extern int constraint_exclusion;
5663

5764
extern double clamp_row_est(double nrows);
5865
extern double index_pages_fetched(double tuples_fetched, BlockNumber pages,

0 commit comments

Comments
 (0)