We are using trino version 377
We are trying to pushdown aggregates on mysql database. All aggregates working fine except sum.
Could you please help us to fix this issue. Is there any specific config required to use SUM pushdown?
Is there any limitation with SUM aggregate function?
Regards,
Mahebub Sayyed
Can you post your query and the output of explain analyze? Also, 377 is pretty old now, you should try with a newer release.
Please find the below requested details.
trino:test_schema> explain select sum(amount) from t1;
Query Plan
------------------------------------------------------------------------------------------------------------------------
Fragment 0 [SINGLE]
Output layout: [sum]
Output partitioning: SINGLE []
Stage Execution Strategy: UNGROUPED_EXECUTION
Output[_col0]
│ Layout: [sum:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ _col0 := sum
└─ Aggregate(FINAL)
│ Layout: [sum:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ sum := sum("sum_1")
└─ LocalExchange[SINGLE] ()
│ Layout: [sum_1:row(bigint, boolean, bigint, boolean)]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ RemoteSource[1]
Layout: [sum_1:row(bigint, boolean, bigint, boolean)]
Fragment 1 [SOURCE]
Output layout: [sum_1]
Output partitioning: SINGLE []
Stage Execution Strategy: UNGROUPED_EXECUTION
Aggregate(PARTIAL)
│ Layout: [sum_1:row(bigint, boolean, bigint, boolean)]
│ sum_1 := sum("amount_0")
└─ ScanProject[table = mysql:test_schema.t1 test_schema.t1 columns=[amount:integer:INT], grouped = false]
Layout: [amount_0:bigint]
Estimates: {rows: 1 (9B), cpu: 5, memory: 0B, network: 0B}/{rows: 1 (9B), cpu: 14, memory: 0B, network: 0B}
amount_0 := CAST("amount" AS bigint)
amount := amount:integer:INT
(1 row)
Query 20220828_165643_00003_ci2sq, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.42 [0 rows, 0B] [0 rows/s, 0B/s]
trino:test_schema> explain analyze select sum(amount) from t1;
Query Plan
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Fragment 1 [SINGLE]
CPU: 3.25ms, Scheduled: 29.66ms, Blocked 367.20ms (Input: 234.73ms, Output: 0.00ns), Input: 1 row (27B); per task: avg.: 1.00 std.dev.: 0.00, Output: 1 row (9B)
Output layout: [sum]
Output partitioning: SINGLE []
Stage Execution Strategy: UNGROUPED_EXECUTION
Aggregate(FINAL)
│ Layout: [sum:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ CPU: 1.00ms (1.89%), Scheduled: 3.00ms (2.91%), Blocked: 0.00ns (0.00%), Output: 1 row (9B)
│ Input avg.: 1.00 rows, Input std.dev.: 0.00%
│ sum := sum("sum_1")
└─ LocalExchange[SINGLE] ()
│ Layout: [sum_1:row(bigint, boolean, bigint, boolean)]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ CPU: 0.00ns (0.00%), Scheduled: 1.00ms (0.97%), Blocked: 133.00ms (36.14%), Output: 1 row (27B)
│ Input avg.: 0.50 rows, Input std.dev.: 100.00%
└─ RemoteSource[2]
Layout: [sum_1:row(bigint, boolean, bigint, boolean)]
CPU: 0.00ns (0.00%), Scheduled: 2.00ms (1.94%), Blocked: 235.00ms (63.86%), Output: 1 row (27B)
Input avg.: 0.50 rows, Input std.dev.: 100.00%
Fragment 2 [SOURCE]
CPU: 53.11ms, Scheduled: 99.46ms, Blocked 0.00ns (Input: 0.00ns, Output: 0.00ns), Input: 2 rows (0B); per task: avg.: 2.00 std.dev.: 0.00, Output: 1 row (27B)
Output layout: [sum_1]
Output partitioning: SINGLE []
Stage Execution Strategy: UNGROUPED_EXECUTION
Aggregate(PARTIAL)
│ Layout: [sum_1:row(bigint, boolean, bigint, boolean)]
│ CPU: 5.00ms (9.43%), Scheduled: 19.00ms (18.45%), Blocked: 0.00ns (0.00%), Output: 1 row (27B)
│ Input avg.: 2.00 rows, Input std.dev.: 0.00%
│ sum_1 := sum("amount_0")
└─ ScanProject[table = mysql:test_schema.t1 test_schema.t1 columns=[amount:integer:INT], grouped = false]
Layout: [amount_0:bigint]
Estimates: {rows: 1 (9B), cpu: 5, memory: 0B, network: 0B}/{rows: 1 (9B), cpu: 14, memory: 0B, network: 0B}
CPU: 47.00ms (88.68%), Scheduled: 78.00ms (75.73%), Blocked: 0.00ns (0.00%), Output: 2 rows (18B)
Input avg.: 2.00 rows, Input std.dev.: 0.00%
amount_0 := CAST("amount" AS bigint)
amount := amount:integer:INT
Input: 2 rows (0B), Filtered: 0.00%
(1 row)
Query 20220828_165816_00004_ci2sq, FINISHED, 1 node
Splits: 7 total, 7 done (100.00%)
0.73 [2 rows, 0B] [2 rows/s, 0B/s]
This is supported in the latest release of Trino. Please upgrade and test to verify - MySQL connector — Trino 422 Documentation