Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5432

Disable union pushdown if an ORDER BY or a LIMIT clause is involved until MCOL-5222 is fixed.

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • None
    • 23.02.1
    • 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

          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.

          tntnatbry Gagan Goel (Inactive) added a comment - 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.

          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)
          

          dleeyh Daniel Lee (Inactive) added a comment - 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)

          People

            tntnatbry Gagan Goel (Inactive)
            tntnatbry Gagan Goel (Inactive)
            Daniel Lee Daniel Lee (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.