Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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.
Attachments
Activity
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)
|
For QA: Please refer to the ticket description. You can use the query in
MCOL-4584and add an order by clause in the outer select: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.