Skip to content

Commit 3893127

Browse files
tglsfdctglsfdc
committed
Fix constraint exclusion to work in inherited UPDATE/DELETE queries
... in fact, it will be applied now in any query whatsoever. I'm still a bit concerned about the cycles that might be expended in failed proof attempts, but given that CE is turned off by default, it's the user's choice whether to expend those cycles or not. (Possibly we should change the simple bool constraint_exclusion parameter to something more fine-grained?)
1 parent 48d9ad3 commit 3893127

File tree

6 files changed

+97
-80
lines changed

6 files changed

+97
-80
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.45 2006/01/23 18:16:41 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.46 2006/02/04 23:03:19 tgl Exp $
33
-->
44
<chapter Id="runtime-config">
55
<title>Server Configuration</title>
@@ -1986,8 +1986,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
19861986
When this parameter is <literal>on</>, the planner compares
19871987
query conditions with table <literal>CHECK</> constraints, and
19881988
omits scanning tables for which the conditions contradict the
1989-
constraints. (Presently this is done only for child tables of
1990-
inheritance scans.) For example:
1989+
constraints. For example:
19911990

19921991
<programlisting>
19931992
CREATE TABLE parent(key integer, ...);

‎doc/src/sgml/ddl.sgml

Lines changed: 5 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.51 2005/12/23 00:38:03 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.52 2006/02/04 23:03:19 tgl Exp $ -->
22

33
<chapter id="ddl">
44
<title>Data Definition</title>
@@ -2179,7 +2179,7 @@ SELECT * from cities*;
21792179

21802180
<para>
21812181
<productname>PostgreSQL</productname> supports basic table
2182-
partitioning. This section describes why and how you can implement
2182+
partitioning. This section describes why and how to implement
21832183
partitioning as part of your database design.
21842184
</para>
21852185

@@ -2237,7 +2237,7 @@ SELECT * from cities*;
22372237
table of a single parent table. The parent table itself is normally
22382238
empty; it exists just to represent the entire data set. You should be
22392239
familiar with inheritance (see <xref linkend="ddl-inherit">) before
2240-
attempting to implement partitioning.
2240+
attempting to set up partitioning.
22412241
</para>
22422242

22432243
<para>
@@ -2579,8 +2579,7 @@ UNION ALL SELECT * FROM measurement_yy05mm12
25792579
UNION ALL SELECT * FROM measurement_yy06mm01;
25802580
</programlisting>
25812581

2582-
However, constraint exclusion is currently not supported for
2583-
partitioned tables defined in this manner. Also, the need to
2582+
However, the need to
25842583
recreate the view adds an extra step to adding and dropping
25852584
individual partitions of the dataset.
25862585
</para>
@@ -2679,8 +2678,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
26792678
optimized, since the planner cannot know what partitions the
26802679
parameter value might select at runtime. For the same reason,
26812680
<quote>stable</> functions such as <function>CURRENT_DATE</function>
2682-
must be avoided. Joining the partition key to a column of another
2683-
table will not be optimized, either.
2681+
must be avoided.
26842682
</para>
26852683
</listitem>
26862684

@@ -2707,13 +2705,6 @@ CHECK ( x = 1::bigint )
27072705
</para>
27082706
</listitem>
27092707

2710-
<listitem>
2711-
<para>
2712-
<command>UPDATE</command> and <command>DELETE</command> commands
2713-
against the master table do not currently perform constraint exclusion.
2714-
</para>
2715-
</listitem>
2716-
27172708
<listitem>
27182709
<para>
27192710
All constraints on all partitions of the master table are considered for

‎src/backend/optimizer/path/allpaths.c

Lines changed: 30 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.141 2006/02/03 21:08:49 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.142 2006/02/04 23:03:20 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -26,7 +26,6 @@
2626
#include "optimizer/paths.h"
2727
#include "optimizer/plancat.h"
2828
#include "optimizer/planner.h"
29-
#include "optimizer/predtest.h"
3029
#include "optimizer/prep.h"
3130
#include "optimizer/var.h"
3231
#include "parser/parsetree.h"
@@ -36,14 +35,12 @@
3635

3736

3837
/* These parameters are set by GUC */
39-
bool constraint_exclusion = false;
4038
bool enable_geqo = false; /* just in case GUC doesn't set it */
4139
int geqo_threshold;
4240

4341

4442
static void set_base_rel_pathlists(PlannerInfo *root);
45-
static void set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
46-
Index rti, RangeTblEntry *rte);
43+
static void set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti);
4744
static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
4845
RangeTblEntry *rte);
4946
static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
@@ -146,8 +143,7 @@ set_base_rel_pathlists(PlannerInfo *root)
146143
if (rel->reloptkind != RELOPT_BASEREL)
147144
continue;
148145

