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

            Transition Time In Source Status Execution Times
            Michael Widenius made transition -
            Open In Progress
            		4d 21h 34m 1
            Sergei Golubchik made transition -
            In Progress In Testing
            		412d 20h 48m 1
            Michael Widenius made transition -
            In Testing Closed
            		7h 9m 1
            Michael Widenius made transition -
            Closed Stalled
            		17s 1
            Elena Stepanova made transition -
            Stalled In Testing
            		14d 4h 56m 1
            Elena Stepanova made transition -
            In Testing Stalled
            		30d 2h 9m 1
            Sergei Golubchik made transition -
            Stalled Closed
            		6d 17h 21m 1

            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.