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

Significant performance degradation when UNION ALL is used in an outer select, compared to in a subquery.

    XMLWordPrintable

Details

    Description

      There appears to be a significant difference in the query execution times between the query Q1:

      MariaDB [test]> select count(c1) from wide_table union all select 1;
      		 
      +-----------+
      		 
      | count(c1) |
      		 
      +-----------+
      		 
      |   1000000 |
      		 
      |         1 |
      		 
      +-----------+
      		 
      2 rows in set (1.941 sec)

      And query Q2:

      MariaDB [test]> select * from (select count(c1) from wide_table union all select 1) q;
      		 
      +-----------+
      		 
      | count(c1) |
      		 
      +-----------+
      		 
      |         1 |
      		 
      |   1000000 |
      		 
      +-----------+
      		 
      2 rows in set (0.035 sec)

      Here, wide_table is a table with 100 INT columns. As can be seen, Q1 is 60x slower than Q2.

      The difference is, Q2 runs using the ColumnStore select handler whereas Q1 does not. Q1's execution path calls mysql_union() in sql/sql_union.cc which currently does not support select handler. This needs to be fixed.

      Attachments

        Issue Links

          duplicates

          Task - A task that needs to be done. MCOL-4901 Allow pushdown of queries involving UNIONs in outer select to ColumnStore

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          is caused by

          Task - A task that needs to be done. MDEV-25080 Allow pushdown of queries involving UNIONs in outer select to foreign engines

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MCOL-4569 Queries with UNION ALL perform disproportionally badly

          • Major - Major loss of function.
          • Closed

          Activity

            People

              Unassigned Unassigned
              tntnatbry Gagan Goel (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.