[MDEV-26256] Bad plan for WHERE customer_id = @cid ORDER BY id LIMIT 10 with compound PK. Created: 2021-07-27  Updated: 2021-10-30

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.5.11, 10.6.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Jean-François Gagné Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: order-by-optimization


 Description   

Hi,

this is to report that MariaDB 10.5.11 and 10.6.3 are affected by upstream Bug#104421 [1]. If you want, I could also try to reproduce with other versions of MariaDB (like 10.4-latest) if useful to you.

[1]: https://bugs.mysql.com/bug.php?id=104421

Description from upstream:

I am getting a bad query plan for a query SELECT ... WHERE customer_id = @cid ORDER BY id LIMIT 10. I would expect the index on (customer_id, id) to be chosen for serving this query, but the optimiser chooses the index on (id). Some more informations: id is an auto-increment and the primary key is compound on (customer_id, another_column, id). See How to repeat for details.

My guess for explaining the the bad plan is a problem with the estimate on rows examined. I would expect the estimate to be 10 (because of the matching index and LIMIT), but I am getting a much larger value in EXPLAIN. See How to repeat for details.

Also about the bad estimate on rows examined, I get the expected estimate for a query ORDER BY id LIMIT 10 without a WHERE clause, but I am getting a bad estimate when adding WHERE id > @id. I am mentioning this because I think this might be related to this bug. See How to repeat for details.

When the PRIMARY KEY is on id (with an index on customer_id), I am not getting a bad plan, but still getting a bad estimate for rows examined. See How to repeat for details.

Suggested fix from upstream:

As presented in Description and shown in How to repeat, I think the problem is the bad estimate on rows examined. I would expect this estimate to be only 10 because of the matching index and LIMIT, but I am getting a much larger value. If this was fixed, I think we would be getting a much saner plan.

Also note that as presented in Description and shown in How to repeat, I am getting the right estimate when I do ORDER BY id LIMIT 10 without a WHERE clause, but I am getting a bad estimate when adding WHERE id > @id, so this also needs attention (maybe related to this bug, I do not know).

Finally, as adding a WHERE id > @id breaks the estimate from paragraph above, I would also expect that adding AND id > @id to my initial query with the bad plan to also break the estimate, so please make sure to also take this case into account in the fix.

How to repeat see code below.

Many thanks for looking into this,

Jean-François Gagné

Reproduction for MariaDB 10.5.11.
 
 
 
# Initialise a sandbox.
 
dbdeployer deploy single mariadb_10_5_11
 
 
 
# Load a dump similar to what is in prod (without indexes for fast load).
 
 
 
# Create indexes and add auto-increment.
 
alter table p add index(id), modify column id bigint unsigned NOT NULL AUTO_INCREMENT, add index(customer_id, id);
 
 
 
# Table size.
 
-rw-rw---- 1 xxx xxx 15G Jul 26 21:37 /home/jgagne/sandboxes/msb_mariadb_10_5_11/data/test_jfg/p.ibd
 
 
 
# Show the table structure.
 
mysql [localhost:10511] {msandbox} (test_jfg) > show create table p\G
 
*************************** 1. row ***************************
 
       Table: p
 
Create Table: CREATE TABLE `p` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `customer_id` int(10) unsigned NOT NULL,
 
  `object_id` bigint(20) unsigned NOT NULL,
 
  `type_id` tinyint(3) unsigned NOT NULL,
 
  `details` text DEFAULT NULL,
 
  PRIMARY KEY (`customer_id`,`object_id`,`id`),
 
  KEY `id` (`id`),
 
  KEY `customer_id` (`customer_id`,`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=344801716 DEFAULT CHARSET=utf8mb4
 
1 row in set (0.000 sec)
 
 
 
# The plan for the query is using the wrong index.
 
mysql [localhost:10511] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: index
 
possible_keys: PRIMARY,customer_id
 
          key: id
 
      key_len: 8
 
          ref: NULL
 
         rows: 721
 
        Extra: Using where
 
1 row in set (0.002 sec)
 
 
 
# And the query is horribly slow (interrupted after 10 seconds and scanned a lot of rows).
 
mysql [localhost:10511] {msandbox} (test_jfg) > pager cat > /dev/null; set max_statement_time = 10; flush local status; SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10; pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
 
Default pager wasn't set, using stdout.
 
+--------------------------+---------+
 
| Variable_name            | Value   |
 
+--------------------------+---------+
 
| Handler_read_first       | 1       |
 
| Handler_read_key         | 0       |
 
| Handler_read_last        | 0       |
 
| Handler_read_next        | 3093505 |
 
| Handler_read_prev        | 0       |
 
| Handler_read_retry       | 0       |
 
| Handler_read_rnd         | 0       |
 
| Handler_read_rnd_deleted | 0       |
 
| Handler_read_rnd_next    | 0       |
 
+--------------------------+---------+
 
9 rows in set (0.001 sec)
 
 
 
# The good plan, hinting an index, has a wrong estimate for rows(I would expect 10).
 
mysql [localhost:10511] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: ref
 
possible_keys: customer_id
 
          key: customer_id
 
      key_len: 4
 
          ref: const
 
         rows: 1281340
 
        Extra: Using where
 
1 row in set (0.001 sec)
 
 
 
# And executing the good plan, hinting an index, is fast and only examining 10 rows (as expected).
 
mysql [localhost:10511] {msandbox} (test_jfg) > pager cat > /dev/null; set max_statement_time = 10; flush local status; SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10 rows in set (0.000 sec)
 
 
 
Default pager wasn't set, using stdout.
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 1     |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 9     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_retry       | 0     |
 
| Handler_read_rnd         | 0     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 0     |
 
+--------------------------+-------+
 
9 rows in set (0.000 sec)
 
 
 
# I would expect the good plan to have rows like this.
 
mysql [localhost:10511] {msandbox} (test_jfg) > explain SELECT id FROM p ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: index
 
possible_keys: NULL
 
          key: id
 
      key_len: 8
 
          ref: NULL
 
         rows: 10
 
        Extra: Using index
 
1 row in set (0.000 sec)
 
 
 
# When I add WHERE id > @id to the query with a good estimate, the estimate becomes wrong even though the query is fast.
 
mysql [localhost:10511] {msandbox} (test_jfg) > explain SELECT id FROM p WHERE id > @id ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: range
 
possible_keys: id
 
          key: id
 
      key_len: 8
 
          ref: NULL
 
         rows: 49962859
 
        Extra: Using where; Using index
 
1 row in set (0.001 sec)
 
 
 
mysql [localhost:10511] {msandbox} (test_jfg) > pager cat > /dev/null; flush local status; SELECT id FROM p WHERE id > @id ORDER BY id LIMIT 10; pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10 rows in set (0.000 sec)
 
 
 
Default pager wasn't set, using stdout.
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 1     |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 9     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_retry       | 0     |
 
| Handler_read_rnd         | 0     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 0     |
 
+--------------------------+-------+
 
9 rows in set (0.000 sec)
 
 
 
# Showing it has something to do with the compound PK with still a bad estimate even with the good plan.
 
mysql [localhost:10511] {msandbox} (test_jfg) > alter table p drop primary key, add primary key (id), drop index id, drop index customer_id, add index(customer_id);
 
Query OK, 0 rows affected (19 min 42.987 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
mysql [localhost:10511] {msandbox} (test_jfg) > show create table p\G
 
*************************** 1. row ***************************
 
       Table: p
 
Create Table: CREATE TABLE `p` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `customer_id` int(10) unsigned NOT NULL,
 
  `object_id` bigint(20) unsigned NOT NULL,
 
  `type_id` tinyint(3) unsigned NOT NULL,
 
  `details` text DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `customer_id` (`customer_id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=344801716 DEFAULT CHARSET=utf8mb4
 
1 row in set (0.000 sec)
 
 
 
mysql [localhost:10511] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: ref
 
possible_keys: customer_id
 
          key: customer_id
 
      key_len: 4
 
          ref: const
 
         rows: 1366822
 
        Extra: Using where
 
1 row in set (0.001 sec)
 
 
 
mysql [localhost:10511] {msandbox} (test_jfg) > pager cat > /dev/null; set max_statement_time = 10; flush local status; SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10; pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10 rows in set (0.000 sec)
 
 
 
Default pager wasn't set, using stdout.
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 1     |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 9     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_retry       | 0     |
 
| Handler_read_rnd         | 0     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 0     |
 
+--------------------------+-------+
 
9 rows in set (0.000 sec)
 
 
 
---
 
 
 
Reproduction for MariaDB 10.6.3.
 
 
 
# Initialise a sandbox.
 
dbdeployer deploy single mariadb_10_6_3
 
 
 
# Load a dump similar to what is in prod (without indexes for fast load).
 
 
 
# Create indexes and add auto-increment.
 
alter table p add index(id), modify column id bigint unsigned NOT NULL AUTO_INCREMENT, add index(customer_id, id);
 
 
 
# Table size.
 
-rw-rw---- 1 xxx xxx 15G Jul 26 23:54 /home/jgagne/sandboxes/msb_mariadb_10_6_3/data/test_jfg/p.ibd
 
 
 
# Show the table structure.
 
mysql [localhost:10603] {msandbox} (test_jfg) > show create table p\G
 
*************************** 1. row ***************************
 
       Table: p
 
Create Table: CREATE TABLE `p` (
 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
  `customer_id` int(10) unsigned NOT NULL,
 
  `object_id` bigint(20) unsigned NOT NULL,
 
  `type_id` tinyint(3) unsigned NOT NULL,
 
  `details` text DEFAULT NULL,
 
  PRIMARY KEY (`customer_id`,`object_id`,`id`),
 
  KEY `id` (`id`),
 
  KEY `customer_id` (`customer_id`,`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=344801716 DEFAULT CHARSET=utf8mb4
 
1 row in set (0.000 sec)
 
 
 
# The plan for the query is using the wrong index.
 
mysql [localhost:10603] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: index
 
possible_keys: PRIMARY,customer_id
 
          key: id
 
      key_len: 8
 
          ref: NULL
 
         rows: 782
 
        Extra: Using where
 
1 row in set (0.019 sec)
 
 
 
# And the query is horribly slow (interrupted after 10 seconds and scanned a lot of rows).
 
mysql [localhost:10603] {msandbox} (test_jfg) > pager cat > /dev/null; set max_statement_time = 10; flush local status; SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10; pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
 
Default pager wasn't set, using stdout.
 
+--------------------------+---------+
 
| Variable_name            | Value   |
 
+--------------------------+---------+
 
| Handler_read_first       | 1       |
 
| Handler_read_key         | 0       |
 
| Handler_read_last        | 0       |
 
| Handler_read_next        | 1358440 |
 
| Handler_read_prev        | 0       |
 
| Handler_read_retry       | 0       |
 
| Handler_read_rnd         | 0       |
 
| Handler_read_rnd_deleted | 0       |
 
| Handler_read_rnd_next    | 0       |
 
+--------------------------+---------+
 
9 rows in set (0.001 sec)
 
 
 
# The good plan, hinting an index, has a wrong estimate for rows(I would expect 10).
 
mysql [localhost:10603] {msandbox} (test_jfg) > explain SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: ref
 
possible_keys: customer_id
 
          key: customer_id
 
      key_len: 4
 
          ref: const
 
         rows: 1281340
 
        Extra: Using where
 
1 row in set (0.000 sec)
 
 
 
# And executing the good plan, hinting an index, is fast and only examining 10 rows (as expected).
 
mysql [localhost:10603] {msandbox} (test_jfg) > pager cat > /dev/null; set max_statement_time = 10; flush local status; SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10 rows in set (0.006 sec)
 
 
 
Default pager wasn't set, using stdout.
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 1     |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 9     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_retry       | 0     |
 
| Handler_read_rnd         | 0     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 0     |
 
+--------------------------+-------+
 
9 rows in set (0.001 sec)
 
 
 
# I would expect the good plan to have rows like this.
 
mysql [localhost:10603] {msandbox} (test_jfg) > explain SELECT id FROM p ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: index
 
possible_keys: NULL
 
          key: id
 
      key_len: 8
 
          ref: NULL
 
         rows: 10
 
        Extra: Using index
 
1 row in set (0.000 sec)
 
 
 
# When I add WHERE id > @id to the query with a good estimate, the estimate becomes wrong even though the query is fast.
 
mysql [localhost:10603] {msandbox} (test_jfg) > explain SELECT id FROM p WHERE id > @id ORDER BY id LIMIT 10\G
 
*************************** 1. row ***************************
 
           id: 1
 
  select_type: SIMPLE
 
        table: p
 
         type: range
 
possible_keys: id
 
          key: id
 
      key_len: 8
 
          ref: NULL
 
         rows: 54197812
 
        Extra: Using where; Using index
 
1 row in set (0.015 sec)
 
 
 
mysql [localhost:10603] {msandbox} (test_jfg) > pager cat > /dev/null; flush local status; SELECT id FROM p WHERE id > @id ORDER BY id LIMIT 10; pager; show status like "Handler_read%";
 
PAGER set to 'cat > /dev/null'
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
10 rows in set (0.000 sec)
 
 
 
Default pager wasn't set, using stdout.
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| Handler_read_first       | 0     |
 
| Handler_read_key         | 1     |
 
| Handler_read_last        | 0     |
 
| Handler_read_next        | 9     |
 
| Handler_read_prev        | 0     |
 
| Handler_read_retry       | 0     |
 
| Handler_read_rnd         | 0     |
 
| Handler_read_rnd_deleted | 0     |
 
| Handler_read_rnd_next    | 0     |
 
+--------------------------+-------+
 
9 rows in set (0.000 sec)



 Comments   
Comment by Rick James [ 2021-10-30 ]

I have long suspected two things, but have not stopped to "prove". Perhaps looking at the Optimizer with these in mind might shed some light.

  • An @variable is not treated as a constant, but instead something harder to optimize. That is `WHERE customer_id = @cid` yields a different query plan than `WHERE customer_id = 123`, at least in some situations.
  • `key(id)` was picked because the index is smaller and helps with the ORDER BY.
Comment by Jean-François Gagné [ 2021-10-30 ]

Hi Rick, thanks for your interest in this bug.

There is a full reproduction in the upstream bug, without using @variables:
https://bugs.mysql.com/bug.php?id=104421

I am not sure this allows to reproduce with MariaDB, someone will have to test.

Cheers, Jean-François

Generated at Thu Feb 08 09:43:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.