Would like to know what exactly happens in 'ALL AMPs SUM' step ? This operation is performed in while using group by clause in the query.
Refer to the 3rd step in the explain statement of the query...
3)We do an all-AMPs SUM step to aggregate from database.tablename by way of an all-rows scan with no residual conditions, and the grouping identifier in field 1025. Aggregate Intermediate Results are computed locally, then placed in Spool 3. The size of Spool 3 is estimated with low confidence to be 3,930 rows. The estimated time for this step is 0.01 seconds.
4)We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 3,930 rows. The estimated time for this step is 0.01 seconds.
The SUM step performs summarization (aggregation). It is used whenever a query contains an aggregate function (e.g. SUM, MAX, MIN, AVERAGE) or GROUP BY. The general method is to generate partial summaries locally on each AMP, and then redistribute them according to the grouping key. The partial results are then combined to produce a spool containing the final result.