HashAgg and StreamAgg costs are too close for small results

[terry1purcell]

Enhancement

Customer issue reported where HashAgg was chosen for a query processing a small result - which resulted in excessive memory usage under high concurrency. StreamAgg resulted in lower memory usage and better overall performance.

`StreamAGG:
+------------------------------------------+---------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+------------------------------------------+---------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_18 | 2.00 | 9586.65 | root | | group by:Column#66, funcs:firstrow(Column#66)->Column#66 |
| └─Union_19 | 2.00 | 7988.13 | root | | |
| ├─StreamAgg_25 | 1.00 | 37914.59 | root | | group by:cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number, funcs:firstrow(cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number)->cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number |
| │ └─Projection_102 | 7.11 | 37556.04 | root | | cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number, cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.debit_interest_rate, cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.arrears_interest_rate |
| │ └─IndexLookUp_101 | 7.11 | 37553.91 | root | | |
| │ ├─IndexRangeScan_98(Build) | 19.09 | 4289.97 | cop[tikv] | table:i, index:tbl_cardcore_interest_transaction_group_bearing_balance_ix8(sub_account_number, arrears_balance_update_status) | range:["1901040103205199","1901040103207489"], keep order:true |
| │ └─Selection_100(Probe) | 7.11 | 9115.55 | cop[tikv] | | or(gt(cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.debit_interest_rate, 0), gt(cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.arrears_interest_rate, 0)) |
| │ └─TableRowIDScan_99 | 19.09 | 8162.83 | cop[tikv] | table:i | keep order:false |
| └─StreamAgg_113 | 1.00 | 2025.87 | root | | group by:cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number, funcs:firstrow(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number)->cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number |
| └─Projection_141 | 0.00 | 1975.47 | root | | cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.debit_interest_rate, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.new_postings_to_int_balance, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.arrears_interest_rate, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.status_code |
| └─IndexLookUp_140 | 0.00 | 1974.98 | root | | |
| ├─Selection_138(Build) | 0.00 | 279.49 | cop[tikv] | | eq(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.status_code, "ACTIVE") |
| │ └─IndexRangeScan_136 | 0.00 | 229.59 | cop[tikv] | table:a, index:tbl_cardcore_anticipated_transaction_group_interest_balance_ix3(sub_account_number, status_code) | range:["1901040103205199","1901040103207489"], keep order:true |
| └─Selection_139(Probe) | 0.00 | 496.06 | cop[tikv] | | gt(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.new_postings_to_int_balance, 0), or(gt(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.debit_interest_rate, 0), gt(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.arrears_interest_rate, 0)) |
| └─TableRowIDScan_137 | 0.00 | 396.26 | cop[tikv] | table:a | keep order:false |
+------------------------------------------+---------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set, 2 warnings (0.00 sec)

HashAGG:
+----------------------------------------+---------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------------+---------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_18 | 2.00 | 9810.68 | root | | group by:Column#66, funcs:firstrow(Column#66)->Column#66 |
| └─Union_19 | 2.00 | 8212.16 | root | | |
| ├─HashAgg_45 | 1.00 | 39034.72 | root | | group by:cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number, funcs:firstrow(cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number)->cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number |
| │ └─IndexLookUp_46 | 1.00 | 37486.96 | root | | |
| │ ├─IndexRangeScan_39(Build) | 19.09 | 4289.97 | cop[tikv] | table:i, index:tbl_cardcore_interest_transaction_group_bearing_balance_ix8(sub_account_number, arrears_balance_update_status) | range:["1901040103205199","1901040103207489"], keep order:false |
| │ └─HashAgg_22(Probe) | 1.00 | 10775.98 | cop[tikv] | | group by:cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.sub_account_number, |
| │ └─Selection_41 | 7.11 | 9115.55 | cop[tikv] | | or(gt(cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.debit_interest_rate, 0), gt(cardcore_issuing.tbl_cardcore_interest_transaction_group_bearing_balance.arrears_interest_rate, 0)) |
| │ └─TableRowIDScan_40 | 19.09 | 8162.83 | cop[tikv] | table:i | keep order:false |
| └─StreamAgg_78 | 1.00 | 2025.87 | root | | group by:cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number, funcs:firstrow(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number)->cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number |
| └─Projection_106 | 0.00 | 1975.47 | root | | cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.sub_account_number, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.debit_interest_rate, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.new_postings_to_int_balance, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.arrears_interest_rate, cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.status_code |
| └─IndexLookUp_105 | 0.00 | 1974.98 | root | | |
| ├─Selection_103(Build) | 0.00 | 279.49 | cop[tikv] | | eq(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.status_code, "ACTIVE") |
| │ └─IndexRangeScan_101 | 0.00 | 229.59 | cop[tikv] | table:a, index:tbl_cardcore_anticipated_transaction_group_interest_balance_ix3(sub_account_number, status_code) | range:["1901040103205199","1901040103207489"], keep order:true |
| └─Selection_104(Probe) | 0.00 | 496.06 | cop[tikv] | | gt(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.new_postings_to_int_balance, 0), or(gt(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.debit_interest_rate, 0), gt(cardcore_issuing.tbl_cardcore_anticipated_transaction_group_interest_balance.arrears_interest_rate, 0)) |
| └─TableRowIDScan_102 | 0.00 | 396.26 | cop[tikv] | table:a | keep order:false |
+----------------------------------------+---------+----------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set, 2 warnings (0.00 sec)`