[MDEV-29442] Optimizer bug Created: 2022-09-01  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.26, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Vova Moroz Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: not-11.0
Environment:

CentOS 7

Attachments: File MDEV-29442.sql    
Issue Links:
Relates
relates to MDEV-29538 Querry latency on some version of mar... Closed

 Description   

After upgrade on 10.4.26 after changing joined tables in WHERE clause the plan getting worse: 

SELECT
 
   subscriptionId, string, stringId, userId, webId, cron, lastAutoId
 
  FROM subscriptions.send se
 
  JOIN subscriptions.subscriptions  s USING(subscriptionId)
 
  JOIN subscriptions.profiles USING(profileId)
 
  JOIN subscriptions.strings USING(stringId)
 
  JOIN subscriptions.schedule USING(scheduleId)
 
  WHERE next <= NOW()
 
    AND se.subscriptionId IN (13304884, 4996516, 422541, 6809130, 7508523, 11254813, 11428600, 13272640, 83632, 13211679, 10155318, 10445013, 9405478, 9031280, 10738519, 13090088, 12006096, 11774593, 5770179, 9977691, 5053234, 13024588, 6246532, 9801612, 8930459, 9484062, 9951533, 3284553, 10883216, 4360798, 8654256, 3070161, 13262056, 8277648, 3673463, 11344523, 7924664, 9705242, 11522204, 8594145, 5431666, 11872542, 12514647, 11898128, 10435159, 8988977, 11392579, 13212233, 11352298, 11925584, 9763763, 10474257, 3057850, 12743391, 12195993, 8842699, 7919760, 729582, 12687723, 7942806, 8956429, 13221336, 13203976, 11212117, 12622147, 4091809, 11601268, 11958971, 11732948, 9935172, 11857650, 11550494, 13363744, 6282972, 10791765, 11669239, 8100300, 6231592, 4376705, 12076875, 9369980, 4280110, 13277244, 10747976, 11563803, 8666015, 12129569, 225808, 10886926, 10599569, 6357250, 9192050, 952478, 11696656, 11013805, 10808603, 8952390, 11796269, 2536646)
 
    AND platform != 1

has the plan below:

1,SIMPLE,profiles,ALL,PRIMARY,NULL,NULL,NULL,2930162,100,
 
1,SIMPLE,subscriptions,ref,"PRIMARY,profiles_ibfk_sub,schedule_ibfk_sub,strings_ibfk_sub",profiles_ibfk_sub,4,subscriptions.profiles.profileId,1,100,Using where
 
1,SIMPLE,schedule,eq_ref,PRIMARY,PRIMARY,4,subscriptions.subscriptions.scheduleId,1,100,
 
1,SIMPLE,send,eq_ref,"PRIMARY,next_idx",PRIMARY,4,subscriptions.subscriptions.subscriptionId,1,100,Using where
 
1,SIMPLE,strings,eq_ref,PRIMARY,PRIMARY,8,subscriptions.subscriptions.stringId,1,100

The proper plan:

SELECT
 
   subscriptionId, string, stringId, userId, webId, cron, lastAutoId
 
  FROM subscriptions.send se
 
  JOIN subscriptions.subscriptions s USING(subscriptionId)
 
  JOIN subscriptions.profiles USING(profileId)
 
  JOIN subscriptions.strings USING(stringId)
 
  JOIN subscriptions.schedule USING(scheduleId)
 
  WHERE next <= NOW()
 
    AND s.subscriptionId IN (13304884, 4996516, 422541, 6809130, 7508523, 11254813, 11428600, 13272640, 83632, 13211679, 10155318, 10445013, 9405478, 9031280, 10738519, 13090088, 12006096, 11774593, 5770179, 9977691, 5053234, 13024588, 6246532, 9801612, 8930459, 9484062, 9951533, 3284553, 10883216, 4360798, 8654256, 3070161, 13262056, 8277648, 3673463, 11344523, 7924664, 9705242, 11522204, 8594145, 5431666, 11872542, 12514647, 11898128, 10435159, 8988977, 11392579, 13212233, 11352298, 11925584, 9763763, 10474257, 3057850, 12743391, 12195993, 8842699, 7919760, 729582, 12687723, 7942806, 8956429, 13221336, 13203976, 11212117, 12622147, 4091809, 11601268, 11958971, 11732948, 9935172, 11857650, 11550494, 13363744, 6282972, 10791765, 11669239, 8100300, 6231592, 4376705, 12076875, 9369980, 4280110, 13277244, 10747976, 11563803, 8666015, 12129569, 225808, 10886926, 10599569, 6357250, 9192050, 952478, 11696656, 11013805, 10808603, 8952390, 11796269, 2536646)
 
    AND platform != 1


1,SIMPLE,s,range,"PRIMARY,profiles_ibfk_sub,schedule_ibfk_sub,strings_ibfk_sub",PRIMARY,4,NULL,99,100,Using where
 
1,SIMPLE,schedule,eq_ref,PRIMARY,PRIMARY,4,subscriptions.s.scheduleId,1,100,
 
1,SIMPLE,send,eq_ref,"PRIMARY,next_idx",PRIMARY,4,subscriptions.s.subscriptionId,1,50,Using where
 
1,SIMPLE,profiles,eq_ref,PRIMARY,PRIMARY,4,subscriptions.s.profileId,1,100,
 
1,SIMPLE,strings,eq_ref,PRIMARY,PRIMARY,8,subscriptions.s.stringId,1,100,

On previous version 10.3.28 it works with proper plan regardless the table given in WHERE clause

 Comments   
Comment by Alice Sherepa [ 2022-09-22 ]

Could you please provide a test case to demonstrate the problem? Or at least table definitions, so we will try to reproduce it on our side.

Comment by Sergei Golubchik [ 2022-09-23 ]

This was sent per email (Moroz, please use web interface to post comments in the future):

CREATE database IF NOT EXISTS subscriptions;
 
 
 
DROP TABLE IF EXISTS subscriptions.send;
 
DROP TABLE IF EXISTS subscriptions.subscriptions;
 
DROP TABLE IF EXISTS subscriptions.profiles;
 
DROP TABLE IF EXISTS subscriptions.schedule;
 
DROP TABLE IF EXISTS subscriptions.strings;
 
 
 
USE subscriptions;
 
 
 
CREATE TABLE subscriptions.strings
 