149-
set_rel_pathlist(root, rel, rti,
150-
rt_fetch(rti, root->parse->rtable));
146+
set_rel_pathlist(root, rel, rti);
151147
}
152148
}
153149

@@ -156,9 +152,10 @@ set_base_rel_pathlists(PlannerInfo *root)
156152
* Build access paths for a base relation
157153
*/
158154
static void
159-
set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
160-
Index rti, RangeTblEntry *rte)
155+
set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti)
161156
{
157+
RangeTblEntry *rte = rt_fetch(rti, root->parse->rtable);
158+
162159
if (rte->inh)
163160
{
164161
/* It's an "append relation", process accordingly */
@@ -207,6 +204,24 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
207204
if (create_or_index_quals(root, rel))
208205
set_baserel_size_estimates(root, rel);
209206

207+
/*
208+
* If we can prove we don't need to scan the rel via constraint exclusion,
209+
* set up a single dummy path for it. (Rather than inventing a special
210+
* "dummy" path type, we represent this as an AppendPath with no members.)
211+
*/
212+
if (relation_excluded_by_constraints(rel, rte))
213+
{
214+
/* Reset output-rows estimate to 0 */
215+
rel->rows = 0;
216+
217+
add_path(rel, (Path *) create_append_path(rel, NIL));
218+
219+
/* Select cheapest path (pretty easy in this case...) */
220+
set_cheapest(rel);
221+
222+
return;
223+
}
224+
210225
/*
211226
* Generate paths and add them to the rel's pathlist.
212227
*
@@ -273,7 +288,6 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
273288
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
274289
int childRTindex;
275290
RelOptInfo *childrel;
276-
RangeTblEntry *childrte;
277291
Path *childpath;
278292
ListCell *parentvars;
279293
ListCell *childvars;
@@ -316,53 +330,18 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
316330
childrel->max_attr);
317331

318332
/*
319-
* If we can prove we don't need to scan this child via constraint
320-
* exclusion, just ignore it. (We have to have converted the
321-
* baserestrictinfo Vars before we can make the test.)
322-
*
323-
* XXX it'd probably be better to give the child some kind of dummy
324-
* cheapest path, or otherwise explicitly mark it as ignorable.
325-
* Currently there is an ugly check in join_before_append() to handle
326-
* excluded children.
327-
*/
328-
childrte = rt_fetch(childRTindex, root->parse->rtable);
329-
if (constraint_exclusion &&
330-
childrte->rtekind == RTE_RELATION)
331-
{
332-
List *constraint_pred;
333-
334-
constraint_pred = get_relation_constraints(childrte->relid,
335-
childrel);
336-
337-
/*
338-
* We do not currently enforce that CHECK constraints contain only
339-
* immutable functions, so it's necessary to check here. We
340-
* daren't draw conclusions from plan-time evaluation of
341-
* non-immutable functions.
342-
*/
343-
if (!contain_mutable_functions((Node *) constraint_pred))
344-
{
345-
/*
346-
* The constraints are effectively ANDed together, so we can
347-
* just try to refute the entire collection at once. This may
348-
* allow us to make proofs that would fail if we took them
349-
* individually.
350-
*/
351-
if (predicate_refuted_by(constraint_pred,
352-
childrel->baserestrictinfo))
353-
continue;
354-
}
355-
}
356-
357-
/*
358-
* Compute the child's access paths, and save the cheapest.
333+
* Compute the child's access paths, and add the cheapest one
334+
* to the Append path we are constructing for the parent.
359335
*
360336
* It's possible that the child is itself an appendrel, in which
361337
* case we can "cut out the middleman" and just add its child
362338
* paths to our own list. (We don't try to do this earlier because
363339
* we need to apply both levels of transformation to the quals.)
340+
* This test also handles the case where the child rel need not
341+
* be scanned because of constraint exclusion: it'll have an
342+
* Append path with no subpaths, and will vanish from our list.
364343
*/
365-
set_rel_pathlist(root, childrel, childRTindex, childrte);
344+
set_rel_pathlist(root, childrel, childRTindex);
366345

367346
childpath = childrel->cheapest_total_path;
368347
if (IsA(childpath, AppendPath))

‎src/backend/optimizer/path/joinpath.c

Lines changed: 7 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.100 2006/02/03 21:08:49 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.101 2006/02/04 23:03:20 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -867,14 +867,13 @@ join_before_append(PlannerInfo *root,
867867
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
868868

869869
/*
870-
* If the child has no cheapest_total_path, assume it was deemed
871-
* excludable by constraint exclusion (see set_append_rel_pathlist).
870+
* Check to see if child was rejected by constraint exclusion.
871+
* If so, it will have a cheapest_total_path that's an Append path
872+
* with no members (see set_plain_rel_pathlist).
872873
*/
873-
if (childrel->cheapest_total_path == NULL)
874-
{
875-
Assert(constraint_exclusion);
876-
continue;
877-
}
874+
if (IsA(childrel->cheapest_total_path, AppendPath) &&
875+
((AppendPath *) childrel->cheapest_total_path)->subpaths == NIL)
876+
continue; /* OK, we can ignore it */
878877

879878
/*
880879
* Get the best innerjoin indexpath (if any) for this outer rel.

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

Lines changed: 50 additions & 2 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.117 2006/01/31 21:39:24 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.118 2006/02/04 23:03:20 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -25,6 +25,7 @@
2525
#include "nodes/makefuncs.h"
2626
#include "optimizer/clauses.h"
2727
#include "optimizer/plancat.h"
28+
#include "optimizer/predtest.h"
2829
#include "optimizer/prep.h"
2930
#include "optimizer/tlist.h"
3031
#include "parser/parsetree.h"
@@ -40,8 +41,13 @@
4041
#include "miscadmin.h"
4142

4243

44+
/* GUC parameter */
45+
bool constraint_exclusion = false;
46+
47+
4348
static void estimate_rel_size(Relation rel, int32 *attr_widths,
4449
BlockNumber *pages, double *tuples);
50+
static List *get_relation_constraints(Oid relationObjectId, RelOptInfo *rel);
4551

4652

4753
/*
@@ -360,7 +366,7 @@ estimate_rel_size(Relation rel, int32 *attr_widths,
360366
* run, and in many cases it won't be invoked at all, so there seems no
361367
* point in caching the data in RelOptInfo.
362368
*/
363-
List *
369+
static List *
364370
get_relation_constraints(Oid relationObjectId, RelOptInfo *rel)
365371
{
366372
List *result = NIL;
@@ -424,6 +430,48 @@ get_relation_constraints(Oid relationObjectId, RelOptInfo *rel)
424430
}
425431

426432

433+
/*
434+
* relation_excluded_by_constraints
435+
*
436+
* Detect whether the relation need not be scanned because it has CHECK
437+
* constraints that conflict with the query's WHERE clause.
438+
*/
439+
bool
440+
relation_excluded_by_constraints(RelOptInfo *rel, RangeTblEntry *rte)
441+
{
442+
List *constraint_pred;
443+
444+
/* Skip the test if constraint exclusion is disabled */
445+
if (!constraint_exclusion)
446+
return false;
447+
448+
/* Only plain relations have constraints */
449+
if (rte->rtekind != RTE_RELATION || rte->inh)
450+
return false;
451+
452+
/* OK to fetch the constraint expressions */
453+
constraint_pred = get_relation_constraints(rte->relid, rel);
454+
455+
/*
456+
* We do not currently enforce that CHECK constraints contain only
457+
* immutable functions, so it's necessary to check here. We daren't draw
458+
* conclusions from plan-time evaluation of non-immutable functions.
459+
*/
460+
if (contain_mutable_functions((Node *) constraint_pred))
461+
return false;
462+
463+
/*
464+
* The constraints are effectively ANDed together, so we can just try to
465+
* refute the entire collection at once. This may allow us to make proofs
466+
* that would fail if we took them individually.
467+
*/
468+
if (predicate_refuted_by(constraint_pred, rel->baserestrictinfo))
469+
return true;
470+
471+
return false;
472+
}
473+
474+
427475
/*
428476
* build_physical_tlist
429477
*

‎src/include/optimizer/plancat.h

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/optimizer/plancat.h,v 1.37 2005/07/23 21:05:48 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/optimizer/plancat.h,v 1.38 2006/02/04 23:03:20 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -19,7 +19,8 @@
1919

2020
extern void get_relation_info(Oid relationObjectId, RelOptInfo *rel);
2121

22-
extern List *get_relation_constraints(Oid relationObjectId, RelOptInfo *rel);
22+
extern bool relation_excluded_by_constraints(RelOptInfo *rel,
23+
RangeTblEntry *rte);
2324

2425
extern List *build_physical_tlist(PlannerInfo *root, RelOptInfo *rel);
2526

0 commit comments

Comments
 (0)