Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15777

Use inferred IS NOT NULL predicates in the range optimizer

Details

    Description

      Consider an example (inspired by queries generated by Hibernate):

      SELECT * 
      FROM 
        t1
      WHERE 
        t1.subset_id IN (SELECT t1_subsets.id
                         FROM t1_subsets)
      

      The subquery is converted into an inner join (because t1_subsets.id is a primary key).
      ANALYZE output:

      +------+-------------+------------+--------+---------------+---------+---------+------------------+-------+----------+----------+------------+--------------------------+
      | id   | select_type | table      | type   | possible_keys | key     | key_len | ref              | rows  | r_rows   | filtered | r_filtered | Extra                    |
      +------+-------------+------------+--------+---------------+---------+---------+------------------+-------+----------+----------+------------+--------------------------+
      |    1 | PRIMARY     | t1         | ALL    | t1_subset_id  | NULL    | NULL    | NULL             | 50396 | 49999.00 |   100.00 |       0.00 | Using where              |
      |    1 | PRIMARY     | t1_subsets | eq_ref | PRIMARY       | PRIMARY | 4       | j22.t1.subset_id |     1 |     NULL |   100.00 |       NULL | Using where; Using index |
      +------+-------------+------------+--------+---------------+---------+---------+------------------+-------+----------+----------+------------+--------------------------+
      

      t1 has only a few rows which have a non-NULL value for t1.subset_id.
      This is visible in r_filtered column: Early NULLs filtering optimization adds a NOT NULL condition which filters out rows with NULLs.

      A piece of ANALYZE FORMAT=JSON output to confirm this:

          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "possible_keys": ["t1_subset_id"],
            "r_loops": 1,
            "rows": 50396,
            "r_rows": 49999,
            "r_total_time_ms": 290.22,
            "filtered": 100,
            "r_filtered": 0,
            "attached_condition": "t1.subset_id is not null"
          },
      

      However, range optimizer is not able to make use of "Early NULLs filtering". We could have used t1_subset_id index to construct range access, but we dont

      Attachments

        Issue Links

          Activity

            Provided review input for the latest patch: https://lists.launchpad.net/maria-developers/msg11841.html

            psergei Sergei Petrunia added a comment - Provided review input for the latest patch: https://lists.launchpad.net/maria-developers/msg11841.html
            igor Igor Babaev added a comment -

            The task can be formulated in a very simple way:
            1. for each field f participating in the WHERE condition C and in any index infer the predicate IS NOT NULL f.
            2. Use the inferred IS NOT NULL predicates to look for range scans and use them in the optimizer when searching for the optimizer plan.

            (1) can be resolved by finding all null rejecting fields in the WHERE condition (this is similar to how we find null rejecting tables) and choosing only those fields that participate in indexes.
            (2) is already resolved in the patch.

            'Early' null filtering is applied to the keys used for equi-joins, while in this task we need keys used for range access.

            igor Igor Babaev added a comment - The task can be formulated in a very simple way: 1. for each field f participating in the WHERE condition C and in any index infer the predicate IS NOT NULL f. 2. Use the inferred IS NOT NULL predicates to look for range scans and use them in the optimizer when searching for the optimizer plan. (1) can be resolved by finding all null rejecting fields in the WHERE condition (this is similar to how we find null rejecting tables) and choosing only those fields that participate in indexes. (2) is already resolved in the patch. 'Early' null filtering is applied to the keys used for equi-joins, while in this task we need keys used for range access.
            varun Varun Gupta (Inactive) added a comment - The patch http://lists.askmonty.org/pipermail/commits/2019-June/013872.html
            igor Igor Babaev added a comment -

            This task was pushed into 10.5.

            igor Igor Babaev added a comment - This task was pushed into 10.5.

            Here is the commit message for the patch pushed to 10.5

            commit 9380850d874c77656d0c42cfa11bf0d187064849
            Author: Igor Babaev <igor@askmonty.org>
            Date:   Fri Aug 30 18:47:14 2019 -0700
             
                MDEV-15777 Use inferred IS NOT NULL predicates in the range optimizer
                
                This patch introduces the optimization that allows range optimizer to
                consider index range scans that are built employing NOT NULL predicates
                inferred from WHERE conditions and ON expressions.
                The patch adds a new optimizer switch not_null_range_scan.
            
            

            th patch adds a new optimizer_swtich not_null_range_scan, this is currently turned off.

            varun Varun Gupta (Inactive) added a comment - Here is the commit message for the patch pushed to 10.5 commit 9380850d874c77656d0c42cfa11bf0d187064849 Author: Igor Babaev <igor@askmonty.org> Date: Fri Aug 30 18:47:14 2019 -0700   MDEV-15777 Use inferred IS NOT NULL predicates in the range optimizer This patch introduces the optimization that allows range optimizer to consider index range scans that are built employing NOT NULL predicates inferred from WHERE conditions and ON expressions. The patch adds a new optimizer switch not_null_range_scan. th patch adds a new optimizer_swtich not_null_range_scan, this is currently turned off.

            People

              igor Igor Babaev
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.