Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit42473b3

Browse files
committed
Have the planner replace COUNT(ANY) with COUNT(*), when possible
This adds SupportRequestSimplifyAggref to allow pg_proc.prosupportfunctions to receive an Aggref and allow them to determine if there is away that the Aggref call can be optimized.Also added is a support function to allow transformation of COUNT(ANY)into COUNT(*). This is possible to do when the given "ANY" cannot beNULL and also that there are no ORDER BY / DISTINCT clauses within theAggref. This is a useful transformation to do as it is common thatpeople write COUNT(1), which until now has added unneeded overhead.When counting a NOT NULL column. The overheads can be worse as thatmight mean deforming more of the tuple, which for large fact tables maybe many columns in.It may be possible to add prosupport functions for other aggregates. Wecould consider if ORDER BY could be dropped for some calls, e.g. theORDER BY is quite useless in MAX(c ORDER BY c).There is a little bit of passing fallout from adjustingexpr_is_nonnullable() to handle Const which results in a plan change inthe aggregates.out regression test. Previously, nothing was able todetermine that "One-Time Filter: (100 IS NOT NULL)" was always true,therefore useless to include in the plan.Author: David Rowley <dgrowleyml@gmail.com>Reviewed-by: Corey Huinker <corey.huinker@gmail.com>Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com>Discussion:https://postgr.es/m/CAApHDvqGcPTagXpKfH=CrmHBqALpziThJEDs_MrPqjKVeDF9wA@mail.gmail.com
1 parentdbdc717 commit42473b3

File tree

9 files changed

+324
-36
lines changed

9 files changed

+324
-36
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2975,9 +2975,9 @@ select sum(t1.c1), count(t2.c1) from ft1 t1 inner join ft2 t2 on (t1.c1 = t2.c1)
29752975
QUERY PLAN
29762976
----------------------------------------------------------------------------------------------------------------------------
29772977
Aggregate
2978-
Output: sum(t1.c1), count(t2.c1)
2978+
Output: sum(t1.c1), count(*)
29792979
-> Foreign Scan
2980-
Output: t1.c1, t2.c1
2980+
Output: t1.c1
29812981
Filter: (((((t1.c1 * t2.c1) / (t1.c1 * t2.c1)))::double precision * random()) <= '1'::double precision)
29822982
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
29832983
Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1"))))
@@ -3073,12 +3073,12 @@ select c2 * (random() <= 1)::int as c2 from ft2 group by c2 * (random() <= 1)::i
30733073
-- GROUP BY clause in various forms, cardinal, alias and constant expression
30743074
explain (verbose, costs off)
30753075
select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2;
3076-
QUERY PLAN
3077-
------------------------------------------------------------------------------------------------------------
3076+
QUERY PLAN
3077+
-----------------------------------------------------------------------------------------------------------
30783078
Foreign Scan
3079-
Output: (count(c2)), c2, 5, 7.0, 9
3079+
Output: (count(*)), c2, 5, 7.0, 9
30803080
Relations: Aggregate on (public.ft1)
3081-
Remote SQL: SELECT count(c2), c2, 5, 7.0, 9 FROM "S 1"."T 1" GROUP BY 2, 3, 5 ORDER BY c2 ASC NULLS LAST
3081+
Remote SQL: SELECT count(*), c2, 5, 7.0, 9 FROM "S 1"."T 1" GROUP BY 2, 3, 5 ORDER BY c2 ASC NULLS LAST
30823082
(4 rows)
30833083

