[MCOL-5432] Disable union pushdown if an ORDER BY or a LIMIT clause is involved until MCOL-5222 is fixed. Created: 2023-02-22  Updated: 2023-03-01  Resolved: 2023-03-01

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

Type: Task Priority: Major
Reporter: Gagan Goel (Inactive) Assignee: Gagan Goel (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description   

Until MCOL-5222 is fixed, disable the UNION pushdown feature introduced in MDEV-25080 for a query involving an ORDER BY or a LIMIT clause, and fallback to the table handler API execution where the full query runs inside the server.

 Comments   
Comment by Gagan Goel (Inactive) [ 2023-02-28 ]

For QA: Please refer to the ticket description. You can use the query in MCOL-4584 and add an order by clause in the outer select:

select count(c1) from wide_table union all select 1 order by 1;
 
EXPLAIN select count(c1) from wide_table union all select 1 order by 1;

Performance would be similar to ColumnStore 6.1.1/22.08.8 which does not have the union pushdown feature. After MCOL-5222 is fixed, UNION in outer select with order by/limit will be pushed down to the engine.

Comment by Daniel Lee (Inactive) [ 2023-03-01 ]

Build verified:
engine: 4d4e4ad30dd2ec494ea9d323e1fa7fa69e97243e
server: 1916028f898cf672a8b79d2b585b1d74f8bcd7db
buildNo: 6965

Verified that union push down was disabled for queries with mentioned clauses, and execution
does not have the performance benefits

Please refer to MCOL-4901 for more performance timing.

Explain plan

MariaDB [mytest]> explain (SELECT   r1.id1,r2.id2
 
    -> FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
 
    -> union select 1,2 order by 1;
 
+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
 
+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
|    1 | PRIMARY      | r1         | ALL  | NULL          | NULL | NULL    | NULL | 2000 |                                                 |
 
|    1 | PRIMARY      | r2         | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
 
|    2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                                  |
 
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using filesort                                  |
 
+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
4 rows in set (0.002 sec)
 
 
 
MariaDB [mytest]> explain (SELECT   r1.id1,r2.id2
 
    -> FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
 
    -> union select 1,2 limit 5;
 
+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
 
+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
|    1 | PRIMARY      | r1         | ALL  | NULL          | NULL | NULL    | NULL | 2000 |                                                 |
 
|    1 | PRIMARY      | r2         | ALL  | NULL          | NULL | NULL    | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
 
|    2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used                                  |
 
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |                                                 |
 
+------+--------------+------------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
4 rows in set (0.002 sec)
 
 
 
 
 
MariaDB [mytest]> explain select count(id1) from r1 union all select 1 order by 1;
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
 
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
 
|    1 | PRIMARY      | r1         | ALL  | NULL          | NULL | NULL    | NULL | 2000 |                |
 
|    2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
 
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using filesort |
 
+------+--------------+------------+------+---------------+------+---------+------+------+----------------+
 
3 rows in set (0.001 sec)
 
 
 
MariaDB [mytest]> explain select count(id1) from r1 union all select 1 limit 5;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
 
|    1 | PRIMARY     | r1    | ALL  | NULL          | NULL | NULL    | NULL | 2000 |                |
 
|    2 | UNION       | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
 
2 rows in set (0.001 sec)

Performance

The performance timing is better in this run comparing to that in MCOL-4901 because this run
is a cached run (data already ready into primproc cache)

MariaDB [mytest]> (SELECT   r1.id1,r2.id2
 
    -> FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
 
    -> union select 1,2 order by 1;
 
+--------+--------+
 
| id1    | id2    |
 
+--------+--------+
 
|      1 |      2 |
 
| 773953 | 773953 |
 
| 773954 | 773954 |
 
| 773955 | 773955 |
 
| 773956 | 773956 |
 
| 773957 | 773957 |
 
| 773958 | 773958 |
 
| 773959 | 773959 |
 
| 773960 | 773960 |
 
| 773961 | 773961 |
 
| 773962 | 773962 |
 
+--------+--------+
 
11 rows in set (6.557 sec)
 
 
 
MariaDB [mytest]> (SELECT   r1.id1,r2.id2
 
    -> FROM r1 JOIN r2  ON r1.id1 = r2.id2 limit 10)
 
    -> union select 1,2 limit 5;
 
+--------+--------+
 
| id1    | id2    |
 
+--------+--------+
 
| 382881 | 382881 |
 
| 382882 | 382882 |
 
| 382883 | 382883 |
 
| 382884 | 382884 |
 
| 382885 | 382885 |
 
+--------+--------+
 
5 rows in set (1 min 47.729 sec)
 
 
 
 
 
MariaDB [mytest]> select count(id1) from r1 union all select 1 order by 1;
 
+------------+
 
| count(id1) |
 
+------------+
 
|          1 |
 
|    1000000 |
 
+------------+
 
2 rows in set (0.238 sec)
 
 
 
MariaDB [mytest]> select count(id1) from r1 union all select 1 limit 5;
 
+------------+
 
| count(id1) |
 
+------------+
 
|    1000000 |
 
|          1 |
 
+------------+
 
2 rows in set (0.187 sec)

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