[MDEV-26974] Improve selectivity and related costs in optimizer - Jira

Details

Type: Task
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 11.0.1
Component/s: Optimizer
Labels:
None

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

MDEV-25830 optimizer_use_condition_selectivity=4 sometimes produces worse plan than optimizer_use_condition_selectivity=1

Closed

causes

MDEV-30088 Assertion `cond_selectivity <= 1.0' failed in get_range_limit_read_cost

Closed

MDEV-30098 Server crashes in ha_myisam::index_read_map with index_merge_sort_intersection=on

Closed

MDEV-30104 Server crashes in handler_rowid_filter_check upon ANALYZE TABLE

Closed

MDEV-30256 Wrong result (missing rows) upon join with empty table

Closed

MDEV-30310 Assertion failure in best_access_path upon IN exceeding IN_PREDICATE_CONVERSION_THRESHOLD, derived_with_keys=off

Closed

MDEV-30313 Sporadic assertion `cond_selectivity <= 1.0' failure in get_range_limit_read_cost

Closed

MDEV-30327 Client crashes in print_last_query_cost

Closed

MDEV-30328 Assertion `avg_io_cost != 0.0 || index_cost.io + row_cost.io == 0' failed in Cost_estimate::total_cost()

Closed

MDEV-30360 Assertion `cond_selectivity <= 1.000000001' failed in get_range_limit_read_cost with LIMIT .. OFFSET

Closed

MDEV-30373 Wrong result with range access

Closed

MDEV-30395 Wrong result with semijoin and Federated as outer table

Closed

MDEV-30525 Assertion `ranges > 0' fails in IO_AND_CPU_COST handler::keyread_time

Closed

MDEV-30529 Assertion `rnd_records <= s->found_records' failed in best_access_path

Closed

MDEV-30540 Wrong result with IN list length reaching IN_PREDICATE_CONVERSION_THRESHOLD

Closed

MDEV-30568 Assertion `cond_selectivity <= 1.000000001' failed in get_range_limit_read_cost on empty Merge table

Closed

MDEV-30569 Assertion `!(p->table->table->file->ha_table_flags() & (1ULL << 60))' failed in Duplicate_weedout_picker::check_qep

Closed

MDEV-30603 Wrong result with non-default JOIN_CACHE_LEVEL=[4|5] and USE_STAT_TABLES=[NEVER|COMPLEMENTARY]

Closed

MDEV-31237 Assertion `!(tab->select && tab->select->quick)' failed in make_join_readinfo

Closed

MDEV-31243 Assertion `field->orig_table->stats_is_read' failed in is_eits_usable with JOIN_CACHE_LEVEL > 2

Confirmed

MDEV-31247 Assertion `c >= 0' failed in COST_MULT upon query with many joins

Closed

MDEV-31258 Assertion `cond_selectivity <= 1.000000001' upon range query (actual value 1.33)

Closed