30843084
select count(c2) w, c2 x, 5 y, 7.0 z from ft1 group by 2, y, 9.0::int order by 2;
@@ -3379,8 +3379,8 @@ select distinct (select count(*) filter (where t2.c2 = 6 and t2.c1 < 10) from ft
33793379
-- Inner query is aggregation query
33803380
explain (verbose, costs off)
33813381
select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1;
3382-
QUERY PLAN
3383-
------------------------------------------------------------------------------------------------------------------------------------------------------
3382+
QUERY PLAN
3383+
--------------------------------------------------------------------------------------------------------------------------------------------------
33843384
Unique
33853385
Output: ((SubPlan expr_1))
33863386
-> Sort
@@ -3391,9 +3391,9 @@ select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) fro
33913391
Remote SQL: SELECT "C 1", c2 FROM "S 1"."T 1" WHERE (((c2 % 6) = 0))
33923392
SubPlan expr_1
33933393
-> Foreign Scan
3394-
Output: (count(t1.c1) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10))))
3394+
Output: (count(*) FILTER (WHERE ((t2.c2 = 6) AND (t2.c1 < 10))))
33953395
Relations: Aggregate on (public.ft1 t1)
3396-
Remote SQL: SELECT count("C 1") FILTER (WHERE (($1::integer = 6) AND ($2::integer < 10))) FROM "S 1"."T 1" WHERE (("C 1" = 6))
3396+
Remote SQL: SELECT count(*) FILTER (WHERE (($1::integer = 6) AND ($2::integer < 10))) FROM "S 1"."T 1" WHERE (("C 1" = 6))
33973397
(13 rows)
33983398

33993399
select distinct (select count(t1.c1) filter (where t2.c2 = 6 and t2.c1 < 10) from ft1 t1 where t1.c1 = 6) from ft2 t2 where t2.c2 % 6 = 0 order by 1;

‎src/backend/optimizer/plan/initsplan.c‎

Lines changed: 2 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -3413,22 +3413,6 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
34133413
restrictinfo->security_level);
34143414
}
34153415

3416-
/*
3417-
* expr_is_nonnullable
3418-
* Check to see if the Expr cannot be NULL
3419-
*
3420-
* Currently we only support simple Vars.
3421-
*/
3422-
staticbool
3423-
expr_is_nonnullable(PlannerInfo*root,Expr*expr)
3424-
{
3425-
/* For now only check simple Vars */
3426-
if (!IsA(expr,Var))
3427-
return false;
3428-
3429-
returnvar_is_nonnullable(root, (Var*)expr, true);
3430-
}
3431-
34323416
/*
34333417
* restriction_is_always_true
34343418
* Check to see if the RestrictInfo is always true.
@@ -3465,7 +3449,7 @@ restriction_is_always_true(PlannerInfo *root,
34653449
if (nulltest->argisrow)
34663450
return false;
34673451

3468-
returnexpr_is_nonnullable(root,nulltest->arg);
3452+
returnexpr_is_nonnullable(root,nulltest->arg, true);
34693453
}
34703454

34713455
/* If it's an OR, check its sub-clauses */
@@ -3530,7 +3514,7 @@ restriction_is_always_false(PlannerInfo *root,
35303514
if (nulltest->argisrow)
35313515
return false;
35323516

3533-
returnexpr_is_nonnullable(root,nulltest->arg);
3517+
returnexpr_is_nonnullable(root,nulltest->arg, true);
35343518
}
35353519