(
 
    stringId BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
 
    hash     varchar(32)         NOT NULL COMMENT,
 
    string   varchar(2048)       NOT NULL COMMENT,
 
    PRIMARY KEY (stringId),
 
    UNIQUE KEY hash_idx (hash)
 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
 
 
 
CREATE TABLE IF NOT EXISTS subscriptions.profiles
 
(
 
    profileId    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
 
    oldProfileId INT(11) UNSIGNED DEFAULT 0,
 
    userId       INT(11) UNSIGNED NOT NULL COMMENT 'Идентификатор пользователя' DEFAULT 0,
 
    webId        INT(11) UNSIGNED NOT NULL DEFAULT 0,
 
    email        VARCHAR(50)      NOT NULL DEFAULT '',
 
    active       TINYINT(1)       NOT NULL DEFAULT 0,
 
    createDate   TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP(),
 
    PRIMARY KEY (profileId),
 
    KEY userId_idx (userId),
 
    KEY webId_idx (webId),
 
    KEY oldProfileId (oldProfileId)
 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
 
 
 
CREATE TABLE IF NOT EXISTS subscriptions.schedule
 
(
 
    scheduleId INT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
 
    cron       varchar(16)     NOT NULL DEFAULT '0 0 7 * * *',
 
    zone       varchar(5)      NOT NULL DEFAULT '+0200',
 
    PRIMARY KEY (scheduleId)
 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
 
 
 
CREATE TABLE IF NOT EXISTS subscriptions.subscriptions
 
(
 
    subscriptionId INT(11) UNSIGNED    NOT NULL AUTO_INCREMENT,
 
    scheduleId     INT(3) UNSIGNED     NOT NULL COMMENT,
 
    profileId      INT(11) UNSIGNED    NOT NULL COMMENT,
 
    stringId       BIGINT(20) UNSIGNED NOT NULL COMMENT,
 
    active         TINYINT(3)          NOT NULL DEFAULT 1,
 
    createDate     TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP(),
 
    platform       TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
 
    lastAutoId     INT(11) UNSIGNED    NOT NULL DEFAULT 0,
 
    sendDate       TIMESTAMP,
 
    PRIMARY KEY (subscriptionId),
 
    CONSTRAINT profiles_ibfk_sub FOREIGN KEY (profileId) REFERENCES profiles (profileId),
 
    CONSTRAINT schedule_ibfk_sub FOREIGN KEY (scheduleId) REFERENCES schedule (scheduleId),
 
    CONSTRAINT strings_ibfk_sub FOREIGN KEY (stringId) REFERENCES strings (stringId)
 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
 
 
 
CREATE TABLE IF NOT EXISTS subscriptions.send
 
(
 
    subscriptionId INT(11) UNSIGNED NOT NULL,
 
    next           DATETIME         NOT NULL,
 
    prev           DATETIME DEFAULT NULL,
 
    PRIMARY KEY (subscriptionId),
 
    KEY next_idx (next),
 
    CONSTRAINT send_ibfk_sub FOREIGN KEY (subscriptionId) REFERENCES subscriptions (subscriptionId)
 
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

Cardinality:

subscriptions,profiles,3239376
 
subscriptions,schedule,19
 
subscriptions,send,4756003
 
subscriptions,strings,12383867
 
subscriptions,subscriptions,11103984

Comment by Alice Sherepa [ 2022-09-30 ]

Thank you! I repeated as described, the change somewhere in 10.3.29. (test MDEV-29442.sql)

10.3.28

 
Empty set (0.003 sec)
 
 
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
| id   | select_type | table    | type   | possible_keys                         | key     | key_len | ref                      | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | send     | range  | PRIMARY,next_idx,subscriptionId       | PRIMARY | 4       | NULL                     |   10 |  10.00 |   100.00 |     100.00 | Using where |
 
|    1 | SIMPLE      | s        | eq_ref | PRIMARY,profileId,scheduleId,stringId | PRIMARY | 4       | test.send.subscriptionId |    1 |   1.00 |   100.00 |       0.00 | Using where |
 
|    1 | SIMPLE      | profiles | eq_ref | PRIMARY                               | PRIMARY | 4       | test.s.profileId         |    1 |   NULL |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | strings  | eq_ref | PRIMARY                               | PRIMARY | 8       | test.s.stringId          |    1 |   NULL |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | schedule | eq_ref | PRIMARY                               | PRIMARY | 4       | test.s.scheduleId        |    1 |   NULL |   100.00 |       NULL |             |
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
5 rows in set (0.001 sec)

10.3.29

 
Empty set (0,034 sec)
 
 
 
+------+-------------+----------+--------+---------------------------------------+-----------+---------+-------------------------+-------+----------+----------+------------+-------------+
 
| id   | select_type | table    | type   | possible_keys                         | key       | key_len | ref                     | rows  | r_rows   | filtered | r_filtered | Extra       |
 
+------+-------------+----------+--------+---------------------------------------+-----------+---------+-------------------------+-------+----------+----------+------------+-------------+
 
|    1 | SIMPLE      | profiles | ALL    | PRIMARY                               | NULL      | NULL    | NULL                    | 10088 | 10000.00 |   100.00 |     100.00 |             |
 
|    1 | SIMPLE      | s        | ref    | PRIMARY,profileId,scheduleId,stringId | profileId | 4       | test.profiles.profileId | 1     | 1.00     |   100.00 |       0.00 | Using where |
 
|    1 | SIMPLE      | send     | eq_ref | PRIMARY,next_idx,subscriptionId       | PRIMARY   | 4       | test.s.subscriptionId   | 1     | NULL     |   100.00 |       NULL | Using where |
 
|    1 | SIMPLE      | strings  | eq_ref | PRIMARY                               | PRIMARY   | 8       | test.s.stringId         | 1     | NULL     |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | schedule | eq_ref | PRIMARY                               | PRIMARY   | 4       | test.s.scheduleId       | 1     | NULL     |   100.00 |       NULL |             |
 
+------+-------------+----------+--------+---------------------------------------+-----------+---------+-------------------------+-------+----------+----------+------------+-------------+
 
5 rows in set (0,022 sec)

on current 10.3 (f65ba9aeb7eea75656c74da7c2):

+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
| id   | select_type | table    | type   | possible_keys                         | key     | key_len | ref                      | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | send     | range  | PRIMARY,next_idx,subscriptionId       | PRIMARY | 4       | NULL                     |   10 |  10.00 |   100.00 |     100.00 | Using where |
 
|    1 | SIMPLE      | s        | eq_ref | PRIMARY,profileId,scheduleId,stringId | PRIMARY | 4       | test.send.subscriptionId |    1 |   1.00 |   100.00 |       0.00 | Using where |
 
|    1 | SIMPLE      | profiles | eq_ref | PRIMARY                               | PRIMARY | 4       | test.s.profileId         |    1 |   NULL |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | strings  | eq_ref | PRIMARY                               | PRIMARY | 8       | test.s.stringId          |    1 |   NULL |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | schedule | eq_ref | PRIMARY                               | PRIMARY | 4       | test.s.scheduleId        |    1 |   NULL |   100.00 |       NULL |             |
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
5 rows in set (0,018 sec)

but on 10.4-10.11:

+------+-------------+----------+--------+---------------------------------------+-----------+---------+-------------------------+-------+----------+----------+------------+-------------+
 
| id   | select_type | table    | type   | possible_keys                         | key       | key_len | ref                     | rows  | r_rows   | filtered | r_filtered | Extra       |
 
+------+-------------+----------+--------+---------------------------------------+-----------+---------+-------------------------+-------+----------+----------+------------+-------------+
 
|    1 | SIMPLE      | profiles | ALL    | PRIMARY                               | NULL      | NULL    | NULL                    | 10088 | 10000.00 |   100.00 |     100.00 |             |
 
|    1 | SIMPLE      | s        | ref    | PRIMARY,profileId,scheduleId,stringId | profileId | 4       | test.profiles.profileId | 1     | 1.00     |   100.00 |       0.00 | Using where |
 
|    1 | SIMPLE      | send     | eq_ref | PRIMARY,next_idx,subscriptionId       | PRIMARY   | 4       | test.s.subscriptionId   | 1     | NULL     |   100.00 |       NULL | Using where |
 
|    1 | SIMPLE      | strings  | eq_ref | PRIMARY                               | PRIMARY   | 8       | test.s.stringId         | 1     | NULL     |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | schedule | eq_ref | PRIMARY                               | PRIMARY   | 4       | test.s.scheduleId       | 1     | NULL     |   100.00 |       NULL |             |
 
+------+-------------+----------+--------+---------------------------------------+-----------+---------+-------------------------+-------+----------+----------+------------+-------------+
 
5 rows in set (1,071 sec)

on 11.0 the plan is as expected:

MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 11.0.2-MariaDB |
 
+----------------+
 
1 row in set (0,000 sec)
 
MariaDB [test]> analyze SELECT
 
    ->    subscriptionId, string, stringId, userId, webId, cron, lastAutoId
 
    ->   FROM send 
    ->   JOIN subscriptions  s USING(subscriptionId)
 
    ->   JOIN profiles USING(profileId)
 
    ->   JOIN strings USING(stringId)
 
    ->   JOIN schedule USING(scheduleId)
 
    ->   WHERE next <= NOW()+1
 
    ->     AND send.subscriptionId IN (134, 499, 422, 680, 750, 112, 114, 132, 836, 132, 101)
 
    ->     AND platform != 1;
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
| id   | select_type | table    | type   | possible_keys                         | key     | key_len | ref                      | rows | r_rows | filtered | r_filtered | Extra       |
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
|    1 | SIMPLE      | send     | range  | PRIMARY,next_idx,subscriptionId       | PRIMARY | 4       | NULL                     | 10   | 10.00  |   100.00 |     100.00 | Using where |
 
|    1 | SIMPLE      | s        | eq_ref | PRIMARY,profileId,scheduleId,stringId | PRIMARY | 4       | test.send.subscriptionId | 1    | 1.00   |   100.00 |       0.00 | Using where |
 
|    1 | SIMPLE      | profiles | eq_ref | PRIMARY                               | PRIMARY | 4       | test.s.profileId         | 1    | NULL   |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | strings  | eq_ref | PRIMARY                               | PRIMARY | 8       | test.s.stringId          | 1    | NULL   |   100.00 |       NULL |             |
 
|    1 | SIMPLE      | schedule | eq_ref | PRIMARY                               | PRIMARY | 4       | test.s.scheduleId        | 1    | NULL   |   100.00 |       NULL |             |
 
+------+-------------+----------+--------+---------------------------------------+---------+---------+--------------------------+------+--------+----------+------------+-------------+
 
5 rows in set (0,000 sec)

Generated at Thu Feb 08 10:08:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.