MDEV-31864 Assertion `d >= 0' failed in COST_ADD with join_cache_level > 2 and partitions

Closed

includes

MDEV-30486 Table is not eliminated in bb-11.0

Closed

relates to

MDEV-30375 Server crashes in TABLE_LIST::is_active_sjm/TABLE_LIST::is_sjm_scan_table after query WITH ROLLUP

Closed

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(18 causes, 1 includes, 1 relates to, 6 mentioned in)

Activity

Ascending order - Click to sort in descending order

Michael Widenius created issue - 2021-11-04 16:31

Michael Widenius made changes - 2021-11-09 14:06

Field	Original Value	New Value
Status	Open [ 1 ]	In Progress [ 3 ]

Sergei Golubchik made changes - 2021-11-10 18:09

Description

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

- Ensure that selectivity is <= 0
- 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 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

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 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

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 126997 ]

MariaDB v4 [ 131853 ]

Michael Widenius made changes - 2021-12-15 12:06

Link

This issue blocks ~~MDEV-25830~~ [ ~~MDEV-25830~~ ]

Thejaka Kanewala added a comment - 2021-12-15 19: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

Thejaka Kanewala added a comment - 2021-12-15 19: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

Rob Schwyzer (Inactive) made changes - 2021-12-15 20:58

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32652 ]

Rob Schwyzer (Inactive) made changes - 2021-12-15 21:14

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32654 ]

Rob Schwyzer (Inactive) made changes - 2021-12-15 23:52

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32665 ]

Julien Fritsch made changes - 2021-12-20 08:44

Priority

Major [ 3 ]

Critical [ 2 ]

Michael Widenius added a comment - 2021-12-21 14:11

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.

Michael Widenius added a comment - 2021-12-21 14:11 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.

Michael Widenius made changes - 2021-12-21 14:12

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

Rob Schwyzer (Inactive) made changes - 2021-12-29 21:08

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32680 ]

Rob Schwyzer (Inactive) made changes - 2022-01-05 23:51

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32694 ]

Rob Schwyzer (Inactive) made changes - 2022-01-13 17:39

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32727 ]

Rob Schwyzer (Inactive) made changes - 2022-01-20 02:32

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32747 ]

Rob Schwyzer (Inactive) made changes - 2022-01-26 17:47

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32652 ]

Rob Schwyzer (Inactive) made changes - 2022-01-26 17:50

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 32654 ]

Sergei Golubchik made changes - 2022-02-01 11:33

Fix Version/s		10.9 [ 26905 ]
Fix Version/s	10.8 [ 26121 ]

Sergei Golubchik made changes - 2022-03-15 19:40

Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.9 [ 26905 ]

Sergei Golubchik made changes - 2022-07-26 12:49

Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.10 [ 27530 ]

Sergei Golubchik made changes - 2022-11-08 15:32

Link

This issue blocks MDEV-6713 [ MDEV-6713 ]

Elena Stepanova made changes - 2022-11-24 16:05

Link

This issue causes ~~MDEV-30088~~ [ ~~MDEV-30088~~ ]

Elena Stepanova made changes - 2022-11-25 21:18

Link

This issue causes ~~MDEV-30098~~ [ ~~MDEV-30098~~ ]

Elena Stepanova made changes - 2022-11-27 14:08

Link

This issue causes ~~MDEV-30104~~ [ ~~MDEV-30104~~ ]

Elena Stepanova made changes - 2022-12-17 13:26

Link

This issue causes ~~MDEV-30256~~ [ ~~MDEV-30256~~ ]

Sergei Golubchik made changes - 2022-12-27 10:53

Fix Version/s		11.0 [ 28320 ]
Fix Version/s	10.11 [ 27614 ]

Sergei Golubchik made changes - 2022-12-27 10:54

Status

In Progress [ 3 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2022-12-27 10:54

Assignee

Michael Widenius [ monty ]

Elena Stepanova [ elenst ]

Michael Widenius made changes - 2022-12-27 14:17

Link

This issue blocks MDEV-6713 [ MDEV-6713 ]

Michael Widenius made changes - 2022-12-27 14:45

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

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/

Michael Widenius made changes - 2022-12-27 18:03

issue.field.resolutiondate

2022-12-27 18:03:55.0

2022-12-27 18:03:55.285

Michael Widenius made changes - 2022-12-27 18:03

Fix Version/s		11.0.0 [ 28500 ]
Fix Version/s	11.0 [ 28320 ]
Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

Michael Widenius made changes - 2022-12-27 18:04

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Michael Widenius made changes - 2022-12-27 18:04

Comment

[ Was fixed as by the fix for ~~MDEV-30098~~ ]

Elena Stepanova made changes - 2022-12-27 21:05

Link

This issue causes ~~MDEV-30310~~ [ ~~MDEV-30310~~ ]

Elena Stepanova made changes - 2022-12-28 20:05

Link

This issue causes ~~MDEV-30313~~ [ ~~MDEV-30313~~ ]

Elena Stepanova made changes - 2023-01-02 15:30

Link

This issue causes ~~MDEV-30325~~ [ ~~MDEV-30325~~ ]

Elena Stepanova made changes - 2023-01-02 17:40

Link

This issue causes ~~MDEV-30327~~ [ ~~MDEV-30327~~ ]

Elena Stepanova made changes - 2023-01-02 21:28

Link

This issue causes ~~MDEV-30328~~ [ ~~MDEV-30328~~ ]

Michael Widenius made changes - 2023-01-05 12:56

Link

This issue causes ~~MDEV-30325~~ [ ~~MDEV-30325~~ ]

Sergei Golubchik made changes - 2023-01-05 19:58

Fix Version/s		11.0 [ 28320 ]
Fix Version/s	11.0.0 [ 28500 ]

Elena Stepanova made changes - 2023-01-07 14:03

Link

This issue causes ~~MDEV-30360~~ [ ~~MDEV-30360~~ ]

Elena Stepanova made changes - 2023-01-10 13:39

Link

This issue causes ~~MDEV-30373~~ [ ~~MDEV-30373~~ ]

Alice Sherepa made changes - 2023-01-10 14:50

Link

This issue relates to ~~MDEV-30375~~ [ ~~MDEV-30375~~ ]

Elena Stepanova made changes - 2023-01-10 23:00

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Elena Stepanova made changes - 2023-01-12 15:56

Link

This issue causes ~~MDEV-30395~~ [ ~~MDEV-30395~~ ]

Michael Widenius made changes - 2023-01-27 17:20

Link

This issue includes ~~MDEV-30486~~ [ ~~MDEV-30486~~ ]

Elena Stepanova made changes - 2023-01-31 14:17

Link

This issue causes ~~MDEV-30525~~ [ ~~MDEV-30525~~ ]

Elena Stepanova made changes - 2023-01-31 20:22

Link

This issue causes ~~MDEV-30529~~ [ ~~MDEV-30529~~ ]

Elena Stepanova made changes - 2023-02-01 21:51

Link

This issue causes ~~MDEV-30540~~ [ ~~MDEV-30540~~ ]

Elena Stepanova made changes - 2023-02-04 18:36

Link

This issue causes ~~MDEV-30568~~ [ ~~MDEV-30568~~ ]

Elena Stepanova made changes - 2023-02-04 20:22

Link

This issue causes ~~MDEV-30569~~ [ ~~MDEV-30569~~ ]

Elena Stepanova made changes - 2023-02-07 16:47

Link

This issue causes ~~MDEV-30603~~ [ ~~MDEV-30603~~ ]

Elena Stepanova made changes - 2023-02-10 01:10

Assignee	Elena Stepanova [ elenst ]	Sergei Golubchik [ serg ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2023-02-16 18:32

Fix Version/s		11.0.1 [ 28548 ]
Fix Version/s	11.0 [ 28320 ]
Assignee	Sergei Golubchik [ serg ]	Michael Widenius [ monty ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Elena Stepanova made changes - 2023-05-12 19:10

Link

This issue causes ~~MDEV-31258~~ [ ~~MDEV-31258~~ ]

Elena Stepanova made changes - 2023-05-12 19:11

Link

This issue causes ~~MDEV-31247~~ [ ~~MDEV-31247~~ ]

Elena Stepanova made changes - 2023-05-12 19:11

Link

This issue causes MDEV-31243 [ MDEV-31243 ]

Elena Stepanova made changes - 2023-05-12 19:11

Link

This issue causes ~~MDEV-31237~~ [ ~~MDEV-31237~~ ]

Elena Stepanova made changes - 2023-08-07 13:13

Link

This issue causes ~~MDEV-31864~~ [ ~~MDEV-31864~~ ]

Rob Schwyzer (Inactive) made changes - 2024-04-04 19:53

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36687 ]

Rob Schwyzer (Inactive) made changes - 2024-04-04 23:04

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36755 ]

Rob Schwyzer (Inactive) made changes - 2024-04-05 18:34

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 36687 ]

Jira Automation (IT) made changes - 2024-07-04 01:56

Zendesk Related Tickets		201658
Zendesk active tickets		201658

People

Assignee:: Michael Widenius

Reporter:: Michael Widenius

Votes:: 0 Vote for this issue

Watchers:: 11 Start watching this issue

Dates

Created:: 2021-11-04 16:31

Updated:: 2024-09-05 20:03

Resolved:: 2023-02-16 18:32

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration