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

Extent elimination doesn't work for a query with a WHERE clause containing only non-const columns

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 1.2.5, 1.4.4, 1.5.3
    • 23.10
    • ExeMgr
    • None

    Description

      As can be seen below, PartitionBlocksEliminated=0 in the query stats:

      MariaDB [test]> create table cs1 (a int, b int)engine=columnstore;
      Query OK, 0 rows affected (0.449 sec)
       
      MariaDB [test]> insert into cs1 values (1234, 1233);
      Query OK, 1 row affected (0.165 sec)
       
      MariaDB [test]> select calshowpartitions('cs1', 'a');
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | calshowpartitions('cs1', 'a')                                                                                                                                |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Part#     Min                           Max                           Status
        0.0.1     N/A                           N/A                           Enabled |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.015 sec)
       
      MariaDB [test]> select calshowpartitions('cs1', 'b');
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | calshowpartitions('cs1', 'b')                                                                                                                                |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Part#     Min                           Max                           Status
        0.0.1     N/A                           N/A                           Enabled |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.018 sec)
       
      MariaDB [test]> select a, b from cs1;
      +------+------+
      | a    | b    |
      +------+------+
      | 1234 | 1233 |
      +------+------+
      1 row in set, 1 warning (0.051 sec)
       
      MariaDB [test]> select calshowpartitions('cs1', 'a');
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | calshowpartitions('cs1', 'a')                                                                                                                                |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Part#     Min                           Max                           Status
        0.0.1     1234                          1234                          Enabled |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> select calshowpartitions('cs1', 'b');
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | calshowpartitions('cs1', 'b')                                                                                                                                |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Part#     Min                           Max                           Status
        0.0.1     N/A                           N/A                           Enabled |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> select b, a from cs1;
      +------+------+
      | b    | a    |
      +------+------+
      | 1233 | 1234 |
      +------+------+
      1 row in set, 1 warning (0.010 sec)
       
      MariaDB [test]> select calshowpartitions('cs1', 'b');
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | calshowpartitions('cs1', 'b')                                                                                                                                |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Part#     Min                           Max                           Status
        0.0.1     1233                          1233                          Enabled |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> select * from cs1 where a < b;
      Empty set, 1 warning (0.011 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                   |
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 9999 | Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-0; CacheI/O-2; BlocksTouched-2; PartitionBlocksEliminated-0; MsgBytesIn-110B; MsgBytesOut-654B; Mode-Distributed |
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select calgettrace();
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | calgettrace()                                                                                                                                                                                                    |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 
      Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows 
      BPS  PM   cs1   4040     (a,b)             0   2   0   0.002   0    
      TNS  UM   -     -        -                 -   -   -   0.000   0    
       |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            tntnatbry Gagan Goel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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