[MDEV-27576] Use DESC indexes for MIN/MAX optimization - Jira

Details

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

Description

As of c10e10c6, opt_sum.cc is still stubbed for DESC indexes

      if (part->key_part_flag & HA_REVERSE_SORT)

        break; // TODO MDEV-13756

Given that we are about to close ~~MDEV-13756~~, it looks strange: either it was forgotten and needs to be enabled, or maybe it requires a better reference.

Given the above, the optimization expectedly doesn't work.

--source include/have_sequence.inc

create or replace table t1 (id int, key(id));

insert into t1 select seq from seq_1_to_100 order by rand(1);

explain extended select max(id) from t1 where id > 50;

create or replace table t1 (id int, key(id desc));

insert into t1 select seq from seq_1_to_100 order by rand(1);

explain extended select max(id) from t1 where id > 50;

# Cleanup

drop table t1;

With the ASC index, it is

preview-10.8-MDEV-13756-desc-indexes c10e10c6
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
\| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| filtered \| Extra \|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
\| 1 \| SIMPLE \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| Select tables optimized away \|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+

With the DESC index, it is

+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+

| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |

+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+

|    1 | SIMPLE      | t1    | range | id            | id   | 5       | NULL | 50   |   100.00 | Using where; Using index |

+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+

Attachments

Issue Links

is caused by

MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

Closed

relates to

MDEV-32732 Support DESC indexes in loose scan optimization

In Testing

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2022-01-22 14:07

Elena Stepanova made changes - 2022-01-22 14:07

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-13756~~ [ ~~MDEV-13756~~ ]

Elena Stepanova made changes - 2022-01-22 14:09

Description

As of c10e10c6, opt_sum.cc is still stubbed for DESC indexes
{noformat}
if (part->key_part_flag & HA_REVERSE_SORT)
break; // TODO ~~MDEV-13756~~
{noformat}
Given that we are about to close ~~MDEV-13756~~, it looks strange: either it was forgotten and needs to be enabled, or maybe it requires a better reference.

Given the above, the optimization expectedly doesn't work.

{code:sql}
--source include/have_innodb.inc
--source include/have_sequence.inc

create or replace table t1 (id int, key(id));
insert into t1 select seq from seq_1_to_100 order by rand(1);
explain extended select max(id) from t1 where id > 50;

create or replace table t1 (id int, key(id desc));
insert into t1 select seq from seq_1_to_100 order by rand(1);
explain extended select max(id) from t1 where id > 50;

# Cleanup
drop table t1;
{code}

With the ASC index, it is
{code:sql|title=preview-10.8-~~MDEV-13756~~-desc-indexes c10e10c6}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
{code}
With the DESC index, it is
{code:sql}
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | range | id | id | 5 | NULL | 50 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
{code}

As of c10e10c6, opt_sum.cc is still stubbed for DESC indexes
{noformat}
if (part->key_part_flag & HA_REVERSE_SORT)
break; // TODO ~~MDEV-13756~~
{noformat}
Given that we are about to close ~~MDEV-13756~~, it looks strange: either it was forgotten and needs to be enabled, or maybe it requires a better reference.

Given the above, the optimization expectedly doesn't work.

{code:sql}
--source include/have_sequence.inc

create or replace table t1 (id int, key(id));
insert into t1 select seq from seq_1_to_100 order by rand(1);
explain extended select max(id) from t1 where id > 50;

create or replace table t1 (id int, key(id desc));
insert into t1 select seq from seq_1_to_100 order by rand(1);
explain extended select max(id) from t1 where id > 50;

# Cleanup
drop table t1;
{code}

With the ASC index, it is
{code:sql|title=preview-10.8-~~MDEV-13756~~-desc-indexes c10e10c6}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------------------+
{code}
With the DESC index, it is
{code:sql}
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | range | id | id | 5 | NULL | 50 | 100.00 | Using where; Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
{code}

Sergei Golubchik made changes - 2022-01-22 20:35

Assignee

Sergei Golubchik [ serg ]

Sergei Petrunia [ psergey ]

Elena Stepanova made changes - 2022-01-23 12:23

Comment

[ A comment with security level 'Developers' was removed. ]

Sergei Petrunia made changes - 2022-06-10 14:27

Fix Version/s		10.11 [ 27614 ]
Fix Version/s	10.8 [ 26121 ]

Sergei Golubchik made changes - 2022-08-02 19:58

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

Sergei Petrunia made changes - 2022-08-15 11:42

Labels

optimizer-easy optimizer-feature

Sergei Petrunia made changes - 2022-08-15 11:42

Priority

Critical [ 2 ]

Major [ 3 ]

Yuchen Pei made changes - 2023-06-02 03:59

Assignee

Sergei Petrunia [ psergey ]

Yuchen Pei [ JIRAUSER52627 ]

Yuchen Pei made changes - 2023-06-02 04:02

Affects Version/s	N/A [ 14700 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Yuchen Pei made changes - 2023-06-02 04:05

Fix Version/s		11.2 [ 28603 ]
Fix Version/s	11.0 [ 28320 ]

Yuchen Pei made changes - 2023-06-02 07:03

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2023-06-05 17:23

Summary

Use of DESC indexes is disabled for MIN/MAX optimization

Use DESC indexes for MIN/MAX optimization

Yuchen Pei made changes - 2023-08-16 02:00

Fix Version/s		11.3 [ 28565 ]
Fix Version/s	11.2 [ 28603 ]

Yuchen Pei made changes - 2023-08-16 02:00

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2023-09-17 18:02

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.3 [ 28565 ]

Sergei Golubchik made changes - 2023-10-30 12:59

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Yuchen Pei made changes - 2023-11-02 00:28

Assignee	Yuchen Pei [ JIRAUSER52627 ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2023-11-03 08:53

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.5 [ 29506 ]

Sergei Petrunia made changes - 2023-11-08 11:46

Assignee	Sergei Petrunia [ psergey ]	Yuchen Pei [ JIRAUSER52627 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Petrunia made changes - 2023-11-08 11:47

Link

This issue relates to MDEV-32732 [ MDEV-32732 ]

Sergei Golubchik made changes - 2023-11-08 15:09

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Yuchen Pei made changes - 2023-11-09 00:48

Assignee	Yuchen Pei [ JIRAUSER52627 ]	Sergei Petrunia [ psergey ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Petrunia made changes - 2023-11-13 07:47

Assignee	Sergei Petrunia [ psergey ]	Yuchen Pei [ JIRAUSER52627 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Yuchen Pei made changes - 2023-11-14 02:00

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Yuchen Pei made changes - 2023-11-14 02:00

Assignee

Yuchen Pei [ JIRAUSER52627 ]

Lena Startseva [ JIRAUSER50478 ]

Sergei Petrunia made changes - 2023-11-21 11:35

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	11.5 [ 29506 ]

Lena Startseva made changes - 2023-12-11 15:26

Assignee	Lena Startseva [ JIRAUSER50478 ]	Yuchen Pei [ JIRAUSER52627 ]
Status	In Testing [ 10301 ]	Stalled [ 10000 ]

Yuchen Pei made changes - 2023-12-14 23:10

Fix Version/s		11.4.1 [ 29523 ]
Fix Version/s	11.4 [ 29301 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2023-12-22 09:35

Link

This issue causes TODO-4495 [ TODO-4495 ]

Sergei Golubchik made changes - 2023-12-22 11:12

Link

This issue causes TODO-4495 [ TODO-4495 ]

Sergei Golubchik made changes - 2023-12-22 11:13

Link

This issue is part of TODO-4495 [ TODO-4495 ]

Ralf Gebhardt made changes - 2023-12-27 10:20

Labels

optimizer-easy optimizer-feature

Preview_11.4 optimizer-easy optimizer-feature

Rob Schwyzer (Inactive) made changes - 2024-04-04 20:02

Remote Link

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

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

Remote Link

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

People

Assignee:: Yuchen Pei

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2022-01-22 14:07

Updated:: 2024-04-05 18:34

Resolved:: 2023-12-14 23:10

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