{"kind": "plan", "major": "18", "item": {"slug": "aggregate", "name": "Aggregate", "name_zh": "Agg", "category": "Aggregation", "summary": "Computes aggregate results using the selected grouping strategy and aggregation stage.", "aliases": ["Agg", "Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate", "T_Agg"], "content_hash": "3730c8cba1f67b3d1d5027d1a7aeca45790a785dcb981566c0515edfad69e31f", "versions": {"10": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "c9793abfa077342a3f5591e608fa5ebbca4ba4e87f22271c6f8fc3354f5ff5c4", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}], "mechanism": "aggregate", "description": "Aggregation maintains transition states and may sort inputs for ordered or DISTINCT aggregates. This extraction does not establish a hashed-aggregation spill path for this source build.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=10", "label": "EXPLAIN"}, {"url": "/docs/10/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/10/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=10", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=10", "label": "work_mem"}], "release": {"ref": "PostgreSQL 10.23 source archive", "label": "10.23", "major": "10", "channel": "historical", "revision": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9", "source_url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 1022, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1022", "sha256": "a785298532047cfeda969e78c3597a343dc1c56d61ba85830b0f16a02a14b5a1", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "line": 323, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:323", "sha256": "cea76648bb38ae55f18f989768bee1a4ee025691ceea0f86bccb29dcdc166acc", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "c9793abfa077342a3f5591e608fa5ebbca4ba4e87f22271c6f8fc3354f5ff5c4", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "https://ftp.postgresql.org/pub/source/v10.23/postgresql-10.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d562c321108844798cd234303fffb618f13d4ee3f3a5ac79bfd963b077e47c22", "archive_sha256": "94a4b2528372458e5662c18d406629266667c437198160a18cdfd2c4a4d6eee9"}, {"url": "/docs/10/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 10.23 \u00b7 parallel-plans", "sha256": "cd37ed0ef7e2cf177707f50d9a7258574b4cefdcc087e6ee99a3bf377d960fcb"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["Aggregation maintains transition states and may sort inputs for ordered or DISTINCT aggregates. This extraction does not establish a hashed-aggregation spill path for this source build."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate", "parallel_callbacks": []}, "comparison_hash": "6b783c0f46b55bb43e89386e12a6f493942ee5fcd4f3ec97bd38db3425405f32", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": []}, "11": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "397642c478282cd0a1555a151a4ccaf1f784330c84de22d53d275760347fe93a", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}], "mechanism": "aggregate", "description": "Aggregation maintains transition states and may sort inputs for ordered or DISTINCT aggregates. This extraction does not establish a hashed-aggregation spill path for this source build.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=11", "label": "EXPLAIN"}, {"url": "/docs/11/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/11/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=11", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=11", "label": "work_mem"}], "release": {"ref": "PostgreSQL 11.22 source archive", "label": "11.22", "major": "11", "channel": "historical", "revision": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0", "source_url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 1147, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1147", "sha256": "9df8400c1a4377179572ceb916d6020fca4e2760f74bf416d77ed97476523bbd", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "line": 323, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:323", "sha256": "95ef4d4a5df4c29f14af9763fae2c530449bdacdf3853d9ff297adf1fed6153b", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "397642c478282cd0a1555a151a4ccaf1f784330c84de22d53d275760347fe93a", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "https://ftp.postgresql.org/pub/source/v11.22/postgresql-11.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "5e0511194183800e8d6eb293fd4b40639c7d3118e2d199c8e7865ba4fa4cf67f", "archive_sha256": "2cb7c97d7a0d7278851bbc9c61f467b69c094c72b81740b751108e7892ebe1f0"}, {"url": "/docs/11/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 11.22 \u00b7 parallel-plans", "sha256": "353df5869034b7665159a74037d9cf3e1800d15efcea3390daaa99aae89fa288"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["Aggregation maintains transition states and may sort inputs for ordered or DISTINCT aggregates. This extraction does not establish a hashed-aggregation spill path for this source build."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate", "parallel_callbacks": []}, "comparison_hash": "6b783c0f46b55bb43e89386e12a6f493942ee5fcd4f3ec97bd38db3425405f32", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": []}, "12": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "2bb48041150f8936e1caa3c43f2ce08a610bc2a6a91d91963bb67cc3f32f2d8d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}], "mechanism": "aggregate", "description": "Aggregation maintains transition states and may sort inputs for ordered or DISTINCT aggregates. This extraction does not establish a hashed-aggregation spill path for this source build.", "source_notes": []}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=12", "label": "EXPLAIN"}, {"url": "/docs/12/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/12/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=12", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=12", "label": "work_mem"}], "release": {"ref": "PostgreSQL 12.22 source archive", "label": "12.22", "major": "12", "channel": "historical", "revision": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b", "source_url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 1218, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1218", "sha256": "d02ea84fdaa201de5d9360645a9f24bfbd2c31f7d45a639e09560ac0e6b6471d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "line": 323, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:323", "sha256": "311b17379fe54e3f342fe5ad41c43afbdfa1b844978db2bb2eb22b82520d3256", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "2bb48041150f8936e1caa3c43f2ce08a610bc2a6a91d91963bb67cc3f32f2d8d", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "https://ftp.postgresql.org/pub/source/v12.22/postgresql-12.22.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "b0c4a0aeb48660ce06e5e700d5529ca9066fd16682bd15783d6e71b5420b07b0", "archive_sha256": "8df3c0474782589d3c6f374b5133b1bd14d168086edbc13c6e72e67dd4527a3b"}, {"url": "/docs/12/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 12.22 \u00b7 parallel-plans", "sha256": "fa33380814ee65998f524524f8681d70cf3b1198d54b39847b00462b01517ffb"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["Aggregation maintains transition states and may sort inputs for ordered or DISTINCT aggregates. This extraction does not establish a hashed-aggregation spill path for this source build."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: none extracted from this node implementation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate", "parallel_callbacks": []}, "comparison_hash": "6b783c0f46b55bb43e89386e12a6f493942ee5fcd4f3ec97bd38db3425405f32", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": []}, "13": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "6aadfd2221119ee6a7f00d39e51652acd7568d208185eaa90b6e42bfcc74e037", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=13", "label": "EXPLAIN"}, {"url": "/docs/13/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/13/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=13", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=13", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=13", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 13.23 source archive", "label": "13.23", "major": "13", "channel": "historical", "revision": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6", "source_url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 1279, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1279", "sha256": "541713e0e7f1c9cc352c2b6028964d440c19d2678a4463000094c24a88c1e730", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "line": 328, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:328", "sha256": "d085ee99acfa00587e6ade3a1d9f8108a0566beedbbee3f54a50c9fc0cc2e875", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "6aadfd2221119ee6a7f00d39e51652acd7568d208185eaa90b6e42bfcc74e037", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "https://ftp.postgresql.org/pub/source/v13.23/postgresql-13.23.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "dcb296833777b02008c4b6bae8e8f7c6423b7ffba21f36702597c9d596d039ab", "archive_sha256": "6ec3c82726af92b7dec873fa1cdf881eca92a4219787dfad05acb6b10e041fd6"}, {"url": "/docs/13/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 13.23 \u00b7 parallel-plans", "sha256": "025ad8564a8461b676c9153f9e084429cca0ef86c968090689b082120060f3d0"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "14": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "0af1c14c105760710921c58efb8abef621913102d99db65cbddb61fd9952d3fe", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=14", "label": "EXPLAIN"}, {"url": "/docs/14/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/14/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=14", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=14", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=14", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 14.24 source archive", "label": "14.24", "major": "14", "channel": "stable", "revision": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897", "source_url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 1321, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1321", "sha256": "e091be4e2a083b8dea39ccd09beedede22c1716ef974da66c214a44f48be8c41", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "72da1c5ad457f1d92a39ab73531701794df858419e3b89d6e6cb7079634e68fa", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "0af1c14c105760710921c58efb8abef621913102d99db65cbddb61fd9952d3fe", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "https://ftp.postgresql.org/pub/source/v14.24/postgresql-14.24.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "302f51a16b570dba7ec4e7bc045f7df5800d21630280354d1a24025f3baec75d", "archive_sha256": "a7fa7ed3d558172355f51406097a7bd4f6b473be80f311ef7cda96bf383d8897"}, {"url": "/docs/14/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 14.24 \u00b7 parallel-plans", "sha256": "71fc3525781b74150364925e123cd8598fdebe0e05326706f2bc2e1ffa0b88a4"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "15": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "7d1f87f3c9b9c101fe6dfdfe44cfaf8f943af38e97d3cb979af9660e53b9207c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=15", "label": "EXPLAIN"}, {"url": "/docs/15/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/15/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=15", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=15", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=15", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 15.19 source archive", "label": "15.19", "major": "15", "channel": "stable", "revision": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89", "source_url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 1324, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1324", "sha256": "bb3b442d0f1b098aa8707335250102f027a596cd94117308bd16d1d36b258f5c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "19836c50a272741a4eac653541e655437c2e00710a541e5348d6a277d0669d7c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "7d1f87f3c9b9c101fe6dfdfe44cfaf8f943af38e97d3cb979af9660e53b9207c", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "https://ftp.postgresql.org/pub/source/v15.19/postgresql-15.19.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "fb4a4c8165495299131173680bc02a950d88e1ff610231fd97997bc0c9afc1d7", "archive_sha256": "e1a64a87a46b825b88c082e4518161a47aab53c45694964f8ba1df28f7859f89"}, {"url": "/docs/15/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 15.19 \u00b7 parallel-plans", "sha256": "ec9345488a15cdc05d3e0b0849763e2bf0b864ad17de786c5f023db5dab1ea96"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "16": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "4061681527a5ba814d25eb342a903d9739842e1581df80c57e8f30338e0e6ca8", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=16", "label": "EXPLAIN"}, {"url": "/docs/16/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/16/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=16", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=16", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=16", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 16.15 source archive", "label": "16.15", "major": "16", "channel": "stable", "revision": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed", "source_url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 1357, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1357", "sha256": "8e017f0116dbea471339b40c37a667cc9f95039e7e0329c783e5e8ce194de7e1", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "e48c08e555f8cb4e4bb43df516c4b8906ce9bc374b2a745d98a1fc8c22cc5099", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "4061681527a5ba814d25eb342a903d9739842e1581df80c57e8f30338e0e6ca8", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "https://ftp.postgresql.org/pub/source/v16.15/postgresql-16.15.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "97db47353db76326b874589a5ad0a04501cc74cd72e237e7bd956e7472c41f1f", "archive_sha256": "c1575341fa7bd40f5274ea465b34390f4dc64cdd0770af327005caaeb9f6b7ed"}, {"url": "/docs/16/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 16.15 \u00b7 parallel-plans", "sha256": "53d83f63f97381fe1e4c0cfe5ceb81d30c1542b03e35a144684afd8fb3b9686c"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "17": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "995eff2986d133347aba073bc2ad594cdedab415814cae38d3a73c6e332f4fb3", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=17", "label": "EXPLAIN"}, {"url": "/docs/17/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/17/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=17", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=17", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=17", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 17.11 source archive", "label": "17.11", "major": "17", "channel": "stable", "revision": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979", "source_url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 1546, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1546", "sha256": "741251b1a3b6d269a52a673d42eb63b02e13a5872db7b359b137086ab21b63c8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "a77576e158b94cb01fa8c5174ba133004eabdd727660323f8afc66c8d2e757b8", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "995eff2986d133347aba073bc2ad594cdedab415814cae38d3a73c6e332f4fb3", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "https://ftp.postgresql.org/pub/source/v17.11/postgresql-17.11.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "d390dd69e2d3f5085beb42b33e46ff0676a2959b916a12b82118a7e545f8e562", "archive_sha256": "dd27f2b3c59e73ed14aa3324901242bf69a032a6347805f274e6260322d42979"}, {"url": "/docs/17/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 17.11 \u00b7 parallel-plans", "sha256": "784fe3d3b7ae7d1a34e466aa551bde484a40a3b60dc5d2ada6e1675f40713bbc"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "18": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "8719569a73f054e9026c0a00c814f60b6c7879753c58e4e6dcc38fee333c45c5", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=18", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=18", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=18", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1531, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1531", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "8719569a73f054e9026c0a00c814f60b6c7879753c58e4e6dcc38fee333c45c5", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "/docs/18/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 18.6 \u00b7 parallel-plans", "sha256": "62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "19": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "a215920b1c46c0bb8c8c1aef6ae9da332f7b3e80cee45f0edff38316809e27ef", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=19", "label": "EXPLAIN"}, {"url": "/docs/19/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/19/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=19", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=19", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=19", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 19beta4 source archive", "label": "19beta4", "major": "19", "channel": "preview", "revision": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86", "source_url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 1543, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1543", "sha256": "8b115b1c194a4b54ae630209a741e293b1df49a9052f10b2de9ca092a48998e3", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "a215920b1c46c0bb8c8c1aef6ae9da332f7b3e80cee45f0edff38316809e27ef", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "https://ftp.postgresql.org/pub/source/v19beta4/postgresql-19beta4.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "1c65d5d6b6c81c71531685843647869bcae630779d815a5036b06e070c6c06c7", "archive_sha256": "83157ee9c599d03b2f7a3d73ef3a56ec24e0e79cc2b3501a64d1364f56398c86"}, {"url": "/docs/19/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 19beta4 \u00b7 parallel-plans", "sha256": "f99fee3456a48a5ce2c399d6b35ab93183c2c9012e8ed60c53c0c253f79c3756"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "20": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "26f95c4ca8af157543a9d85c23d21eb595f091a34e92961491746ff3e996c2e1", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=20", "label": "EXPLAIN"}, {"url": "/docs/devel/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/devel/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=20", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=20", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=20", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 20devel source archive", "label": "20devel", "major": "20", "channel": "devel", "revision": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41", "source_url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "source_snapshot_utc": "26-Sep-2026 20:22"}, "sources": [{"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 1543, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1543", "sha256": "13402758013520451539427b5993db06d463ca11c4e2d4cc5444e82367688077", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "5e39b2037bed672da55104229ecc32da5abde44c26bcad01479edcfa044d09ed", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "26f95c4ca8af157543a9d85c23d21eb595f091a34e92961491746ff3e996c2e1", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "7a94ed1652f0d74d50c39971d1cd3e8051dbc0d6058f31b6de71a433ca343521", "archive_sha256": "4d3346909b201ac1648232cf290462a7070c119326f56196f1f0253ed80fae41"}, {"url": "/docs/devel/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 20devel \u00b7 parallel-plans", "sha256": "6451d4254d26d789b8697ba7207288ac26e75e56f9711c8a5a3f5480171a4724"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}}}, "snapshot": {"facts": [{"label": "Core node tag", "value": "T_Agg"}, {"label": "Structured EXPLAIN Node Type", "value": "Aggregate"}, {"label": "Inputs", "value": "One child plan"}, {"label": "Output", "value": "Aggregate result tuples"}, {"label": "Executor initializer", "value": "ExecInitAgg"}, {"label": "Memory mechanism", "value": "aggregate-spill"}, {"label": "EXPLAIN strategies", "value": "Plain, Sorted, Hashed, Mixed"}, {"label": "Aggregation stages", "value": "Partial, Finalize, Simple"}], "memory": {"evidence": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "8719569a73f054e9026c0a00c814f60b6c7879753c58e4e6dcc38fee333c45c5", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}], "mechanism": "aggregate-spill", "description": "This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "source_notes": ["When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, "tables": [{"key": "explain-labels", "rows": [{"label": "Aggregate", "identity": "Aggregate"}, {"label": "GroupAggregate", "identity": "Aggregate"}, {"label": "HashAggregate", "identity": "Aggregate"}, {"label": "MixedAggregate", "identity": "Aggregate"}], "title": "EXPLAIN labels in this source build", "columns": [{"key": "label", "label": "Text-format label"}, {"key": "identity", "label": "Structured node identity"}]}], "related": [{"url": "/wiki/sql/explain/?v=18", "label": "EXPLAIN"}, {"url": "/docs/18/using-explain.html", "label": "Using EXPLAIN"}, {"url": "/docs/18/parallel-plans.html", "label": "Parallel plans"}, {"url": "/wiki/guc/enable_hashagg/?v=18", "label": "enable_hashagg"}, {"url": "/wiki/guc/work_mem/?v=18", "label": "work_mem"}, {"url": "/wiki/guc/hash_mem_multiplier/?v=18", "label": "hash_mem_multiplier"}], "release": {"ref": "PostgreSQL 18.6 source archive", "label": "18.6", "major": "18", "channel": "stable", "revision": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f", "source_url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "source_snapshot_utc": ""}, "sources": [{"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 1531, "path": "src/backend/commands/explain.c", "label": "src/backend/commands/explain.c:1531", "sha256": "34c86d6070224a0e981efef51f79101d6d505e5874f1684ace183034bab14bb4", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "line": 340, "path": "src/backend/executor/execProcnode.c", "label": "src/backend/executor/execProcnode.c:340", "sha256": "f8a06a3f539077249b20664b2812433db6d7bd12b2c0ca633525db43d06f112a", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/backend/executor/nodeAgg.c", "label": "src/backend/executor/nodeAgg.c", "sha256": "8719569a73f054e9026c0a00c814f60b6c7879753c58e4e6dcc38fee333c45c5", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2", "path": "src/include/nodes/plannodes.h", "label": "src/include/nodes/plannodes.h", "sha256": "52422b327a8049fbbb20d8b96008a0fc0a6fafa60f7eff3c695d5b2e83830120", "archive_sha256": "555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}, {"url": "/docs/18/parallel-plans.html#PARALLEL-AGGREGATION", "path": "parallel-plans.html", "label": "PostgreSQL 18.6 \u00b7 parallel-plans", "sha256": "62207d207bead82b01b59dc119c4f95856f08655cc11d4699a05a40867ed2070"}], "node_tag": "T_Agg", "sections": [{"title": "EXPLAIN names and attributes", "paragraphs": ["Structured formats use the Node Type above. Text-format spellings can also include operation, strategy, join type, scan direction or aggregation-stage attributes.", "Text names recorded by this source: Aggregate, GroupAggregate, HashAggregate, MixedAggregate.", "Parallel-aware and parallel-safe are different plan properties. A node running inside a parallel worker is not necessarily a parallel-aware node."]}, {"title": "Memory and temporary storage", "paragraphs": ["This implementation contains a disk-spill path for hashed aggregation. Aggregate input sorting and transition-state allocations have their own behavior; one spill policy does not describe every aggregation strategy.", "When performing hash aggregation, if the hash table memory exceeds the limit (see hash_agg_check_limits()), we enter \"spill mode\". In spill mode, we advance the transition states only for groups already in the hash table. For tuples that would need to create a new hash table entries (and initialize new transition states), we instead spill them to disk to be processed later. The tuples are spilled in a partitioned manner, so that subsequent batches are smaller and less likely to exceed hash_mem (if a batch does exceed hash_mem, it must be spilled recursively).", "Spilled data is written to logical tapes. These provide better control over memory usage, disk space, and the number of files than if we were to use a BufFile for each spill. We don't know the number of tapes needed at the start of the algorithm (because it can recurse), so a tape set is allocated at the beginning, and individual tapes are created as needed. As a particular tape is read, logtape.c recycles its disk space. When a tape is read to completion, it is destroyed entirely.", "Control how many partitions are created when spilling HashAgg to disk.", "We also specify a min and max number of partitions per spill. Too few might mean a lot of wasted I/O from repeated spilling of the same tuples. Too many will result in lots of memory wasted buffering the spill files (which could instead be spent on a larger hash table)."]}, {"title": "Parallel execution and instrumentation", "paragraphs": ["The source callbacks below can coordinate execution or collect worker instrumentation. Their presence is not a blanket claim that this node supports a shared parallel scan or shared state.", "Callbacks in this build: ExecAggEstimate, ExecAggInitializeDSM, ExecAggInitializeWorker, ExecAggRetrieveInstrumentation."]}, {"title": "Same-version manual discussion", "paragraphs": ["PostgreSQL supports parallel aggregation by aggregating in two stages. First, each process participating in the parallel portion of the query performs an aggregation step, producing a partial result for each group of which that process is aware. This is reflected in the plan as a Partial Aggregate node. Second, the partial results are transferred to the leader via Gather or Gather Merge . Finally, the leader re-aggregates the results across all workers in order to produce the final result. This is reflected in the plan as a Finalize Aggregate node.", "Because the Finalize Aggregate node runs on the leader process, queries that produce a relatively large number of groups in comparison to the number of input rows will appear less favorable to the query planner. For example, in the worst-case scenario the number of groups seen by the Finalize Aggregate node could be as many as the number of input rows that were seen by all worker processes in the Partial Aggregate stage. For such cases, there is clearly going to be no performance benefit to using parallel aggregation. The query planner takes this into account during the planning process and is unlikely to choose parallel aggregate in this scenario.", "Parallel aggregation is not supported in all situations. Each aggregate must be safe for parallelism and must have a combine function. If the aggregate has a transition state of type internal , it must have serialization and deserialization functions. See CREATE AGGREGATE for more details. Parallel aggregation is not supported if any aggregate function call contains DISTINCT or ORDER BY clause and is also not supported for ordered set aggregates or when the query involves GROUPING SETS . It can only be used when all joins involved in the query are also part of the parallel portion of the plan."]}, {"title": "Executor implementation notes", "paragraphs": ["ExecAgg normally evaluates each aggregate in the following steps:", "transvalue = initcond foreach input_tuple do transvalue = transfunc(transvalue, input_value(s)) result = finalfunc(transvalue, direct_argument(s))", "If a finalfunc is not supplied then the result is just the ending value of transvalue.", "Other behaviors can be selected by the \"aggsplit\" mode, which exists to support partial aggregation. It is possible to: * Skip running the finalfunc, so that the output is always the final transvalue state. * Substitute the combinefunc for the transfunc, so that transvalue states (propagated up from a child partial-aggregation step) are merged rather than processing raw input rows. (The statements below about the transfunc apply equally to the combinefunc, when it's selected.) * Apply the serializefunc to the output values (this only makes sense when skipping the finalfunc, since the serializefunc works on the transvalue data type). * Apply the deserializefunc to the input values (this only makes sense when using the combinefunc, for similar reasons). It is the planner's responsibility to connect up Agg nodes using these alternate behaviors in a way that makes sense, with partial aggregation results being fed to nodes that expect them.", "If a normal aggregate call specifies DISTINCT or ORDER BY, we sort the input tuples and eliminate duplicates (if required) before performing the above-depicted process. (However, we don't do that for ordered-set aggregates; their \"ORDER BY\" inputs are ordinary aggregate arguments so far as this module is concerned.) Note that partial aggregation is not supported in these cases, since we couldn't ensure global ordering or distinctness of the inputs."]}, {"code": "case T_Agg:\n\t\t\t{\n\t\t\t\tAgg\t\t   *agg = (Agg *) plan;\n\n\t\t\t\tsname = \"Aggregate\";\n\t\t\t\tswitch (agg->aggstrategy)\n\t\t\t\t{\n\t\t\t\t\tcase AGG_PLAIN:\n\t\t\t\t\t\tpname = \"Aggregate\";\n\t\t\t\t\t\tstrategy = \"Plain\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_SORTED:\n\t\t\t\t\t\tpname = \"GroupAggregate\";\n\t\t\t\t\t\tstrategy = \"Sorted\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_HASHED:\n\t\t\t\t\t\tpname = \"HashAggregate\";\n\t\t\t\t\t\tstrategy = \"Hashed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tcase AGG_MIXED:\n\t\t\t\t\t\tpname = \"MixedAggregate\";\n\t\t\t\t\t\tstrategy = \"Mixed\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t\tdefault:\n\t\t\t\t\t\tpname = \"Aggregate ???\";\n\t\t\t\t\t\tstrategy = \"???\";\n\t\t\t\t\t\tbreak;\n\t\t\t\t}\n\n\t\t\t\tif (DO_AGGSPLIT_SKIPFINAL(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Partial\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse if (DO_AGGSPLIT_COMBINE(agg->aggsplit))\n\t\t\t\t{\n\t\t\t\t\tpartialmode = \"Finalize\";\n\t\t\t\t\tpname = psprintf(\"%s %s\", partialmode, pname);\n\t\t\t\t}\n\t\t\t\telse\n\t\t\t\t\tpartialmode = \"Simple\";\n\t\t\t}\n\t\t\tbreak;", "title": "EXPLAIN identity in core source"}], "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "description": ["Computes aggregate results using the selected grouping strategy and aggregation stage."], "evidence_kind": "source and documentation", "explain_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "partial_modes": ["Partial", "Finalize", "Simple"], "comparison_data": {"node_tag": "T_Agg", "strategies": ["Plain", "Sorted", "Hashed", "Mixed"], "text_names": ["Aggregate", "GroupAggregate", "HashAggregate", "MixedAggregate"], "initializer": "ExecInitAgg", "partial_modes": ["Partial", "Finalize", "Simple"], "memory_mechanism": "aggregate-spill", "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison_hash": "1144b010ce8f8de60989a1cd00684cccaefed0d3e7341c71e2fbf2edd3e504eb", "explain_prefixes": ["Parallel", "Async"], "runtime_verified": false, "source_inventory": {"explain": "src/backend/commands/explain.c", "executor": "src/backend/executor/execProcnode.c", "implementation": "src/backend/executor/nodeAgg.c"}, "parallel_callbacks": ["ExecAggEstimate", "ExecAggInitializeDSM", "ExecAggInitializeWorker", "ExecAggRetrieveInstrumentation"]}, "comparison": {"left": "17", "right": "18", "status": "unchanged", "diff": ""}}