[MCOL-3714] Window frame RANGE is not optimized Created: 2020-01-06  Updated: 2023-11-21  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.4.2
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: David Hall (Inactive) Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None

Epic Link: ColumnStore Performance Improvements

 Description   

For Window Functions, if a ROWS frame is used, then optimizations are made such that the row falling out of the frame is subtracted (whatever that means for the particular function) and the row coming into the frame is added.

If RANGE frame is used, then the intermediate results from the previous row are ignored and the entire frame is iterated and recalculated.

RANGE is the default for most Window Functions.

This MCOL is to look for ways to optimize RANGE in a similar fashion as ROWS.

 Comments   
Comment by David Hall (Inactive) [ 2020-01-07 ]

Against the standard tpch1 database:
Using RANGE (default):
select avg(o_totalprice) as a, max(o_orderkey) over () as b from orders;
.
.
.
1500000 rows in set (2 days 22 hours 57 min 13.455 sec)

Using ROWS:
select avg(o_totalprice) as a, max(o_orderkey) over (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as b from orders;
.
.
.
1500000 rows in set (13.464 sec)

Generated at Thu Feb 08 02:44:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.