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

Improve selectivity and related costs in optimizer

Details

    Description

      There is a cleanup task for several things in the optimizer.

      • Ensure that 0 <= selectivity <= 1
      • Ensure that selectivity calculation doesn't return too few row
      • Calculate and use index only cost when appropriate.
      • Examine and improve cost calculation in the optimizer for:
      • filters
      • Ensure that filter cost calculation also include index only cost and compare with WHERE
      • ranges
      • table scans
      • hash and joins and cached full joins
      • group by
      • Adjusted Range_rowid_filter_cost_info lookup cost for very small arrays.
      • Limit calculated rows to the number of rows in the table
      • Ensure that test_quick_select doesn't return more rows than in the table

      A lot of other optimizer related issues, documented at
      https://mariadb.com/kb/en/the-optimizer-cost-model-from-mariadb-11-0/

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25830 optimizer_use_condition_selectivity=4 sometimes produces worse plan than optimizer_use_condition_selectivity=1

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30088 Assertion `cond_selectivity <= 1.0' failed in get_range_limit_read_cost

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30098 Server crashes in ha_myisam::index_read_map with index_merge_sort_intersection=on

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30104 Server crashes in handler_rowid_filter_check upon ANALYZE TABLE

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30256 Wrong result (missing rows) upon join with empty table

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30310 Assertion failure in best_access_path upon IN exceeding IN_PREDICATE_CONVERSION_THRESHOLD, derived_with_keys=off

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30313 Sporadic assertion `cond_selectivity <= 1.0' failure in get_range_limit_read_cost

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30327 Client crashes in print_last_query_cost

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30328 Assertion `avg_io_cost != 0.0 || index_cost.io + row_cost.io == 0' failed in Cost_estimate::total_cost()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30360 Assertion `cond_selectivity <= 1.000000001' failed in get_range_limit_read_cost with LIMIT .. OFFSET

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30373 Wrong result with range access

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30395 Wrong result with semijoin and Federated as outer table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30525 Assertion `ranges > 0' fails in IO_AND_CPU_COST handler::keyread_time

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30529 Assertion `rnd_records <= s->found_records' failed in best_access_path

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30540 Wrong result with IN list length reaching IN_PREDICATE_CONVERSION_THRESHOLD

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30568 Assertion `cond_selectivity <= 1.000000001' failed in get_range_limit_read_cost on empty Merge table

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30569 Assertion `!(p->table->table->file->ha_table_flags() & (1ULL << 60))' failed in Duplicate_weedout_picker::check_qep

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30603 Wrong result with non-default JOIN_CACHE_LEVEL=[4|5] and USE_STAT_TABLES=[NEVER|COMPLEMENTARY]

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31237 Assertion `!(tab->select && tab->select->quick)' failed in make_join_readinfo

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31243 Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with JOIN_CACHE_LEVEL > 2

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31247 Assertion `c >= 0' failed in COST_MULT upon query with many joins

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31258 Assertion `cond_selectivity <= 1.000000001' upon range query (actual value 1.33)

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31864 Assertion `d >= 0' failed in COST_ADD with join_cache_level > 2 and partitions

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30486 Table is not eliminated in bb-11.0

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30375 Server crashes in TABLE_LIST::is_active_sjm/TABLE_LIST::is_sjm_scan_table after query WITH ROLLUP

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          mentioned in

          Page Loading...

          Page Loading...

          Page Loading...

          Page Loading...

          Page Loading...

          Page Loading...

          Activity

            Ascending order - Click to sort in descending order
            thejaka Thejaka Kanewala added a comment -

            Hello Michael,

            I am interested in understanding this task in detail. The description for this task is very concise and hard to understand what the task is really about. It will be really great if you could elaborate each point in detail.

            Few specific questions:
            1. The description says "Ensure that 0 <= selectivity <= 1" – does this mean this task applies only when optimizer_use_condition_selectivity=1 ?
            2. What MariaDB versions are affected by this cleanup task ?

            Please let me know.

            Thank you
            Thejaka

            thejaka Thejaka Kanewala added a comment - Hello Michael, I am interested in understanding this task in detail. The description for this task is very concise and hard to understand what the task is really about. It will be really great if you could elaborate each point in detail. Few specific questions: 1. The description says "Ensure that 0 <= selectivity <= 1" – does this mean this task applies only when optimizer_use_condition_selectivity=1 ? 2. What MariaDB versions are affected by this cleanup task ? Please let me know. Thank you Thejaka
            monty Michael Widenius added a comment -

            Answer to Thejaka:
            1) It applies always
            2) I am working on MariaDB 10.7 code base. Will move it to the next alpha MariaDB version when done.

            You can find out more what is already done by examining 10.7-selectivity branch.
            The last commit is still work on progress (test results are not yet up to date). code is more or less correct, but a lot of additional work is done in my tree. The commit messages contains a lot of information of what has been done.

            monty Michael Widenius added a comment - Answer to Thejaka: 1) It applies always 2) I am working on MariaDB 10.7 code base. Will move it to the next alpha MariaDB version when done. You can find out more what is already done by examining 10.7-selectivity branch. The last commit is still work on progress (test results are not yet up to date). code is more or less correct, but a lot of additional work is done in my tree. The commit messages contains a lot of information of what has been done.

            People

              monty Michael Widenius
              monty Michael Widenius
              Votes:
              0 Vote for this issue
              Watchers:
              11 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.