1 /*-------------------------------------------------------------------------
4 * Routines to plan a single query
6 * What's in a name, anyway? The top-level entry point of the planner/
7 * optimizer is over in planner.c, not here as you might think from the
8 * file name. But this is the main code for planning a basic join operation,
9 * shorn of features like subselects, inheritance, aggregates, grouping,
10 * and so on. (Those are the things planner.c deals with.)
12 * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
13 * Portions Copyright (c) 1994, Regents of the University of California
17 * $PostgreSQL: pgsql/src/backend/optimizer/plan/planmain.c,v 1.92 2006/01/31 21:39:24 tgl Exp $
19 *-------------------------------------------------------------------------
23 #include "optimizer/clauses.h"
24 #include "optimizer/cost.h"
25 #include "optimizer/pathnode.h"
26 #include "optimizer/paths.h"
27 #include "optimizer/planmain.h"
28 #include "optimizer/tlist.h"
29 #include "utils/selfuncs.h"
34 * Generate a path (that is, a simplified plan) for a basic query,
35 * which may involve joins but not any fancier features.
37 * Since query_planner does not handle the toplevel processing (grouping,
38 * sorting, etc) it cannot select the best path by itself. It selects
39 * two paths: the cheapest path that produces all the required tuples,
40 * independent of any ordering considerations, and the cheapest path that
41 * produces the expected fraction of the required tuples in the required
42 * ordering, if there is a path that is cheaper for this than just sorting
43 * the output of the cheapest overall path. The caller (grouping_planner)
44 * will make the final decision about which to use.
47 * root describes the query to plan
48 * tlist is the target list the query should produce
49 * (this is NOT necessarily root->parse->targetList!)
50 * tuple_fraction is the fraction of tuples we expect will be retrieved
53 * *cheapest_path receives the overall-cheapest path for the query
54 * *sorted_path receives the cheapest presorted path for the query,
55 * if any (NULL if there is no useful presorted path)
56 * *num_groups receives the estimated number of groups, or 1 if query
57 * does not use grouping
59 * Note: the PlannerInfo node also includes a query_pathkeys field, which is
60 * both an input and an output of query_planner(). The input value signals
61 * query_planner that the indicated sort order is wanted in the final output
62 * plan. But this value has not yet been "canonicalized", since the needed
63 * info does not get computed until we scan the qual clauses. We canonicalize
64 * it as soon as that task is done. (The main reason query_pathkeys is a
65 * PlannerInfo field and not a passed parameter is that the low-level routines
66 * in indxpath.c need to see it.)
68 * Note: the PlannerInfo node also includes group_pathkeys and sort_pathkeys,
69 * which like query_pathkeys need to be canonicalized once the info is
72 * tuple_fraction is interpreted as follows:
73 * 0: expect all tuples to be retrieved (normal case)
74 * 0 < tuple_fraction < 1: expect the given fraction of tuples available
75 * from the plan to be retrieved
76 * tuple_fraction >= 1: tuple_fraction is the absolute number of tuples
77 * expected to be retrieved (ie, a LIMIT specification)
80 query_planner(PlannerInfo *root, List *tlist, double tuple_fraction,
81 Path **cheapest_path, Path **sorted_path,
84 Query *parse = root->parse;
87 RelOptInfo *final_rel;
91 /* Make tuple_fraction accessible to lower-level routines */
92 root->tuple_fraction = tuple_fraction;
94 *num_groups = 1; /* default result */
97 * If the query has an empty join tree, then it's something easy like
98 * "SELECT 2+2;" or "INSERT ... VALUES()". Fall through quickly.
100 if (parse->jointree->fromlist == NIL)
102 *cheapest_path = (Path *) create_result_path(NULL, NULL,
103 (List *) parse->jointree->quals);
109 * Pull out any non-variable WHERE clauses so these can be put in a
110 * toplevel "Result" node, where they will gate execution of the whole
111 * plan (the Result will not invoke its descendant plan unless the quals
112 * are true). Note that any *really* non-variable quals will have been
113 * optimized away by eval_const_expressions(). What we're mostly
114 * interested in here is quals that depend only on outer-level vars,
115 * although if the qual reduces to "WHERE FALSE" this path will also be
118 parse->jointree->quals = (Node *)
119 pull_constant_clauses((List *) parse->jointree->quals,
123 * Init planner lists to empty, and set up the array to hold RelOptInfos
126 * NOTE: in_info_list and append_rel_list were set up by subquery_planner,
129 root->simple_rel_array_size = list_length(parse->rtable) + 1;
130 root->simple_rel_array = (RelOptInfo **)
131 palloc0(root->simple_rel_array_size * sizeof(RelOptInfo *));
132 root->join_rel_list = NIL;
133 root->join_rel_hash = NULL;
134 root->equi_key_list = NIL;
135 root->left_join_clauses = NIL;
136 root->right_join_clauses = NIL;
137 root->full_join_clauses = NIL;
138 root->oj_info_list = NIL;
141 * Construct RelOptInfo nodes for all base relations in query.
143 add_base_rels_to_query(root, (Node *) parse->jointree);
146 * Examine the targetlist and qualifications, adding entries to baserel
147 * targetlists for all referenced Vars. Restrict and join clauses are
148 * added to appropriate lists belonging to the mentioned relations. We
149 * also build lists of equijoined keys for pathkey construction, and
150 * form a target joinlist for make_one_rel() to work from.
152 * Note: all subplan nodes will have "flat" (var-only) tlists. This
153 * implies that all expression evaluations are done at the root of the
154 * plan tree. Once upon a time there was code to try to push expensive
155 * function calls down to lower plan nodes, but that's dead code and has
156 * been for a long time...
158 build_base_rel_tlists(root, tlist);
160 joinlist = deconstruct_jointree(root);
163 * Use the completed lists of equijoined keys to deduce any implied but
164 * unstated equalities (for example, A=B and B=C imply A=C).
166 generate_implied_equalities(root);
169 * We should now have all the pathkey equivalence sets built, so it's now
170 * possible to convert the requested query_pathkeys to canonical form.
171 * Also canonicalize the groupClause and sortClause pathkeys for use
174 root->query_pathkeys = canonicalize_pathkeys(root, root->query_pathkeys);
175 root->group_pathkeys = canonicalize_pathkeys(root, root->group_pathkeys);
176 root->sort_pathkeys = canonicalize_pathkeys(root, root->sort_pathkeys);
179 * Ready to do the primary planning.
181 final_rel = make_one_rel(root, joinlist);
183 if (!final_rel || !final_rel->cheapest_total_path)
184 elog(ERROR, "failed to construct the join relation");
187 * If there's grouping going on, estimate the number of result groups. We
188 * couldn't do this any earlier because it depends on relation size
189 * estimates that were set up above.
191 * Then convert tuple_fraction to fractional form if it is absolute, and
192 * adjust it based on the knowledge that grouping_planner will be doing
193 * grouping or aggregation work with our result.
195 * This introduces some undesirable coupling between this code and
196 * grouping_planner, but the alternatives seem even uglier; we couldn't
197 * pass back completed paths without making these decisions here.
199 if (parse->groupClause)
203 groupExprs = get_sortgrouplist_exprs(parse->groupClause,
205 *num_groups = estimate_num_groups(root,
210 * In GROUP BY mode, an absolute LIMIT is relative to the number of
211 * groups not the number of tuples. If the caller gave us a fraction,
212 * keep it as-is. (In both cases, we are effectively assuming that
213 * all the groups are about the same size.)
215 if (tuple_fraction >= 1.0)
216 tuple_fraction /= *num_groups;
219 * If both GROUP BY and ORDER BY are specified, we will need two
220 * levels of sort --- and, therefore, certainly need to read all the
221 * tuples --- unless ORDER BY is a subset of GROUP BY.
223 if (parse->groupClause && parse->sortClause &&
224 !pathkeys_contained_in(root->sort_pathkeys, root->group_pathkeys))
225 tuple_fraction = 0.0;
227 else if (parse->hasAggs || root->hasHavingQual)
230 * Ungrouped aggregate will certainly want to read all the tuples, and
231 * it will deliver a single result row (so leave *num_groups 1).
233 tuple_fraction = 0.0;
235 else if (parse->distinctClause)
238 * Since there was no grouping or aggregation, it's reasonable to
239 * assume the UNIQUE filter has effects comparable to GROUP BY. Return
240 * the estimated number of output rows for use by caller. (If DISTINCT
241 * is used with grouping, we ignore its effects for rowcount
242 * estimation purposes; this amounts to assuming the grouped rows are
247 distinctExprs = get_sortgrouplist_exprs(parse->distinctClause,
249 *num_groups = estimate_num_groups(root,
254 * Adjust tuple_fraction the same way as for GROUP BY, too.
256 if (tuple_fraction >= 1.0)
257 tuple_fraction /= *num_groups;
262 * Plain non-grouped, non-aggregated query: an absolute tuple fraction
263 * can be divided by the number of tuples.
265 if (tuple_fraction >= 1.0)
266 tuple_fraction /= final_rel->rows;
270 * Pick out the cheapest-total path and the cheapest presorted path for
271 * the requested pathkeys (if there is one). We should take the tuple
272 * fraction into account when selecting the cheapest presorted path, but
273 * not when selecting the cheapest-total path, since if we have to sort
274 * then we'll have to fetch all the tuples. (But there's a special case:
275 * if query_pathkeys is NIL, meaning order doesn't matter, then the
276 * "cheapest presorted" path will be the cheapest overall for the tuple
279 * The cheapest-total path is also the one to use if grouping_planner
280 * decides to use hashed aggregation, so we return it separately even if
281 * this routine thinks the presorted path is the winner.
283 cheapestpath = final_rel->cheapest_total_path;
286 get_cheapest_fractional_path_for_pathkeys(final_rel->pathlist,
287 root->query_pathkeys,
290 /* Don't return same path in both guises; just wastes effort */
291 if (sortedpath == cheapestpath)
295 * Forget about the presorted path if it would be cheaper to sort the
296 * cheapest-total path. Here we need consider only the behavior at the
297 * tuple fraction point.
301 Path sort_path; /* dummy for result of cost_sort */
303 if (root->query_pathkeys == NIL ||
304 pathkeys_contained_in(root->query_pathkeys,
305 cheapestpath->pathkeys))
307 /* No sort needed for cheapest path */
308 sort_path.startup_cost = cheapestpath->startup_cost;
309 sort_path.total_cost = cheapestpath->total_cost;
313 /* Figure cost for sorting */
314 cost_sort(&sort_path, root, root->query_pathkeys,
315 cheapestpath->total_cost,
316 final_rel->rows, final_rel->width);
319 if (compare_fractional_path_costs(sortedpath, &sort_path,
322 /* Presorted path is a loser */
328 * If we have constant quals, add a toplevel Result step to process them.
332 cheapestpath = (Path *) create_result_path(final_rel,
336 sortedpath = (Path *) create_result_path(final_rel,
341 *cheapest_path = cheapestpath;
342 *sorted_path = sortedpath;