[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~~ ]

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