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

Commit6cb8614

Browse files
committed
Allow aggregates to provide estimates of their transition state data size.
Formerly the planner had a hard-wired rule of thumb for guessing the amountof space consumed by an aggregate function's transition state data. Thisestimate is critical to deciding whether it's OK to use hash aggregation,and in many situations the built-in estimate isn't very good. This patchadds a column to pg_aggregate wherein a per-aggregate estimate can beprovided, overriding the planner's default, and infrastructure for settingthe column via CREATE AGGREGATE.It may be that additional smarts will be required in future, perhaps evena per-aggregate estimation function. But this is already a step forward.This is extracted from a larger patch to improve the performance of numericand int8 aggregates. I (tgl) thought it was worth reviewing and committingthis infrastructure separately. In this commit, all built-in aggregatesare given aggtransspace = 0, so no behavior should change.Hadi Moshayedi, reviewed by Pavel Stehule and Tomas Vondra
1 parent55c3d86 commit6cb8614

File tree

14 files changed

+245
-147
lines changed

14 files changed

+245
-147
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -372,6 +372,13 @@
372372
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
373373
<entry>Data type of the aggregate function's internal transition (state) data</entry>
374374
</row>
375+
<row>
376+
<entry><structfield>aggtransspace</structfield></entry>
377+
<entry><type>int4</type></entry>
378+
<entry></entry>
379+
<entry>Approximate average size (in bytes) of the transition state
380+
data, or zero to use a default estimate</entry>
381+
</row>
375382
<row>
376383
<entry><structfield>agginitval</structfield></entry>
377384
<entry><type>text</type></entry>

‎doc/src/sgml/ref/create_aggregate.sgml

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@ PostgreSQL documentation
2424
CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">arg_name</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) (
2525
SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
2626
STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
27+
[ , SSPACE = <replaceable class="PARAMETER">state_data_size</replaceable> ]
2728
[ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
2829
[ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
2930
[ , SORTOP = <replaceable class="PARAMETER">sort_operator</replaceable> ]
@@ -35,6 +36,7 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
3536
BASETYPE = <replaceable class="PARAMETER">base_type</replaceable>,
3637
SFUNC = <replaceable class="PARAMETER">sfunc</replaceable>,
3738
STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
39+
[ , SSPACE = <replaceable class="PARAMETER">state_data_size</replaceable> ]
3840
[ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
3941
[ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
4042
[ , SORTOP = <replaceable class="PARAMETER">sort_operator</replaceable> ]
@@ -264,6 +266,22 @@ SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
264266
</listitem>
265267
</varlistentry>
266268

269+
<varlistentry>
270+
<term><replaceable class="PARAMETER">state_data_size</replaceable></term>
271+
<listitem>
272+
<para>
273+
The approximate average size (in bytes) of the aggregate's state value.
274+
If this parameter is omitted or is zero, a default estimate is used
275+
based on the <replaceable>state_data_type</>.
276+
The planner uses this value to estimate the memory required for a
277+
grouped aggregate query. The planner will consider using hash
278+
aggregation for such a query only if the hash table is estimated to fit
279+
in <xref linkend="guc-work-mem">; therefore, large values of this
280+
parameter discourage use of hash aggregation.
281+
</para>
282+
</listitem>
283+
</varlistentry>
284+
267285
<varlistentry>
268286
<term><replaceable class="PARAMETER">ffunc</replaceable></term>
269287
<listitem>

‎src/backend/catalog/pg_aggregate.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ AggregateCreate(const char *aggName,
5555
List*aggfinalfnName,
5656
List*aggsortopName,
5757
OidaggTransType,
58+
int32aggTransSpace,
5859
constchar*agginitval)
5960
{
6061
Relationaggdesc;
@@ -273,6 +274,7 @@ AggregateCreate(const char *aggName,
273274
values[Anum_pg_aggregate_aggfinalfn-1]=ObjectIdGetDatum(finalfn);
274275
values[Anum_pg_aggregate_aggsortop-1]=ObjectIdGetDatum(sortop);
275276
values[Anum_pg_aggregate_aggtranstype-1]=ObjectIdGetDatum(aggTransType);
277+
values[Anum_pg_aggregate_aggtransspace-1]=Int32GetDatum(aggTransSpace);
276278
if (agginitval)
277279
values[Anum_pg_aggregate_agginitval-1]=CStringGetTextDatum(agginitval);
278280
else

‎src/backend/commands/aggregatecmds.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -60,6 +60,7 @@ DefineAggregate(List *name, List *args, bool oldstyle, List *parameters,
6060
List*sortoperatorName=NIL;
6161
TypeName*baseType=NULL;
6262
TypeName*transType=NULL;
63+
int32transSpace=0;
6364
char*initval=NULL;
6465
intnumArgs;
6566
oidvector*parameterTypes;
@@ -102,6 +103,8 @@ DefineAggregate(List *name, List *args, bool oldstyle, List *parameters,
102103
transType=defGetTypeName(defel);
103104
elseif (pg_strcasecmp(defel->defname,"stype1")==0)
104105
transType=defGetTypeName(defel);
106+
elseif (pg_strcasecmp(defel->defname,"sspace")==0)
107+
transSpace=defGetInt32(defel);
105108
elseif (pg_strcasecmp(defel->defname,"initcond")==0)
106109
initval=defGetString(defel);
107110
elseif (pg_strcasecmp(defel->defname,"initcond1")==0)
@@ -248,5 +251,6 @@ DefineAggregate(List *name, List *args, bool oldstyle, List *parameters,
248251
finalfuncName,/* final function name */
249252
sortoperatorName,/* sort operator name */
250253
transTypeId,/* transition data type */
254+
transSpace,/* transition space */
251255
initval);/* initial condition */
252256
}

‎src/backend/commands/define.c

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -164,6 +164,30 @@ defGetBoolean(DefElem *def)
164164
return false;/* keep compiler quiet */
165165
}
166166

167+
/*
168+
* Extract an int32 value from a DefElem.
169+
*/
170+
int32
171+
defGetInt32(DefElem*def)
172+
{
173+
if (def->arg==NULL)
174+
ereport(ERROR,
175+
(errcode(ERRCODE_SYNTAX_ERROR),
176+
errmsg("%s requires an integer value",
177+
def->defname)));
178+
switch (nodeTag(def->arg))
179+
{
180+
caseT_Integer:
181+
return (int32)intVal(def->arg);
182+
default:
183+
ereport(ERROR,
184+
(errcode(ERRCODE_SYNTAX_ERROR),
185+
errmsg("%s requires an integer value",
186+
def->defname)));
187+
}
188+
return0;/* keep compiler quiet */
189+
}
190+
167191
/*
168192
* Extract an int64 value from a DefElem.
169193
*/

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

Lines changed: 27 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -461,6 +461,7 @@ count_agg_clauses_walker(Node *node, count_agg_clauses_context *context)
461461
Oidaggtransfn;
462462
Oidaggfinalfn;
463463
Oidaggtranstype;
464+
int32aggtransspace;
464465
QualCostargcosts;
465466
Oid*inputTypes;
466467
intnumArguments;
@@ -478,6 +479,7 @@ count_agg_clauses_walker(Node *node, count_agg_clauses_context *context)
478479
aggtransfn=aggform->aggtransfn;
479480
aggfinalfn=aggform->aggfinalfn;
480481
aggtranstype=aggform->aggtranstype;
482+
aggtransspace=aggform->aggtransspace;
481483
ReleaseSysCache(aggTuple);
482484

483485
/* count it */
@@ -541,35 +543,47 @@ count_agg_clauses_walker(Node *node, count_agg_clauses_context *context)
541543
*/
542544
if (!get_typbyval(aggtranstype))
543545
{
544-
int32aggtranstypmod;
545546
int32avgwidth;
546547

547-
/*
548-
* If transition state is of same type as first input, assume it's
549-
* the same typmod (same width) as well. This works for cases
550-
* like MAX/MIN and is probably somewhat reasonable otherwise.
551-
*/
552-
if (numArguments>0&&aggtranstype==inputTypes[0])
553-
aggtranstypmod=exprTypmod((Node*)linitial(aggref->args));
548+
/* Use average width if aggregate definition gave one */
549+
if (aggtransspace>0)
550+
avgwidth=aggtransspace;
554551
else
555-
aggtranstypmod=-1;
552+
{
553+
/*
554+
* If transition state is of same type as first input, assume
555+
* it's the same typmod (same width) as well. This works for
556+
* cases like MAX/MIN and is probably somewhat reasonable
557+
* otherwise.
558+
*/
559+
int32aggtranstypmod;
556560

557-
avgwidth=get_typavgwidth(aggtranstype,aggtranstypmod);
558-
avgwidth=MAXALIGN(avgwidth);
561+
if (numArguments>0&&aggtranstype==inputTypes[0])
562+
aggtranstypmod=exprTypmod((Node*)linitial(aggref->args));
563+
else
564+
aggtranstypmod=-1;
565+
566+
avgwidth=get_typavgwidth(aggtranstype,aggtranstypmod);
567+
}
559568

569+
avgwidth=MAXALIGN(avgwidth);
560570
costs->transitionSpace+=avgwidth+2*sizeof(void*);
561571
}
562572
elseif (aggtranstype==INTERNALOID)
563573
{
564574
/*
565575
* INTERNAL transition type is a special case: although INTERNAL
566576
* is pass-by-value, it's almost certainly being used as a pointer
567-
* to some large data structure. We assume usage of
577+
* to some large data structure. The aggregate definition can
578+
* provide an estimate of the size. If it doesn't, then we assume
568579
* ALLOCSET_DEFAULT_INITSIZE, which is a good guess if the data is
569580
* being kept in a private memory context, as is done by
570581
* array_agg() for instance.
571582
*/
572-
costs->transitionSpace+=ALLOCSET_DEFAULT_INITSIZE;
583+
if (aggtransspace>0)
584+
costs->transitionSpace+=aggtransspace;
585+
else
586+
costs->transitionSpace+=ALLOCSET_DEFAULT_INITSIZE;
573587
}
574588

575589
/*

‎src/bin/pg_dump/pg_dump.c

Lines changed: 30 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -11521,12 +11521,14 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1152111521
inti_aggfinalfn;
1152211522
inti_aggsortop;
1152311523
inti_aggtranstype;
11524+
inti_aggtransspace;
1152411525
inti_agginitval;
1152511526
inti_convertok;
1152611527
constchar*aggtransfn;
1152711528
constchar*aggfinalfn;
1152811529
constchar*aggsortop;
1152911530
constchar*aggtranstype;
11531+
constchar*aggtransspace;
1153011532
constchar*agginitval;
1153111533
boolconvertok;
1153211534

@@ -11544,12 +11546,26 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1154411546
selectSourceSchema(fout,agginfo->aggfn.dobj.namespace->dobj.name);
1154511547

1154611548
/* Get aggregate-specific details */
11547-
if (fout->remoteVersion >=80400)
11549+
if (fout->remoteVersion >=90400)
11550+
{
11551+
appendPQExpBuffer(query,"SELECT aggtransfn, "
11552+
"aggfinalfn, aggtranstype::pg_catalog.regtype, "
11553+
"aggsortop::pg_catalog.regoperator, "
11554+
"aggtransspace, agginitval, "
11555+
"'t'::boolean AS convertok, "
11556+
"pg_catalog.pg_get_function_arguments(p.oid) AS funcargs, "
11557+
"pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs "
11558+
"FROM pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
11559+
"WHERE a.aggfnoid = p.oid "
11560+
"AND p.oid = '%u'::pg_catalog.oid",
11561+
agginfo->aggfn.dobj.catId.oid);
11562+
}
11563+
elseif (fout->remoteVersion >=80400)
1154811564
{
1154911565
appendPQExpBuffer(query,"SELECT aggtransfn, "
1155011566
"aggfinalfn, aggtranstype::pg_catalog.regtype, "
1155111567
"aggsortop::pg_catalog.regoperator, "
11552-
"agginitval, "
11568+
"0 AS aggtransspace,agginitval, "
1155311569
"'t'::boolean AS convertok, "
1155411570
"pg_catalog.pg_get_function_arguments(p.oid) AS funcargs, "
1155511571
"pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs "
@@ -11563,7 +11579,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1156311579
appendPQExpBuffer(query,"SELECT aggtransfn, "
1156411580
"aggfinalfn, aggtranstype::pg_catalog.regtype, "
1156511581
"aggsortop::pg_catalog.regoperator, "
11566-
"agginitval, "
11582+
"0 AS aggtransspace,agginitval, "
1156711583
"'t'::boolean AS convertok "
1156811584
"FROM pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
1156911585
"WHERE a.aggfnoid = p.oid "
@@ -11575,7 +11591,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1157511591
appendPQExpBuffer(query,"SELECT aggtransfn, "
1157611592
"aggfinalfn, aggtranstype::pg_catalog.regtype, "
1157711593
"0 AS aggsortop, "
11578-
"agginitval, "
11594+
"0 AS aggtransspace,agginitval, "
1157911595
"'t'::boolean AS convertok "
1158011596
"FROM pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
1158111597
"WHERE a.aggfnoid = p.oid "
@@ -11587,7 +11603,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1158711603
appendPQExpBuffer(query,"SELECT aggtransfn, aggfinalfn, "
1158811604
"format_type(aggtranstype, NULL) AS aggtranstype, "
1158911605
"0 AS aggsortop, "
11590-
"agginitval, "
11606+
"0 AS aggtransspace,agginitval, "
1159111607
"'t'::boolean AS convertok "
1159211608
"FROM pg_aggregate "
1159311609
"WHERE oid = '%u'::oid",
@@ -11599,7 +11615,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1159911615
"aggfinalfn, "
1160011616
"(SELECT typname FROM pg_type WHERE oid = aggtranstype1) AS aggtranstype, "
1160111617
"0 AS aggsortop, "
11602-
"agginitval1 AS agginitval, "
11618+
"0 AS aggtransspace,agginitval1 AS agginitval, "
1160311619
"(aggtransfn2 = 0 and aggtranstype2 = 0 and agginitval2 is null) AS convertok "
1160411620
"FROM pg_aggregate "
1160511621
"WHERE oid = '%u'::oid",
@@ -11612,13 +11628,15 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1161211628
i_aggfinalfn=PQfnumber(res,"aggfinalfn");
1161311629
i_aggsortop=PQfnumber(res,"aggsortop");
1161411630
i_aggtranstype=PQfnumber(res,"aggtranstype");
11631+
i_aggtransspace=PQfnumber(res,"aggtransspace");
1161511632
i_agginitval=PQfnumber(res,"agginitval");
1161611633
i_convertok=PQfnumber(res,"convertok");
1161711634

1161811635
aggtransfn=PQgetvalue(res,0,i_aggtransfn);
1161911636
aggfinalfn=PQgetvalue(res,0,i_aggfinalfn);
1162011637
aggsortop=PQgetvalue(res,0,i_aggsortop);
1162111638
aggtranstype=PQgetvalue(res,0,i_aggtranstype);
11639+
aggtransspace=PQgetvalue(res,0,i_aggtransspace);
1162211640
agginitval=PQgetvalue(res,0,i_agginitval);
1162311641
convertok= (PQgetvalue(res,0,i_convertok)[0]=='t');
1162411642

@@ -11672,6 +11690,12 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
1167211690
fmtId(aggtranstype));
1167311691
}
1167411692

11693+
if (strcmp(aggtransspace,"0")!=0)
11694+
{
11695+
appendPQExpBuffer(details,",\n SSPACE = %s",
11696+
aggtransspace);
11697+
}
11698+
1167511699
if (!PQgetisnull(res,0,i_agginitval))
1167611700
{
1167711701
appendPQExpBuffer(details,",\n INITCOND = ");

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201311081
56+
#defineCATALOG_VERSION_NO201311161
5757

5858
#endif

0 commit comments

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