From 3893127431b41a3341ac5ff9611c7ea0215b9110 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 4 Feb 2006 23:03:20 +0000 Subject: [PATCH] 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?) --- doc/src/sgml/config.sgml | 5 +-- doc/src/sgml/ddl.sgml | 19 +++----- src/backend/optimizer/path/allpaths.c | 81 +++++++++++++---------------------- src/backend/optimizer/path/joinpath.c | 15 +++---- src/backend/optimizer/util/plancat.c | 52 +++++++++++++++++++++- src/include/optimizer/plancat.h | 5 ++- 6 files changed, 97 insertions(+), 80 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 1de87b2441..0820c6cc9f 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,5 +1,5 @@ Server Configuration @@ -1986,8 +1986,7 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows When this parameter is on, the planner compares query conditions with table CHECK constraints, and omits scanning tables for which the conditions contradict the - constraints. (Presently this is done only for child tables of - inheritance scans.) For example: + constraints. For example: CREATE TABLE parent(key integer, ...); diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index e6b694ed7b..2342f8703c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1,4 +1,4 @@ - + Data Definition @@ -2179,7 +2179,7 @@ SELECT * from cities*; PostgreSQL supports basic table - partitioning. This section describes why and how you can implement + partitioning. This section describes why and how to implement partitioning as part of your database design. @@ -2237,7 +2237,7 @@ SELECT * from cities*; table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set. You should be familiar with inheritance (see ) before - attempting to implement partitioning. + attempting to set up partitioning. @@ -2579,8 +2579,7 @@ UNION ALL SELECT * FROM measurement_yy05mm12 UNION ALL SELECT * FROM measurement_yy06mm01; - However, constraint exclusion is currently not supported for - partitioned tables defined in this manner. Also, the need to + However, the need to recreate the view adds an extra step to adding and dropping individual partitions of the dataset. @@ -2679,8 +2678,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; optimized, since the planner cannot know what partitions the parameter value might select at runtime. For the same reason, stable functions such as CURRENT_DATE - must be avoided. Joining the partition key to a column of another - table will not be optimized, either. + must be avoided. @@ -2709,13 +2707,6 @@ CHECK ( x = 1::bigint ) - UPDATE and DELETE commands - against the master table do not currently perform constraint exclusion. - - - - - All constraints on all partitions of the master table are considered for constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 890bb59a12..d210090540 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.141 2006/02/03 21:08:49 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/allpaths.c,v 1.142 2006/02/04 23:03:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -26,7 +26,6 @@ #include "optimizer/paths.h" #include "optimizer/plancat.h" #include "optimizer/planner.h" -#include "optimizer/predtest.h" #include "optimizer/prep.h" #include "optimizer/var.h" #include "parser/parsetree.h" @@ -36,14 +35,12 @@ /* These parameters are set by GUC */ -bool constraint_exclusion = false; bool enable_geqo = false; /* just in case GUC doesn't set it */ int geqo_threshold; static void set_base_rel_pathlists(PlannerInfo *root); -static void set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, - Index rti, RangeTblEntry *rte); +static void set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti); static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte); static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, @@ -146,8 +143,7 @@ set_base_rel_pathlists(PlannerInfo *root) if (rel->reloptkind != RELOPT_BASEREL) continue; - set_rel_pathlist(root, rel, rti, - rt_fetch(rti, root->parse->rtable)); + set_rel_pathlist(root, rel, rti); } } @@ -156,9 +152,10 @@ set_base_rel_pathlists(PlannerInfo *root) * Build access paths for a base relation */ static void -set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, - Index rti, RangeTblEntry *rte) +set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti) { + RangeTblEntry *rte = rt_fetch(rti, root->parse->rtable); + if (rte->inh) { /* It's an "append relation", process accordingly */ @@ -208,6 +205,24 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) set_baserel_size_estimates(root, rel); /* + * If we can prove we don't need to scan the rel via constraint exclusion, + * set up a single dummy path for it. (Rather than inventing a special + * "dummy" path type, we represent this as an AppendPath with no members.) + */ + if (relation_excluded_by_constraints(rel, rte)) + { + /* Reset output-rows estimate to 0 */ + rel->rows = 0; + + add_path(rel, (Path *) create_append_path(rel, NIL)); + + /* Select cheapest path (pretty easy in this case...) */ + set_cheapest(rel); + + return; + } + + /* * Generate paths and add them to the rel's pathlist. * * Note: add_path() will discard any paths that are dominated by another @@ -273,7 +288,6 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l); int childRTindex; RelOptInfo *childrel; - RangeTblEntry *childrte; Path *childpath; ListCell *parentvars; ListCell *childvars; @@ -316,53 +330,18 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, childrel->max_attr); /* - * If we can prove we don't need to scan this child via constraint - * exclusion, just ignore it. (We have to have converted the - * baserestrictinfo Vars before we can make the test.) - * - * XXX it'd probably be better to give the child some kind of dummy - * cheapest path, or otherwise explicitly mark it as ignorable. - * Currently there is an ugly check in join_before_append() to handle - * excluded children. - */ - childrte = rt_fetch(childRTindex, root->parse->rtable); - if (constraint_exclusion && - childrte->rtekind == RTE_RELATION) - { - List *constraint_pred; - - constraint_pred = get_relation_constraints(childrte->relid, - childrel); - - /* - * We do not currently enforce that CHECK constraints contain only - * immutable functions, so it's necessary to check here. We - * daren't draw conclusions from plan-time evaluation of - * non-immutable functions. - */ - if (!contain_mutable_functions((Node *) constraint_pred)) - { - /* - * The constraints are effectively ANDed together, so we can - * just try to refute the entire collection at once. This may - * allow us to make proofs that would fail if we took them - * individually. - */ - if (predicate_refuted_by(constraint_pred, - childrel->baserestrictinfo)) - continue; - } - } - - /* - * Compute the child's access paths, and save the cheapest. + * Compute the child's access paths, and add the cheapest one + * to the Append path we are constructing for the parent. * * It's possible that the child is itself an appendrel, in which * case we can "cut out the middleman" and just add its child * paths to our own list. (We don't try to do this earlier because * we need to apply both levels of transformation to the quals.) + * This test also handles the case where the child rel need not + * be scanned because of constraint exclusion: it'll have an + * Append path with no subpaths, and will vanish from our list. */ - set_rel_pathlist(root, childrel, childRTindex, childrte); + set_rel_pathlist(root, childrel, childRTindex); childpath = childrel->cheapest_total_path; if (IsA(childpath, AppendPath)) diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index df3bd12874..450bc26bed 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.100 2006/02/03 21:08:49 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/joinpath.c,v 1.101 2006/02/04 23:03:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -867,14 +867,13 @@ join_before_append(PlannerInfo *root, Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL); /* - * If the child has no cheapest_total_path, assume it was deemed - * excludable by constraint exclusion (see set_append_rel_pathlist). + * Check to see if child was rejected by constraint exclusion. + * If so, it will have a cheapest_total_path that's an Append path + * with no members (see set_plain_rel_pathlist). */ - if (childrel->cheapest_total_path == NULL) - { - Assert(constraint_exclusion); - continue; - } + if (IsA(childrel->cheapest_total_path, AppendPath) && + ((AppendPath *) childrel->cheapest_total_path)->subpaths == NIL) + continue; /* OK, we can ignore it */ /* * Get the best innerjoin indexpath (if any) for this outer rel. diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index c926ae5d48..b6dcfa144f 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.117 2006/01/31 21:39:24 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/util/plancat.c,v 1.118 2006/02/04 23:03:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -25,6 +25,7 @@ #include "nodes/makefuncs.h" #include "optimizer/clauses.h" #include "optimizer/plancat.h" +#include "optimizer/predtest.h" #include "optimizer/prep.h" #include "optimizer/tlist.h" #include "parser/parsetree.h" @@ -40,8 +41,13 @@ #include "miscadmin.h" +/* GUC parameter */ +bool constraint_exclusion = false; + + static void estimate_rel_size(Relation rel, int32 *attr_widths, BlockNumber *pages, double *tuples); +static List *get_relation_constraints(Oid relationObjectId, RelOptInfo *rel); /* @@ -360,7 +366,7 @@ estimate_rel_size(Relation rel, int32 *attr_widths, * run, and in many cases it won't be invoked at all, so there seems no * point in caching the data in RelOptInfo. */ -List * +static List * get_relation_constraints(Oid relationObjectId, RelOptInfo *rel) { List *result = NIL; @@ -425,6 +431,48 @@ get_relation_constraints(Oid relationObjectId, RelOptInfo *rel) /* + * relation_excluded_by_constraints + * + * Detect whether the relation need not be scanned because it has CHECK + * constraints that conflict with the query's WHERE clause. + */ +bool +relation_excluded_by_constraints(RelOptInfo *rel, RangeTblEntry *rte) +{ + List *constraint_pred; + + /* Skip the test if constraint exclusion is disabled */ + if (!constraint_exclusion) + return false; + + /* Only plain relations have constraints */ + if (rte->rtekind != RTE_RELATION || rte->inh) + return false; + + /* OK to fetch the constraint expressions */ + constraint_pred = get_relation_constraints(rte->relid, rel); + + /* + * We do not currently enforce that CHECK constraints contain only + * immutable functions, so it's necessary to check here. We daren't draw + * conclusions from plan-time evaluation of non-immutable functions. + */ + if (contain_mutable_functions((Node *) constraint_pred)) + return false; + + /* + * The constraints are effectively ANDed together, so we can just try to + * refute the entire collection at once. This may allow us to make proofs + * that would fail if we took them individually. + */ + if (predicate_refuted_by(constraint_pred, rel->baserestrictinfo)) + return true; + + return false; +} + + +/* * build_physical_tlist * * Build a targetlist consisting of exactly the relation's user attributes, diff --git a/src/include/optimizer/plancat.h b/src/include/optimizer/plancat.h index 8a4c1e4941..888105605b 100644 --- a/src/include/optimizer/plancat.h +++ b/src/include/optimizer/plancat.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/optimizer/plancat.h,v 1.37 2005/07/23 21:05:48 tgl Exp $ + * $PostgreSQL: pgsql/src/include/optimizer/plancat.h,v 1.38 2006/02/04 23:03:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -19,7 +19,8 @@ extern void get_relation_info(Oid relationObjectId, RelOptInfo *rel); -extern List *get_relation_constraints(Oid relationObjectId, RelOptInfo *rel); +extern bool relation_excluded_by_constraints(RelOptInfo *rel, + RangeTblEntry *rte); extern List *build_physical_tlist(PlannerInfo *root, RelOptInfo *rel); -- 2.11.0