[MDEV-28246] Optimizer uses all partitions during an update in MariaDB 10.6.x but not in 10.2.x - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL)
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4
Component/s: Optimizer, Partitioning
Labels:
Environment:
Windows Server

Description

Simple Test
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src (
part INT(1), a INT(1),
b INT(1),
PRIMARY KEY (a,part),
INDEX b (b,part)
)ENGINE=InnoDB
PARTITION BY LIST (part) (
PARTITION Current VALUES IN (0),
PARTITION Relevant VALUES IN (1),
PARTITION Archive VALUES IN (2)
);

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON
UPDATE trg JOIN src USING(a) SET trg.part=1
WHERE trg.part=1 AND src.part=2 ;

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

C25636c.png
33 kB
2022-04-06 07:08
C25636d.png
35 kB
2022-04-06 07:08

Issue Links

relates to

MDEV-22248 Optimizer chooses wrong strategy on delete

Closed

MDEV-22537 optimizer_use_cond_selectivity > 1 can cause slow plans

Closed

MDEV-25480 Optimizer uses wrong index

Closed

Activity

Ascending order - Click to sort in descending order

Sven Heidrich created issue - 2022-04-06 07:31

Sven Heidrich made changes - 2022-04-06 07:33

Field	Original Value	New Value
Description	We used 10.2.x for a long time, where update commands on a partition were quite fast. The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT. {noformat:title=Simple Test} USE test; DROP TABLE IF EXISTS src; DROP TABLE IF EXISTS trg; CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2)); CREATE TABLE trg LIKE src; INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ; {noformat}	We used 10.2.x for a long time, where update commands on a partition were quite fast. The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT. !C25636c.png\|thumbnail! {noformat:title=Simple Test} USE test; DROP TABLE IF EXISTS src; DROP TABLE IF EXISTS trg; CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2)); CREATE TABLE trg LIKE src; INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2); EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ; {noformat}

Sven Heidrich made changes - 2022-04-06 07:34

Description

We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.
!C25636c.png|thumbnail!
{noformat:title=Simple Test}
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB
PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2));

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ;
{noformat}

We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.
!C25636c.png|thumbnail! !C25636d.png|thumbnail!
{noformat:title=Simple Test}
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB
PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2));

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ;
{noformat}

Sven Heidrich made changes - 2022-04-06 07:34

Description

We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.
!C25636c.png|thumbnail! !C25636d.png|thumbnail!
{noformat:title=Simple Test}
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB
PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2));

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ;
{noformat}

We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.

!C25636c.png|thumbnail! !C25636d.png|thumbnail!
{noformat:title=Simple Test}
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB
PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2));

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ;
{noformat}

Sven Heidrich made changes - 2022-04-06 07:37

Link

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

Sven Heidrich made changes - 2022-04-06 07:37

Link

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

Sven Heidrich made changes - 2022-04-06 07:37

Link

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

Sven Heidrich made changes - 2022-04-06 10:06

Affects Version/s		10.6 [ 24028 ]
Affects Version/s	10.6.7 [ 26812 ]

Alice Sherepa made changes - 2022-04-06 10:18

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.7 [ 24805 ]

Alice Sherepa made changes - 2022-04-06 11:37

Assignee

Sergei Petrunia [ psergey ]

Alice Sherepa made changes - 2022-04-06 11:37

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]

Sergei Petrunia made changes - 2022-04-06 13:53

Assignee

Sergei Petrunia [ psergey ]

Oleg Smirnov [ JIRAUSER50405 ]

Oleg Smirnov made changes - 2022-04-11 09:30

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Petrunia made changes - 2022-04-13 10:24

Description

We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.

!C25636c.png|thumbnail! !C25636d.png|thumbnail!
{noformat:title=Simple Test}
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src ( part INT(1), a INT(1), b INT(1), PRIMARY KEY (a,part), INDEX b (b,part))ENGINE=InnoDB
PARTITION BY LIST (part) ( PARTITION Current VALUES IN (0), PARTITION Relevant VALUES IN (1), PARTITION Archive VALUES IN (2));

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON UPDATE trg JOIN src USING(a) SET trg.part=1 WHERE trg.part=1 AND src.part=2 ;
{noformat}

We used 10.2.x for a long time, where update commands on a partition were quite fast.
The third partition archive is very large and should not be part of the update. MariaDB 10.6.x uses all partitions on UPDATE, not on SELECT.

!C25636c.png|thumbnail! !C25636d.png|thumbnail!
{noformat:title=Simple Test}
USE test;
DROP TABLE IF EXISTS src;
DROP TABLE IF EXISTS trg;

CREATE TABLE src (
  part INT(1), a INT(1),
  b INT(1),
  PRIMARY KEY (a,part),
  INDEX b (b,part)
)ENGINE=InnoDB
PARTITION BY LIST (part) (
  PARTITION Current VALUES IN (0),
  PARTITION Relevant VALUES IN (1),
  PARTITION Archive VALUES IN (2)
);

CREATE TABLE trg LIKE src;
INSERT INTO src (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);
INSERT INTO trg (part,a,b) VALUES (0,0,0),(1,1,1),(2,2,2);

EXPLAIN FORMAT=JSON
UPDATE trg JOIN src USING(a) SET trg.part=1
WHERE trg.part=1 AND src.part=2 ;
{noformat}

Oleg Smirnov made changes - 2022-05-14 03:28

Assignee	Oleg Smirnov [ JIRAUSER50405 ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Petrunia made changes - 2022-05-17 12:44

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Sergei Petrunia made changes - 2022-05-17 12:44

Assignee

Sergei Petrunia [ psergey ]

Oleg Smirnov [ JIRAUSER50405 ]

Oleg Smirnov made changes - 2022-05-19 07:37

Fix Version/s		10.3.35 [ 27512 ]
Fix Version/s		10.4.25 [ 27510 ]
Fix Version/s		10.5.16 [ 27508 ]
Fix Version/s		10.6.8 [ 27506 ]
Fix Version/s		10.7.4 [ 27504 ]
Fix Version/s		10.8.3 [ 27502 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Daniel Bartholomew made changes - 2022-05-24 12:49

Fix Version/s		10.3.36 [ 27513 ]
Fix Version/s		10.4.26 [ 27511 ]
Fix Version/s		10.5.17 [ 27509 ]
Fix Version/s		10.6.9 [ 27507 ]
Fix Version/s		10.7.5 [ 27505 ]
Fix Version/s		10.8.4 [ 27503 ]
Fix Version/s	10.8.3 [ 27502 ]
Fix Version/s	10.7.4 [ 27504 ]
Fix Version/s	10.6.8 [ 27506 ]
Fix Version/s	10.5.16 [ 27508 ]
Fix Version/s	10.4.25 [ 27510 ]
Fix Version/s	10.3.35 [ 27512 ]

People

Assignee:: Oleg Smirnov

Reporter:: Sven Heidrich

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2022-04-06 07:31

Updated:: 2022-05-24 12:49

Resolved:: 2022-05-19 07:37

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration