Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * planagg.c
4 : * Special planning for aggregate queries.
5 : *
6 : * This module tries to replace MIN/MAX aggregate functions by subqueries
7 : * of the form
8 : * (SELECT col FROM tab
9 : * WHERE col IS NOT NULL AND existing-quals
10 : * ORDER BY col ASC/DESC
11 : * LIMIT 1)
12 : * Given a suitable index on tab.col, this can be much faster than the
13 : * generic scan-all-the-rows aggregation plan. We can handle multiple
14 : * MIN/MAX aggregates by generating multiple subqueries, and their
15 : * orderings can be different. However, if the query contains any
16 : * non-optimizable aggregates, there's no point since we'll have to
17 : * scan all the rows anyway.
18 : *
19 : *
20 : * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
21 : * Portions Copyright (c) 1994, Regents of the University of California
22 : *
23 : *
24 : * IDENTIFICATION
25 : * src/backend/optimizer/plan/planagg.c
26 : *
27 : *-------------------------------------------------------------------------
28 : */
29 : #include "postgres.h"
30 :
31 : #include "access/htup_details.h"
32 : #include "catalog/pg_aggregate.h"
33 : #include "catalog/pg_type.h"
34 : #include "nodes/makefuncs.h"
35 : #include "nodes/nodeFuncs.h"
36 : #include "optimizer/cost.h"
37 : #include "optimizer/optimizer.h"
38 : #include "optimizer/pathnode.h"
39 : #include "optimizer/paths.h"
40 : #include "optimizer/planmain.h"
41 : #include "optimizer/subselect.h"
42 : #include "optimizer/tlist.h"
43 : #include "parser/parse_clause.h"
44 : #include "parser/parsetree.h"
45 : #include "rewrite/rewriteManip.h"
46 : #include "utils/lsyscache.h"
47 : #include "utils/syscache.h"
48 :
49 : static bool can_minmax_aggs(PlannerInfo *root, List **context);
50 : static bool build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo,
51 : Oid eqop, Oid sortop, bool reverse_sort,
52 : bool nulls_first);
53 : static void minmax_qp_callback(PlannerInfo *root, void *extra);
54 : static Oid fetch_agg_sort_op(Oid aggfnoid);
55 :
56 :
57 : /*
58 : * preprocess_minmax_aggregates - preprocess MIN/MAX aggregates
59 : *
60 : * Check to see whether the query contains MIN/MAX aggregate functions that
61 : * might be optimizable via indexscans. If it does, and all the aggregates
62 : * are potentially optimizable, then create a MinMaxAggPath and add it to
63 : * the (UPPERREL_GROUP_AGG, NULL) upperrel.
64 : *
65 : * This should be called by grouping_planner() just before it's ready to call
66 : * query_planner(), because we generate indexscan paths by cloning the
67 : * planner's state and invoking query_planner() on a modified version of
68 : * the query parsetree. Thus, all preprocessing needed before query_planner()
69 : * must already be done. This relies on the list of aggregates in
70 : * root->agginfos, so preprocess_aggrefs() must have been called already, too.
71 : */
72 : void
73 36566 : preprocess_minmax_aggregates(PlannerInfo *root)
74 : {
75 36566 : Query *parse = root->parse;
76 : FromExpr *jtnode;
77 : RangeTblRef *rtr;
78 : RangeTblEntry *rte;
79 : List *aggs_list;
80 : RelOptInfo *grouped_rel;
81 : ListCell *lc;
82 :
83 : /* minmax_aggs list should be empty at this point */
84 : Assert(root->minmax_aggs == NIL);
85 :
86 : /* Nothing to do if query has no aggregates */
87 36566 : if (!parse->hasAggs)
88 36156 : return;
89 :
90 : Assert(!parse->setOperations); /* shouldn't get here if a setop */
91 : Assert(parse->rowMarks == NIL); /* nor if FOR UPDATE */
92 :
93 : /*
94 : * Reject unoptimizable cases.
95 : *
96 : * We don't handle GROUP BY or windowing, because our current
97 : * implementations of grouping require looking at all the rows anyway, and
98 : * so there's not much point in optimizing MIN/MAX.
99 : */
100 36566 : if (parse->groupClause || list_length(parse->groupingSets) > 1 ||
101 32758 : parse->hasWindowFuncs)
102 3814 : return;
103 :
104 : /*
105 : * Reject if query contains any CTEs; there's no way to build an indexscan
106 : * on one so we couldn't succeed here. (If the CTEs are unreferenced,
107 : * that's not true, but it doesn't seem worth expending cycles to check.)
108 : */
109 32752 : if (parse->cteList)
110 74 : return;
111 :
112 : /*
113 : * We also restrict the query to reference exactly one table, since join
114 : * conditions can't be handled reasonably. (We could perhaps handle a
115 : * query containing cartesian-product joins, but it hardly seems worth the
116 : * trouble.) However, the single table could be buried in several levels
117 : * of FromExpr due to subqueries. Note the "single" table could be an
118 : * inheritance parent, too, including the case of a UNION ALL subquery
119 : * that's been flattened to an appendrel.
120 : */
121 32678 : jtnode = parse->jointree;
122 62060 : while (IsA(jtnode, FromExpr))
123 : {
124 32712 : if (list_length(jtnode->fromlist) != 1)
125 3330 : return;
126 29382 : jtnode = linitial(jtnode->fromlist);
127 : }
128 29348 : if (!IsA(jtnode, RangeTblRef))
129 1208 : return;
130 28140 : rtr = (RangeTblRef *) jtnode;
131 28140 : rte = planner_rt_fetch(rtr->rtindex, root);
132 28140 : if (rte->rtekind == RTE_RELATION)
133 : /* ordinary relation, ok */ ;
134 5414 : else if (rte->rtekind == RTE_SUBQUERY && rte->inh)
135 : /* flattened UNION ALL subquery, ok */ ;
136 : else
137 5360 : return;
138 :
139 : /*
140 : * Examine all the aggregates and verify all are MIN/MAX aggregates. Stop
141 : * as soon as we find one that isn't.
142 : */
143 22780 : aggs_list = NIL;
144 22780 : if (!can_minmax_aggs(root, &aggs_list))
145 22058 : return;
146 :
147 : /*
148 : * OK, there is at least the possibility of performing the optimization.
149 : * Build an access path for each aggregate. If any of the aggregates
150 : * prove to be non-indexable, give up; there is no point in optimizing
151 : * just some of them.
152 : */
153 1168 : foreach(lc, aggs_list)
154 : {
155 758 : MinMaxAggInfo *mminfo = (MinMaxAggInfo *) lfirst(lc);
156 : Oid eqop;
157 : bool reverse;
158 :
159 : /*
160 : * We'll need the equality operator that goes with the aggregate's
161 : * ordering operator.
162 : */
163 758 : eqop = get_equality_op_for_ordering_op(mminfo->aggsortop, &reverse);
164 758 : if (!OidIsValid(eqop)) /* shouldn't happen */
165 0 : elog(ERROR, "could not find equality operator for ordering operator %u",
166 : mminfo->aggsortop);
167 :
168 : /*
169 : * We can use either an ordering that gives NULLS FIRST or one that
170 : * gives NULLS LAST; furthermore there's unlikely to be much
171 : * performance difference between them, so it doesn't seem worth
172 : * costing out both ways if we get a hit on the first one. NULLS
173 : * FIRST is more likely to be available if the operator is a
174 : * reverse-sort operator, so try that first if reverse.
175 : */
176 758 : if (build_minmax_path(root, mminfo, eqop, mminfo->aggsortop, reverse, reverse))
177 446 : continue;
178 312 : if (build_minmax_path(root, mminfo, eqop, mminfo->aggsortop, reverse, !reverse))
179 0 : continue;
180 :
181 : /* No indexable path for this aggregate, so fail */
182 312 : return;
183 : }
184 :
185 : /*
186 : * OK, we can do the query this way. Prepare to create a MinMaxAggPath
187 : * node.
188 : *
189 : * First, create an output Param node for each agg. (If we end up not
190 : * using the MinMaxAggPath, we'll waste a PARAM_EXEC slot for each agg,
191 : * which is not worth worrying about. We can't wait till create_plan time
192 : * to decide whether to make the Param, unfortunately.)
193 : */
194 856 : foreach(lc, aggs_list)
195 : {
196 446 : MinMaxAggInfo *mminfo = (MinMaxAggInfo *) lfirst(lc);
197 :
198 446 : mminfo->param =
199 446 : SS_make_initplan_output_param(root,
200 446 : exprType((Node *) mminfo->target),
201 : -1,
202 446 : exprCollation((Node *) mminfo->target));
203 : }
204 :
205 : /*
206 : * Create a MinMaxAggPath node with the appropriate estimated costs and
207 : * other needed data, and add it to the UPPERREL_GROUP_AGG upperrel, where
208 : * it will compete against the standard aggregate implementation. (It
209 : * will likely always win, but we need not assume that here.)
210 : *
211 : * Note: grouping_planner won't have created this upperrel yet, but it's
212 : * fine for us to create it first. We will not have inserted the correct
213 : * consider_parallel value in it, but MinMaxAggPath paths are currently
214 : * never parallel-safe anyway, so that doesn't matter. Likewise, it
215 : * doesn't matter that we haven't filled FDW-related fields in the rel.
216 : * Also, because there are no rowmarks, we know that the processed_tlist
217 : * doesn't need to change anymore, so making the pathtarget now is safe.
218 : */
219 410 : grouped_rel = fetch_upper_rel(root, UPPERREL_GROUP_AGG, NULL);
220 410 : add_path(grouped_rel, (Path *)
221 410 : create_minmaxagg_path(root, grouped_rel,
222 : create_pathtarget(root,
223 : root->processed_tlist),
224 : aggs_list,
225 410 : (List *) parse->havingQual));
226 : }
227 :
228 : /*
229 : * can_minmax_aggs
230 : * Examine all the aggregates in the query, and check if they are
231 : * all MIN/MAX aggregates. If so, build a list of MinMaxAggInfo
232 : * nodes for them.
233 : *
234 : * Returns false if a non-MIN/MAX aggregate is found, true otherwise.
235 : */
236 : static bool
237 22780 : can_minmax_aggs(PlannerInfo *root, List **context)
238 : {
239 : ListCell *lc;
240 :
241 : /*
242 : * This function used to have to scan the query for itself, but now we can
243 : * just thumb through the AggInfo list made by preprocess_aggrefs.
244 : */
245 23862 : foreach(lc, root->agginfos)
246 : {
247 23140 : AggInfo *agginfo = lfirst_node(AggInfo, lc);
248 23140 : Aggref *aggref = linitial_node(Aggref, agginfo->aggrefs);
249 : Oid aggsortop;
250 : TargetEntry *curTarget;
251 : MinMaxAggInfo *mminfo;
252 :
253 : Assert(aggref->agglevelsup == 0);
254 23140 : if (list_length(aggref->args) != 1)
255 22058 : return false; /* it couldn't be MIN/MAX */
256 :
257 : /*
258 : * ORDER BY is usually irrelevant for MIN/MAX, but it can change the
259 : * outcome if the aggsortop's operator class recognizes non-identical
260 : * values as equal. For example, 4.0 and 4.00 are equal according to
261 : * numeric_ops, yet distinguishable. If MIN() receives more than one
262 : * value equal to 4.0 and no value less than 4.0, it is unspecified
263 : * which of those equal values MIN() returns. An ORDER BY expression
264 : * that differs for each of those equal values of the argument
265 : * expression makes the result predictable once again. This is a
266 : * niche requirement, and we do not implement it with subquery paths.
267 : * In any case, this test lets us reject ordered-set aggregates
268 : * quickly.
269 : */
270 14408 : if (aggref->aggorder != NIL)
271 456 : return false;
272 : /* note: we do not care if DISTINCT is mentioned ... */
273 :
274 : /*
275 : * We might implement the optimization when a FILTER clause is present
276 : * by adding the filter to the quals of the generated subquery. For
277 : * now, just punt.
278 : */
279 13952 : if (aggref->aggfilter != NULL)
280 442 : return false;
281 :
282 13510 : aggsortop = fetch_agg_sort_op(aggref->aggfnoid);
283 13510 : if (!OidIsValid(aggsortop))
284 12398 : return false; /* not a MIN/MAX aggregate */
285 :
286 1112 : curTarget = (TargetEntry *) linitial(aggref->args);
287 :
288 1112 : if (contain_mutable_functions((Node *) curTarget->expr))
289 6 : return false; /* not potentially indexable */
290 :
291 1106 : if (type_is_rowtype(exprType((Node *) curTarget->expr)))
292 24 : return false; /* IS NOT NULL would have weird semantics */
293 :
294 1082 : mminfo = makeNode(MinMaxAggInfo);
295 1082 : mminfo->aggfnoid = aggref->aggfnoid;
296 1082 : mminfo->aggsortop = aggsortop;
297 1082 : mminfo->target = curTarget->expr;
298 1082 : mminfo->subroot = NULL; /* don't compute path yet */
299 1082 : mminfo->path = NULL;
300 1082 : mminfo->pathcost = 0;
301 1082 : mminfo->param = NULL;
302 :
303 1082 : *context = lappend(*context, mminfo);
304 : }
305 722 : return true;
306 : }
307 :
308 : /*
309 : * build_minmax_path
310 : * Given a MIN/MAX aggregate, try to build an indexscan Path it can be
311 : * optimized with.
312 : *
313 : * If successful, stash the best path in *mminfo and return true.
314 : * Otherwise, return false.
315 : */
316 : static bool
317 1070 : build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo,
318 : Oid eqop, Oid sortop, bool reverse_sort, bool nulls_first)
319 : {
320 : PlannerInfo *subroot;
321 : Query *parse;
322 : TargetEntry *tle;
323 : List *tlist;
324 : NullTest *ntest;
325 : SortGroupClause *sortcl;
326 : RelOptInfo *final_rel;
327 : Path *sorted_path;
328 : Cost path_cost;
329 : double path_fraction;
330 :
331 : /*
332 : * We are going to construct what is effectively a sub-SELECT query, so
333 : * clone the current query level's state and adjust it to make it look
334 : * like a subquery. Any outer references will now be one level higher
335 : * than before. (This means that when we are done, there will be no Vars
336 : * of level 1, which is why the subquery can become an initplan.)
337 : */
338 1070 : subroot = (PlannerInfo *) palloc(sizeof(PlannerInfo));
339 1070 : memcpy(subroot, root, sizeof(PlannerInfo));
340 1070 : subroot->query_level++;
341 1070 : subroot->parent_root = root;
342 : /* reset subplan-related stuff */
343 1070 : subroot->plan_params = NIL;
344 1070 : subroot->outer_params = NULL;
345 1070 : subroot->init_plans = NIL;
346 1070 : subroot->agginfos = NIL;
347 1070 : subroot->aggtransinfos = NIL;
348 :
349 1070 : subroot->parse = parse = copyObject(root->parse);
350 1070 : IncrementVarSublevelsUp((Node *) parse, 1, 1);
351 :
352 : /* append_rel_list might contain outer Vars? */
353 1070 : subroot->append_rel_list = copyObject(root->append_rel_list);
354 1070 : IncrementVarSublevelsUp((Node *) subroot->append_rel_list, 1, 1);
355 : /* There shouldn't be any OJ info to translate, as yet */
356 : Assert(subroot->join_info_list == NIL);
357 : /* and we haven't made equivalence classes, either */
358 : Assert(subroot->eq_classes == NIL);
359 : /* and we haven't created PlaceHolderInfos, either */
360 : Assert(subroot->placeholder_list == NIL);
361 :
362 : /*----------
363 : * Generate modified query of the form
364 : * (SELECT col FROM tab
365 : * WHERE col IS NOT NULL AND existing-quals
366 : * ORDER BY col ASC/DESC
367 : * LIMIT 1)
368 : *----------
369 : */
370 : /* single tlist entry that is the aggregate target */
371 1070 : tle = makeTargetEntry(copyObject(mminfo->target),
372 : (AttrNumber) 1,
373 : pstrdup("agg_target"),
374 : false);
375 1070 : tlist = list_make1(tle);
376 1070 : subroot->processed_tlist = parse->targetList = tlist;
377 :
378 : /* No HAVING, no DISTINCT, no aggregates anymore */
379 1070 : parse->havingQual = NULL;
380 1070 : subroot->hasHavingQual = false;
381 1070 : parse->distinctClause = NIL;
382 1070 : parse->hasDistinctOn = false;
383 1070 : parse->hasAggs = false;
384 :
385 : /* Build "target IS NOT NULL" expression */
386 1070 : ntest = makeNode(NullTest);
387 1070 : ntest->nulltesttype = IS_NOT_NULL;
388 1070 : ntest->arg = copyObject(mminfo->target);
389 : /* we checked it wasn't a rowtype in can_minmax_aggs */
390 1070 : ntest->argisrow = false;
391 1070 : ntest->location = -1;
392 :
393 : /* User might have had that in WHERE already */
394 1070 : if (!list_member((List *) parse->jointree->quals, ntest))
395 1070 : parse->jointree->quals = (Node *)
396 1070 : lcons(ntest, (List *) parse->jointree->quals);
397 :
398 : /* Build suitable ORDER BY clause */
399 1070 : sortcl = makeNode(SortGroupClause);
400 1070 : sortcl->tleSortGroupRef = assignSortGroupRef(tle, subroot->processed_tlist);
401 1070 : sortcl->eqop = eqop;
402 1070 : sortcl->sortop = sortop;
403 1070 : sortcl->reverse_sort = reverse_sort;
404 1070 : sortcl->nulls_first = nulls_first;
405 1070 : sortcl->hashable = false; /* no need to make this accurate */
406 1070 : parse->sortClause = list_make1(sortcl);
407 :
408 : /* set up expressions for LIMIT 1 */
409 1070 : parse->limitOffset = NULL;
410 1070 : parse->limitCount = (Node *) makeConst(INT8OID, -1, InvalidOid,
411 : sizeof(int64),
412 : Int64GetDatum(1), false,
413 : FLOAT8PASSBYVAL);
414 :
415 : /*
416 : * Generate the best paths for this query, telling query_planner that we
417 : * have LIMIT 1.
418 : */
419 1070 : subroot->tuple_fraction = 1.0;
420 1070 : subroot->limit_tuples = 1.0;
421 :
422 1070 : final_rel = query_planner(subroot, minmax_qp_callback, NULL);
423 :
424 : /*
425 : * Since we didn't go through subquery_planner() to handle the subquery,
426 : * we have to do some of the same cleanup it would do, in particular cope
427 : * with params and initplans used within this subquery. (This won't
428 : * matter if we end up not using the subplan.)
429 : */
430 1070 : SS_identify_outer_params(subroot);
431 1070 : SS_charge_for_initplans(subroot, final_rel);
432 :
433 : /*
434 : * Get the best presorted path, that being the one that's cheapest for
435 : * fetching just one row. If there's no such path, fail.
436 : */
437 1070 : if (final_rel->rows > 1.0)
438 1034 : path_fraction = 1.0 / final_rel->rows;
439 : else
440 36 : path_fraction = 1.0;
441 :
442 : sorted_path =
443 1070 : get_cheapest_fractional_path_for_pathkeys(final_rel->pathlist,
444 : subroot->query_pathkeys,
445 : NULL,
446 : path_fraction);
447 1070 : if (!sorted_path)
448 624 : return false;
449 :
450 : /*
451 : * The path might not return exactly what we want, so fix that. (We
452 : * assume that this won't change any conclusions about which was the
453 : * cheapest path.)
454 : */
455 446 : sorted_path = apply_projection_to_path(subroot, final_rel, sorted_path,
456 : create_pathtarget(subroot,
457 : subroot->processed_tlist));
458 :
459 : /*
460 : * Determine cost to get just the first row of the presorted path.
461 : *
462 : * Note: cost calculation here should match
463 : * compare_fractional_path_costs().
464 : */
465 446 : path_cost = sorted_path->startup_cost +
466 446 : path_fraction * (sorted_path->total_cost - sorted_path->startup_cost);
467 :
468 : /* Save state for further processing */
469 446 : mminfo->subroot = subroot;
470 446 : mminfo->path = sorted_path;
471 446 : mminfo->pathcost = path_cost;
472 :
473 446 : return true;
474 : }
475 :
476 : /*
477 : * Compute query_pathkeys and other pathkeys during query_planner()
478 : */
479 : static void
480 1070 : minmax_qp_callback(PlannerInfo *root, void *extra)
481 : {
482 1070 : root->group_pathkeys = NIL;
483 1070 : root->window_pathkeys = NIL;
484 1070 : root->distinct_pathkeys = NIL;
485 :
486 1070 : root->sort_pathkeys =
487 1070 : make_pathkeys_for_sortclauses(root,
488 1070 : root->parse->sortClause,
489 1070 : root->parse->targetList);
490 :
491 1070 : root->query_pathkeys = root->sort_pathkeys;
492 1070 : }
493 :
494 : /*
495 : * Get the OID of the sort operator, if any, associated with an aggregate.
496 : * Returns InvalidOid if there is no such operator.
497 : */
498 : static Oid
499 13510 : fetch_agg_sort_op(Oid aggfnoid)
500 : {
501 : HeapTuple aggTuple;
502 : Form_pg_aggregate aggform;
503 : Oid aggsortop;
504 :
505 : /* fetch aggregate entry from pg_aggregate */
506 13510 : aggTuple = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(aggfnoid));
507 13510 : if (!HeapTupleIsValid(aggTuple))
508 0 : return InvalidOid;
509 13510 : aggform = (Form_pg_aggregate) GETSTRUCT(aggTuple);
510 13510 : aggsortop = aggform->aggsortop;
511 13510 : ReleaseSysCache(aggTuple);
512 :
513 13510 : return aggsortop;
514 : }
|
