[MCOL-4563] Investigate and correct a CPU / elapsed anomaly with UNION Created: 2021-02-25  Updated: 2021-04-04  Resolved: 2021-04-04

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: None
Fix Version/s: 6.1.1

Type: Task Priority: Major
Reporter: Gregory Dorman (Inactive) Assignee: Gregory Dorman (Inactive)
Resolution: Duplicate Votes: 0
Labels: None

Attachments: Microsoft Word Summary.xlsx    
Issue Links:
Duplicate
duplicates MCOL-4569 Queries with UNION ALL perform dispro... Closed
Epic Link: Columnstore - Technical debts in Optimizer / Rewriter

 Description   

This was tested in 3 node cluster, with and without shared folders. The problem is that UNION takes far more than the sum of its branches. This is reflect in both elapsed and CPU time consumed by ExeMgr. See consolidated summary in the attachement, as well as an easy reproduction below.

{{
MariaDB [bts]> select count(year) from (select * from flights where year <2018) q;
select count(year) from (select * from flights where year >2018) q;
select count(year) from (select * from flights where year <2018 union all select * from flights where year>2018) q;-------------

count(year)

-------------

17111358

-------------
1 row in set, 1 warning (2.189 sec)

MariaDB [bts]> select count(year) from (select * from flights where year >2018) q;

-------------

count(year)

-------------

12717658

-------------
1 row in set, 1 warning (1.531 sec)

MariaDB [bts]> select count(year) from (select * from flights where year <2018 union all select * from flights where year>2018) q;

-------------

count(year)

-------------

29829016

-------------
1 row in set, 1 warning (11.077 sec)

MariaDB [bts]>
MariaDB [bts]> select calgettrace();
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calgettrace()

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM flights 3028 (year) 0 6144 3272 10.821 17111358
TNS UM - - - - - - 10.819 17111358
BPS PM flights 3028 (year) 0 3272 6144 8.824 12717658
TNS UM - - - - - - 8.967 12717658
TUS UM - - - - - - 10.781 28241118
TAS UM - - - - - - 10.714 1
TNS UM - - - - - - 0.000 1

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.000 sec)
}}
Generated at Thu Feb 08 02:51:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.