[#MDEV-31583] Index Not Utilized by Query Optimizer Leads to Full Table Scan; Simple Value Swap Fixes Execution Plan

[MDEV-31583] Index Not Utilized by Query Optimizer Leads to Full Table Scan; Simple Value Swap Fixes Execution Plan Created: 2023-06-29 Updated: 2023-07-09
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.4.12, 10.6.2, 10.6.7, 10.4.30, 10.6, 10.9, 10.5.21, 10.6.14, 10.9.7, 10.11.4
Fix Version/s:	10.4, 10.6, 10.11

Type:

Bug

Priority:

Major

Reporter:

Daniel

Assignee:

Igor Babaev

Resolution:

Unresolved

Votes:

Labels:

index, not-10.3, not-11.0, not-5.5, optimizer, regression-10.3

Environment:

Debian 11, mariadb:10 Docker container

Attachments:

test.sql

Issue Links:

Relates
relates to	MDEV-30566	Performance Degraded After Migration ...	Open

Description

I noticed a significant slowdown in a specific query following the update to MariaDB 10.6.12 (unaware of the previous version). It appears that instead of utilizing an index, the query now scans the entire table, leading to the decreased performance:

MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed');

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

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

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

|    1 | SIMPLE      | t      | index         | NULL           | PRIMARY        | 4       | NULL      | 44100  | Using index                                     |

|    1 | SIMPLE      | open   | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1     | test.t.id | 1 (0%) | Using where; Using rowid filter                 |

|    1 | SIMPLE      | closed | ALL           | PRIMARY,status | NULL           | NULL    | NULL      | 44100  | Using where; Using join buffer (flat, BNL join) |

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

3 rows in set (0.001 sec)

However, when I simply switch around 'open' and 'closed' the issue goes away:

MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='closed') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='open');

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

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

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

|    1 | SIMPLE      | t      | index         | NULL           | PRIMARY        | 4       | NULL      | 44100  | Using index                     |

|    1 | SIMPLE      | open   | eq_ref        | PRIMARY,status | PRIMARY        | 4       | test.t.id | 1      | Using where                     |

|    1 | SIMPLE      | closed | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1     | test.t.id | 1 (0%) | Using where; Using rowid filter |

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

3 rows in set (0.001 sec)

I checked the 10.6.14, 10.9, and 10 docker containers, and they all have this problem. However, it appears to be fixed in version 11/latest.

To perform a basic test, please refer to the queries mentioned above. I have included a table dump for your reference.

There also appears to be a specific threshold for this issue. In my actual data, it only occurred after approximately 40,000 entries in the table. However, in the minimal test table (where I removed all columns except for "status"), the issue appeared after 97 entries. Perhaps it is somehow related to the overall size of the table:

MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed');

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

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

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

|    1 | SIMPLE      | t      | index         | NULL           | PRIMARY        | 4       | NULL      | 97     | Using index                                     |

|    1 | SIMPLE      | open   | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1     | test.t.id | 1 (1%) | Using where; Using rowid filter                 |

|    1 | SIMPLE      | closed | ALL           | PRIMARY,status | NULL           | NULL    | NULL      | 97     | Using where; Using join buffer (flat, BNL join) |

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

3 rows in set (0.001 sec)

MariaDB [test]> delete from test limit 1;

Query OK, 1 row affected (0.002 sec)

MariaDB [test]> explain SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed');

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

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

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

|    1 | SIMPLE      | t      | index         | NULL           | PRIMARY        | 4       | NULL      | 96     | Using index                     |

|    1 | SIMPLE      | open   | eq_ref|filter | PRIMARY,status | PRIMARY|status | 4|1     | test.t.id | 1 (1%) | Using where; Using rowid filter |

|    1 | SIMPLE      | closed | eq_ref        | PRIMARY,status | PRIMARY        | 4       | test.t.id | 1      | Using where                     |

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

3 rows in set (0.001 sec)

Comments

Comment by Daniel Black [ 2023-06-30 ]

thanks for the test case.

$ podman exec -t  test  mariadb -u test -ptest test  -e "select version();analyze format=json SELECT count(open.id) as open, count(closed.id) as closed FROM test t LEFT JOIN test open ON (open.id=t.id AND open.status='open') LEFT JOIN test closed ON (closed.id=t.id AND closed.status='closed')\\G"

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

| version()                             |

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

| 11.0.2-MariaDB-1:11.0.2+maria~ubu2204 |

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

*************************** 1. row ***************************

ANALYZE: {

  "query_optimization": {

    "r_total_time_ms": 0.120357252

},

  "query_block": {

    "select_id": 1,

    "cost": 34.96745253,

    "r_loops": 1,

    "r_total_time_ms": 50.61856297,

    "const_condition": "1",

    "nested_loop": [

        "table": {

          "table_name": "t",

          "access_type": "index",

          "key": "PRIMARY",

          "key_length": "4",

          "used_key_parts": ["id"],

          "loops": 1,

          "r_loops": 1,

          "rows": 44100,

          "r_rows": 44100,

          "cost": 6.141240342,

          "r_table_time_ms": 2.271114788,

          "r_other_time_ms": 1.784361469,

          "filtered": 100,

          "r_filtered": 100,

          "using_index": true

},

        "table": {

          "table_name": "open",

          "access_type": "eq_ref",

          "possible_keys": ["PRIMARY", "status"],

          "key": "PRIMARY",

          "key_length": "4",

          "used_key_parts": ["id"],

          "ref": ["test.t.id"],

          "rowid_filter": {

            "range": {

              "key": "status",

              "used_key_parts": ["status"]

},

            "rows": 3,

            "selectivity_pct": 0.006802721,

            "r_rows": 3,

            "r_lookups": 44100,

            "r_selectivity_pct": 0.006802721,

            "r_buffer_size": 2,

            "r_filling_time_ms": 0.00300845

},

          "loops": 44100,

          "r_loops": 44100,

          "rows": 1,

          "r_rows": 6.802721e-5,

          "cost": 28.82041071,

          "r_table_time_ms": 16.61129226,

          "r_other_time_ms": 1.967738371,

          "filtered": 0.006802721,

          "r_filtered": 100,

          "attached_condition": "trigcond(`open`.`status` = 'open')"

},

        "table": {

          "table_name": "closed",

          "access_type": "eq_ref",

          "possible_keys": ["PRIMARY", "status"],

          "key": "PRIMARY",

          "key_length": "4",

          "used_key_parts": ["id"],

          "ref": ["test.t.id"],

          "loops": 3,

          "r_loops": 44100,

          "rows": 1,

          "r_rows": 1,

          "cost": 0.005801478,

          "r_table_time_ms": 23.55952462,

          "r_other_time_ms": 4.42011174,

          "filtered": 99.97052002,

          "r_filtered": 99.99319728,

          "attached_condition": "trigcond(closed.`status` = 'closed')"

10.6.14
+-----------------------------------------+
\| version() \|
+-----------------------------------------+
\| 10.6.14-MariaDB-1:10.6.14+maria~ubu2004 \|
+-----------------------------------------+
************************* 1. row *************************
ANALYZE: {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 78908.18444,
"const_condition": "1",
"table": {
"table_name": "t",
"access_type": "index",
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"r_loops": 1,
"rows": 44100,
"r_rows": 44100,
"r_table_time_ms": 2.580078919,
"r_other_time_ms": 2.097937098,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"table": {
"table_name": "open",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "status"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["test.t.id"],
"rowid_filter": {
"range": {
"key": "status",
"used_key_parts": ["status"]
},
"rows": 3,
"selectivity_pct": 0.006802721,
"r_rows": 3,
"r_lookups": 44100,
"r_selectivity_pct": 0.006802721,
"r_buffer_size": 2,
"r_filling_time_ms": 0.002808746
},
"r_loops": 44100,
"rows": 1,
"r_rows": 6.802721e-5,
"r_table_time_ms": 19.31780521,
"r_other_time_ms": 2.676132408,
"filtered": 0.006802721,
"r_filtered": 100,
"attached_condition": "trigcond(`open`.`status` = 'open')"
},
"block-nl-join": {
"table": {
"table_name": "closed",
"access_type": "ALL",
"possible_keys": ["PRIMARY", "status"],
"r_loops": 2,
"rows": 44100,
"r_rows": 44100,
"r_table_time_ms": 14.6934785,
"r_other_time_ms": 4.41740098,
"filtered": 99.97052002,
"r_filtered": 99.99319728,
"attached_condition": "closed.`status` = 'closed'"
},
"buffer_type": "flat",
"buffer_size": "256Kb",
"join_type": "BNL",
"attached_condition": "trigcond(closed.`id` = t.`id` and closed.`status` = 'closed')",
"r_loops": 44100,
"r_filtered": 0.002267728,
"r_unpack_time_ms": 35048.6041,
"r_other_time_ms": 43813.79331,
"r_effective_rows": 44097
}
}
}

Also tested affected versions.

10.3/5.5 is quick:

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

| version()                               |

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

| 10.3.39-MariaDB-1:10.3.39+maria~ubu2004 |

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

*************************** 1. row ***************************

EXPLAIN: {

  "query_block": {

    "select_id": 1,

    "const_condition": "1",

    "table": {

      "table_name": "t",

      "access_type": "index",

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "rows": 44100,

      "filtered": 100,

      "using_index": true

},

    "table": {

      "table_name": "open",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY", "status"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "ref": ["test.t.id"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "trigcond(`open`.`status` = 'open')"

},

    "table": {

      "table_name": "closed",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY", "status"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "ref": ["test.t.id"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "trigcond(closed.`status` = 'closed')"

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

[MDEV-31583] Index Not Utilized by Query Optimizer Leads to Full Table Scan; Simple Value Swap Fixes Execution Plan Created: 2023-06-29 Updated: 2023-07-09