35363520
/* If it's an OR, check its sub-clauses */

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

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -131,6 +131,8 @@ static Expr *simplify_function(Oid funcid,
131131
Oidresult_collid,Oidinput_collid,List**args_p,
132132
boolfuncvariadic,boolprocess_args,boolallow_non_const,
133133
eval_const_expressions_context*context);
134+
staticNode*simplify_aggref(Aggref*aggref,
135+
eval_const_expressions_context*context);
134136
staticList*reorder_function_arguments(List*args,intpronargs,
135137
HeapTuplefunc_tuple);
136138
staticList*add_function_defaults(List*args,intpronargs,
@@ -2634,6 +2636,9 @@ eval_const_expressions_mutator(Node *node,
26342636
newexpr->location=expr->location;
26352637
return (Node*)newexpr;
26362638
}
2639+
caseT_Aggref:
2640+
node=ece_generic_processing(node);
2641+
returnsimplify_aggref((Aggref*)node,context);
26372642
caseT_OpExpr:
26382643
{
26392644
OpExpr*expr= (OpExpr*)node;
@@ -4200,6 +4205,50 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
42004205
returnnewexpr;
42014206
}
42024207

4208+
/*
4209+
* simplify_aggref
4210+
*Call the Aggref.aggfnoid's prosupport function to allow it to
4211+
*determine if simplification of the Aggref is possible. Returns the
4212+
*newly simplified node if conversion took place; otherwise, returns the
4213+
*original Aggref.
4214+
*
4215+
* See SupportRequestSimplifyAggref comments in supportnodes.h for further
4216+
* details.
4217+
*/
4218+
staticNode*
4219+
simplify_aggref(Aggref*aggref,eval_const_expressions_context*context)
4220+
{
4221+
Oidprosupport=get_func_support(aggref->aggfnoid);
4222+
4223+
if (OidIsValid(prosupport))
4224+
{
4225+
SupportRequestSimplifyAggrefreq;
4226+
Node*newnode;
4227+
4228+
/*
4229+
* Build a SupportRequestSimplifyAggref node to pass to the support
4230+
* function.
4231+
*/
4232+
req.type=T_SupportRequestSimplifyAggref;
4233+
req.root=context->root;
4234+
req.aggref=aggref;
4235+
4236+
newnode= (Node*)DatumGetPointer(OidFunctionCall1(prosupport,
4237+
PointerGetDatum(&req)));
4238+
4239+
/*
4240+
* We expect the support function to return either a new Node or NULL
4241+
* (when simplification isn't possible).
4242+
*/
4243+
Assert(newnode!= (Node*)aggref||newnode==NULL);
4244+
4245+
if (newnode!=NULL)
4246+
returnnewnode;
4247+
}
4248+
4249+
return (Node*)aggref;
4250+
}
4251+
42034252
/*
42044253
* var_is_nonnullable: check to see if the Var cannot be NULL
42054254
*
@@ -4261,6 +4310,30 @@ var_is_nonnullable(PlannerInfo *root, Var *var, bool use_rel_info)
42614310
return false;
42624311
}
42634312

4313+
/*
4314+
* expr_is_nonnullable: check to see if the Expr cannot be NULL
4315+
*
4316+
* Returns true iff the given 'expr' cannot produce SQL NULLs.
4317+
*
4318+
* If 'use_rel_info' is true, nullability of Vars is checked via the
4319+
* corresponding RelOptInfo for the given Var. Some callers require
4320+
* nullability information before RelOptInfos are generated. These should
4321+
* pass 'use_rel_info' as false.
4322+
*
4323+
* For now, we only support Var and Const. Support for other node types may
4324+
* be possible.
4325+
*/
4326+
bool
4327+
expr_is_nonnullable(PlannerInfo*root,Expr*expr,booluse_rel_info)
4328+
{
4329+
if (IsA(expr,Var))
4330+
returnvar_is_nonnullable(root, (Var*)expr,use_rel_info);
4331+
if (IsA(expr,Const))
4332+
return !castNode(Const,expr)->constisnull;
4333+
4334+
return false;
4335+
}
4336+
42644337
/*
42654338
* expand_function_arguments: convert named-notation args to positional args
42664339
* and/or insert default args, as needed

‎src/backend/utils/adt/int8.c‎

Lines changed: 48 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,7 @@
2424
#include"nodes/supportnodes.h"
2525
#include"optimizer/optimizer.h"
2626
#include"utils/builtins.h"
27-
27+
#include"utils/fmgroids.h"
2828

2929
typedefstruct
3030
{
@@ -811,6 +811,53 @@ int8inc_support(PG_FUNCTION_ARGS)
811811
PG_RETURN_POINTER(req);
812812
}
813813

814+
if (IsA(rawreq,SupportRequestSimplifyAggref))
815+
{
816+
SupportRequestSimplifyAggref*req= (SupportRequestSimplifyAggref*)rawreq;
817+
Aggref*agg=req->aggref;
818+
819+
/*
820+
* Check for COUNT(ANY) and try to convert to COUNT(*). The input
821+
* argument cannot be NULL, we can't have an ORDER BY / DISTINCT in
822+
* the aggregate, and agglevelsup must be 0.
823+
*
824+
* Technically COUNT(ANY) must have 1 arg, but be paranoid and check.
825+
*/
826+
if (agg->aggfnoid==F_COUNT_ANY&&list_length(agg->args)==1)
827+
{
828+
TargetEntry*tle= (TargetEntry*)linitial(agg->args);
829+
Expr*arg=tle->expr;
830+
831+
/* Check for unsupported cases */
832+
if (agg->aggdistinct!=NIL||agg->aggorder!=NIL||
833+
agg->agglevelsup!=0)
834+
PG_RETURN_POINTER(NULL);
835+
836+
/* If the arg isn't NULLable, do the conversion */
837+
if (expr_is_nonnullable(req->root,arg, false))
838+
{
839+
Aggref*newagg;
840+
841+
/* We don't expect these to have been set yet */
842+
Assert(agg->aggtransno==-1);
843+
Assert(agg->aggtranstype==InvalidOid);
844+
845+
/* Convert COUNT(ANY) to COUNT(*) by making a new Aggref */
846+
newagg=makeNode(Aggref);
847+
memcpy(newagg,agg,sizeof(Aggref));
848+
newagg->aggfnoid=F_COUNT_;
849+
850+
/* count(*) has no args */
851+
newagg->aggargtypes=NULL;
852+
newagg->args=NULL;
853+
newagg->aggstar= true;
854+
newagg->location=-1;
855+
856+
PG_RETURN_POINTER(newagg);
857+
}
858+
}
859+
}
860+
814861
PG_RETURN_POINTER(NULL);
815862
}
816863

‎src/include/nodes/supportnodes.h‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,31 @@ typedef struct SupportRequestSimplify
7171
FuncExpr*fcall;/* Function call to be simplified */
7272
}SupportRequestSimplify;
7373

74+
/*
75+
* Similar to SupportRequestSimplify but for Aggref node types.
76+
*
77+
* This supports conversions such as swapping COUNT(1) or COUNT(notnullcol)
78+
* for COUNT(*).
79+
*
80+
* Supporting functions can consult 'root' and the input 'aggref'. When the
81+
* implementing support function deems the simplification is possible, it must
82+
* create a new Node (probably another Aggref) and not modify the original.
83+
* The newly created Node should then be returned to indicate that the
84+
* conversion is to take place. When no conversion is possible, a NULL
85+
* pointer should be returned.
86+
*
87+
* It is important to consider that implementing support functions can receive
88+
* Aggrefs with agglevelsup > 0. Careful consideration should be given to
89+
* whether the simplification is still possible at levels above 0.
90+
*/
91+
typedefstructSupportRequestSimplifyAggref
92+
{
93+
NodeTagtype;
94+
95+
PlannerInfo*root;/* Planner's infrastructure */
96+
Aggref*aggref;/* Aggref to be simplified */
97+
}SupportRequestSimplifyAggref;
98+
7499
/*
75100
* The InlineInFrom request allows the support function to perform plan-time
76101
* simplification of a call to its target function that appears in FROM.

‎src/include/optimizer/optimizer.h‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -147,6 +147,9 @@ extern Expr *evaluate_expr(Expr *expr, Oid result_type, int32 result_typmod,
147147

148148
externboolvar_is_nonnullable(PlannerInfo*root,Var*var,booluse_rel_info);
149149

150+
externboolexpr_is_nonnullable(PlannerInfo*root,Expr*expr,
151+
booluse_rel_info);
152+
150153
externList*expand_function_arguments(List*args,boolinclude_out_arguments,
151154
Oidresult_type,
152155
HeapTuplefunc_tuple);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp