Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-1, 10.2.2-2, 10.2.2-3
Description
While computing window functions, we can take advantage of the general use case where they specify the same PARTITION and ORDER BY criteria.
We already take advantage of this fact by only sorting the internal tmp table as few times as possible. Filling the window function values within the internal tmp table can be performed during one pass / sort as well. Currently we do a pass for each window function.
In order to do this, we must refactor the computation code to be aware of multiple functions during a scan.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.2 [ 14601 ] |
| Sprint | 10.2.1-3 [ 65 ] |
| Rank | Ranked higher |
| Sprint | 10.2.1-3 [ 65 ] | 10.2.1-3, 10.2.1-4 [ 65, 66 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Sprint | 10.2.1-3, 10.2.1-4 [ 65, 66 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5 [ 65, 66, 68 ] |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5 [ 65, 66, 68 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1 [ 65, 66, 68, 77 ] |
| Rank | Ranked lower |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1 [ 65, 66, 68, 77 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1 [ 65, 66, 68, 77, 79 ] |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1 [ 65, 66, 68, 77, 79 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2 [ 65, 66, 68, 77, 79, 82 ] |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2 [ 65, 66, 68, 77, 79, 82 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 65, 66, 68, 77, 79, 82, 83 ] |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 65, 66, 68, 77, 79, 82, 83 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-1 [ 65, 66, 68, 77, 79, 82, 83, 89 ] |
| Rank | Ranked higher |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-1 [ 65, 66, 68, 77, 79, 82, 83, 89 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2 [ 65, 66, 68, 77, 79, 82, 83, 89, 92 ] |
| Rank | Ranked lower |
| Description |
While computing window functions, we can take advantage of the general use case where they specify the same PARTITION and ORDER BY criteria.
We already take advantage of this fact by only sorting the internal tmp table as few times as possible. Filling the window function values within the internal tmp table can be performed during one pass / sort as well. Currently we do a pass for each window function. In order to do this, we must refactor the computation code to be aware of multiple functions during a scan. |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2 [ 65, 66, 68, 77, 79, 82, 83, 89, 92 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 65, 66, 68, 77, 79, 82, 83, 89, 92, 94 ] |
| Fix Version/s | 10.2.2 [ 22013 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Sprint | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 65, 66, 68, 77, 79, 82, 83, 89, 92, 94 ] | 10.2.1-3, 10.2.1-4, 10.2.1-5, 10.2.2-1, 10.2.2-1, 10.2.2-2, 10.2.2-1, 10.2.2-2, 10.2.2-3 [ 65, 66, 68, 77, 79, 82, 89, 92, 94 ] |
| Workflow | MariaDB v3 [ 75653 ] | MariaDB v4 [ 132867 ] |
While refactoring code for this issue, I have reduced the algorithms that we have for computing window functions to only one. Stream-able functions such as RANK and ROW_NUMBER are now computed by making use of cursors, instead of a specific construct just for them. Performance wise they behave the same as before.