[MDEV-26974] Improve selectivity and related costs in optimizer Created: 2021-11-04  Updated: 2023-08-07  Resolved: 2023-02-16

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Fix Version/s: 11.0.1

Type: Task Priority: Critical
Reporter: Michael Widenius Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-25830 optimizer_use_condition_selectivity=4... Closed
PartOf
includes MDEV-30486 Table is not eliminated in bb-11.0 Closed
Problem/Incident
causes MDEV-30088 Assertion `cond_selectivity <= 1.0' f... Closed
causes MDEV-30098 Server crashes in ha_myisam::index_re... Closed
causes MDEV-30104 Server crashes in handler_rowid_filte... Closed
causes MDEV-30256 Wrong result (missing rows) upon join... Closed
causes MDEV-30310 Assertion failure in best_access_path... Closed
causes MDEV-30313 Sporadic assertion `cond_selectivity ... Closed
causes MDEV-30327 Client crashes in print_last_query_cost Closed
causes MDEV-30328 Assertion `avg_io_cost != 0.0 || inde... Closed
causes MDEV-30360 Assertion `cond_selectivity <= 1.0000... Closed
causes MDEV-30373 Wrong result with range access Closed
causes MDEV-30395 Wrong result with semijoin and Federa... Closed
causes MDEV-30525 Assertion `ranges > 0' fails in IO_AN... Closed
causes MDEV-30529 Assertion `rnd_records <= s->found_re... Closed
causes MDEV-30540 Wrong result with IN list length reac... Closed
causes MDEV-30568 Assertion `cond_selectivity <= 1.0000... Closed
causes MDEV-30569 Assertion `!(p->table->table->file->h... Closed
causes MDEV-30603 Wrong result with non-default JOIN_CA... Stalled
causes MDEV-31237 Assertion `!(tab->select && tab->sele... Closed
causes MDEV-31243 Assertion `field->orig_table->stats_i... Confirmed
causes MDEV-31247 Assertion `c >= 0' failed in COST_MUL... Closed
causes MDEV-31258 Assertion `cond_selectivity <= 1.0000... Closed
causes MDEV-31864 Assertion `d >= 0' failed in COST_ADD... Closed
Relates
relates to MDEV-30375 Server crashes in TABLE_LIST::is_acti... Closed

 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/

 Comments   
Comment by Thejaka Kanewala [ 2021-12-15 ]

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

Comment by Michael Widenius [ 2021-12-21 ]

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.

Generated at Thu Feb 08 09:49:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.