[MDEV-25480] Optimizer uses wrong index Created: 2021-04-21  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3.28, 10.4.18, 10.7
Fix Version/s: 10.4

Type: Bug Priority: Critical
Reporter: Kyle Joiner (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 7
Labels: None
Environment:

Centos 7

Attachments: File analyze-default.json     File analyze-straight-join.json     File dump.sql.tgz.encaa     File dump.sql.tgz.encab     File dump.sql.tgz.encac     File dump.sql.tgz.encad     File dump.sql.tgz.encae     File dump.sql.tgz.encaf     File dump.sql.tgz.encag     File dump.sql.tgz.encah     File dump.sql.tgz.encai    
Issue Links:
Relates
relates to MDEV-28246 Optimizer uses all partitions during ... Closed

 Description   

The optimizer chooses the wrong path for a query that should use the primary key:

MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
 
| id   | select_type | table | type | possible_keys         | key                   | key_len | ref            | rows  | Extra                           |
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
 
|    1 | SIMPLE      | d     | ALL  | PRIMARY               | NULL                  | NULL    | NULL           | 97470 | Using temporary; Using filesort |
 
|    1 | SIMPLE      | c     | ref  | cluster_fk_deployment | cluster_fk_deployment | 767     | cs0275577.d.id | 6     |                                 |
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
 
2 rows in set (0.001 sec)


MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1;
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
 
| id   | select_type | table | type   | possible_keys         | key     | key_len | ref                       | rows | Extra |
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
 
|    1 | SIMPLE      | c     | index  | cluster_fk_deployment | PRIMARY | 767     | NULL                      | 1    |       |
 
|    1 | SIMPLE      | d     | eq_ref | PRIMARY               | PRIMARY | 767     | cs0275577.c.deployment_id | 1    |       |
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
 
2 rows in set (0.000 sec)


 
 
MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| id                                   | dname      | id                                   | cname      | state   |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
1 row in set (25.254 sec)


 
 
MariaDB [cs0275577]> SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1;
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| id                                   | dname      | id                                   | cname      | state   |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
1 row in set (0.002 sec)

Covering indexes did not help the query.

 Comments   
Comment by Roel Van de Paar [ 2021-09-15 ]

Query outcomes verified as described, in 10.7. Note: I have not verified query execution/optimality.

10.7.0 1bc82aaf0a7746c0921a94034aff2d51f0d75cd0 (Debug)

 
10.7.0-dbg>explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
 
| id   | select_type | table | type | possible_keys         | key                   | key_len | ref            | rows  | Extra                           |
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
 
|    1 | SIMPLE      | d     | ALL  | PRIMARY               | NULL                  | NULL    | NULL           | 97470 | Using temporary; Using filesort |
 
|    1 | SIMPLE      | c     | ref  | cluster_fk_deployment | cluster_fk_deployment | 767     | cs0275577.d.id | 6     |                                 |
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+---------------------------------+
 
2 rows in set (0.008 sec)
 
 
 
10.7.0-dbg>explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1;
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
 
| id   | select_type | table | type   | possible_keys         | key     | key_len | ref                       | rows | Extra |
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
 
|    1 | SIMPLE      | c     | index  | cluster_fk_deployment | PRIMARY | 767     | NULL                      | 1    |       |
 
|    1 | SIMPLE      | d     | eq_ref | PRIMARY               | PRIMARY | 767     | cs0275577.c.deployment_id | 1    |       |
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+------+-------+
 
2 rows in set (0.001 sec)
 
 
 
10.7.0-dbg>SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| id                                   | dname      | id                                   | cname      | state   |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
1 row in set (9 min 37.029 sec)
 
 
 
10.7.0-dbg>SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id order by c.id limit 1;
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| id                                   | dname      | id                                   | cname      | state   |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
| 4f8cc10b-83ca-41d3-b5b4-9cc74e1292da | depl-73221 | 00000122-e6b5-499d-993f-8052a9272cf3 | cluster-14 | RUNNING |
 
+--------------------------------------+------------+--------------------------------------+------------+---------+
 
1 row in set (0.006 sec)

Comment by Larry Adams [ 2021-10-04 ]

Sergei,

I'm having a query optimizer issue as well. Just wondering if I should open a new ticket. System with the problems is 10.5.12. The system I compare to is an old MySQL 5.6 server.

The MySQL 5.6 server runs the query in about 9 seconds
The MariaDB 10.5.12 server runs the query in > 1100 seconds
The MariaDB 10.5.12 server Using the FORCE INDEX (blah) runs the query in ~30 seconds

It looks like the Query optimizer is picking the wrong indexes as compared to MySQL 5.6. I've found that for the query in question, but also many more UPDATE and SELECT type queries with both INNER and LEFT joins are operating poorly. I have one LEFT JOIN query that's totally broken and never finishes. It's really disturbing and not quite sure what to do.

Larry Adams

Comment by Roel Van de Paar [ 2021-10-06 ]

TheWitness It may help if you could add a generic testcase compiled from what you are seeing. Often the issue can be reduced to a few lines of SQL by repeated attempts with smaller data sets and SQL clause removal. Thank you.

Comment by Sergei Petrunia [ 2022-02-24 ]

Formatting the query 

SELECT 
  d.id, d.dname, c.id, c.cname, c.state 
FROM 
  deployment d 
  join host c on d.id=c.deployment_id
 
order by c.id limit 1;

The fast query plan uses join order of c,d. For c, it uses an index which produces ORDER BY-compatible ordering and allows to short-cut the execution as soon as #LIMITrows (i.e. one row) is produced.

The slow query plan uses join order d,c. Here, it has to enumerate the entire join output to get the limit.

This looks like something that would be solved by MDEV-8306.

Comment by Sergei Petrunia [ 2022-02-24 ]

ANALYZE FORMAT=JSON outputs on the latest 10.8.
analyze-straight-join.json analyze-default.json

Comment by Sergei Petrunia [ 2022-02-24 ]

Checking EXPLAIN costs without ORDER BY:

MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id;
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+-------+
 
| id   | select_type | table | type | possible_keys         | key                   | key_len | ref            | rows  | Extra |
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+-------+
 
|    1 | SIMPLE      | d     | ALL  | PRIMARY               | NULL                  | NULL    | NULL           | 97335 |       |
 
|    1 | SIMPLE      | c     | ref  | cluster_fk_deployment | cluster_fk_deployment | 767     | cs0275577.d.id | 7     |       |
 
+------+-------------+-------+------+-----------------------+-----------------------+---------+----------------+-------+-------+
 
Last_query_cost=947 913


MariaDB [cs0275577]> explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM host c straight_join deployment d on d.id=c.deployment_id;
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+---------+-------+
 
| id   | select_type | table | type   | possible_keys         | key     | key_len | ref                       | rows    | Extra |
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+---------+-------+
 
|    1 | SIMPLE      | c     | ALL    | cluster_fk_deployment | NULL    | NULL    | NULL                      | 1459293 |       |
 
|    1 | SIMPLE      | d     | eq_ref | PRIMARY               | PRIMARY | 767     | cs0275577.c.deployment_id | 1       |       |
 
+------+-------------+-------+--------+-----------------------+---------+---------+---------------------------+---------+-------+
 
Last_query_cost=2 057 474

d-c is 2x cheaper than c-d.

Comment by Sergei Petrunia [ 2022-02-24 ]

Trying on MySQL-8.0.28

explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
 
+----+-------------+-------+------------+--------+-----------------------+---------+---------+---------------------------+------+----------+-------+
 
| id | select_type | table | partitions | type   | possible_keys         | key     | key_len | ref                       | rows | filtered | Extra |
 
+----+-------------+-------+------------+--------+-----------------------+---------+---------+---------------------------+------+----------+-------+
 
|  1 | SIMPLE      | c     | NULL       | index  | cluster_fk_deployment | PRIMARY | 767     | NULL                      |    1 |   100.00 | NULL  |
 
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY               | PRIMARY | 767     | cs0275577.c.deployment_id |    1 |   100.00 | NULL  |
 
+----+-------------+-------+------------+--------+-----------------------+---------+---------+---------------------------+------+----------+-------+


explain analyze SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id order by c.id limit 1;
 
| -> Limit: 1 row(s)  (cost=355496.36 rows=1) (actual time=0.673..0.673 rows=1 loops=1)
 
    -> Nested loop inner join  (cost=355496.36 rows=1) (actual time=0.669..0.669 rows=1 loops=1)
 
        -> Index scan on c using PRIMARY  (cost=0.01 rows=1) (actual time=0.427..0.427 rows=1 loops=1)
 
        -> Single-row index lookup on d using PRIMARY (id=c.deployment_id)  (cost=0.25 rows=1) (actual time=0.233..0.233 rows=1 loops=1)

It uses the c-d join order which is fast.

Comment by Sergei Petrunia [ 2022-02-24 ]

... and in mysql-8.0, the c-d join order is 2x cheaper than d-c:

explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d join host c on d.id=c.deployment_id;
 
+----+-------------+-------+------------+--------+-----------------------+---------+---------+---------------------------+---------+----------+-------+
 
| id | select_type | table | partitions | type   | possible_keys         | key     | key_len | ref                       | rows    | filtered | Extra |
 
+----+-------------+-------+------------+--------+-----------------------+---------+---------+---------------------------+---------+----------+-------+
 
|  1 | SIMPLE      | c     | NULL       | ALL    | cluster_fk_deployment | NULL    | NULL    | NULL                      | 1421985 |   100.00 | NULL  |
 
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY               | PRIMARY | 767     | cs0275577.c.deployment_id |       1 |   100.00 | NULL  |
 
+----+-------------+-------+------------+--------+-----------------------+---------+---------+---------------------------+---------+----------+-------+
 
Last_query_cost=651 674


explain SELECT d.id, d.dname, c.id, c.cname, c.state FROM deployment d straight_join host c on d.id=c.deployment_id;
 
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+----------------+-------+----------+-------+
 
| id | select_type | table | partitions | type | possible_keys         | key                   | key_len | ref            | rows  | filtered | Extra |
 
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+----------------+-------+----------+-------+
 
|  1 | SIMPLE      | d     | NULL       | ALL  | PRIMARY               | NULL                  | NULL    | NULL           | 97470 |   100.00 | NULL  |
 
|  1 | SIMPLE      | c     | NULL       | ref  | cluster_fk_deployment | cluster_fk_deployment | 767     | cs0275577.d.id |    14 |   100.00 | NULL  |
 
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+----------------+-------+----------+-------+
 
Last_query_cost=1 320 881

Comment by Sergei Petrunia [ 2022-02-24 ]

Exploring costs in MySQL-8:

Plan d-c

plan_prefix rows cost
d 97470 9932
dc 14.6896 1.31095e+06

full_plan:
rows_for_plan=1.4318e+06
cost_for_plan=1.32088e+06

Plan c-d

plan_prefix rows cost
c 1421985 153995
cd 1 497695

full_plan:
rows_for_plan=1.42198e+06
cost_for_plan=651690

Comment by Sergei Petrunia [ 2022-02-24 ]

In MariaDB:

Plan c-d

plan_prefix rows cost
c 1459293 14464
cd 1 1459293

full_plan:
"rows_for_plan": 1459293,
"cost_for_plan": 2057474.2,

Plan d-c

plan_prefix rows cost
d 97335 740
d,c 7 791437.8057

"rows_for_plan": 681345,
"cost_for_plan": 947913.8057,

Here, #rows in d,c is off by a factor of 2...

Comment by Sergei Petrunia [ 2022-02-24 ]

In MariaDB with EITS stats, d-c plan is still picked.

Plan c-d

plan_prefix rows cost
d 98048 740
d,c 15 1,596,306

"rows_for_plan": 1,472,082.867,
"cost_for_plan": 1,911,072.586,

Plan c-d

plan_prefix rows cost
c 1472081 14464
c,d 1 1,472,081

"rows_for_plan": 1,472,081,
"cost_for_plan": 2,075,377.4,

Comment by Larry Adams [ 2022-02-25 ]

I'll see about getting some query traces in json on what we've been seeing tomorrow. I'm watching 10.5.16 closely right now due to the cursor issues that seem to have been resolved there. I've used FORCE INDEX hard in the mean time to keep things stable.

Comment by Sergei Petrunia [ 2022-02-25 ]

So,

  • in MariaDB, the costs of join orders are close.
  • in MySQL, the "d,c" order has cost of 1.3M while "c,d" has a cost of 500K.

there 1.5 M incoming row combinations from table C. Table d has 98K rows in total.
Could this be the reason for the difference in costs?

(Note: the costs problem is actually secondary. THE problem here is the lack of MDEV-8306: join order is picked with little regard to ORDER BY ... LIMIT optimization. MySQL, for some unrelated reason, happens to pick a suitable join order and so is lucky. MariaDB happens to pick a bad join order and so is not lucky. The difference between the picked join order is actually secondary. )

Comment by Larry Adams [ 2022-02-25 ]

Here is one example. Correct index would be 'clusterid_stat':

MariaDB [cacti]> explain UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='86' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1';
 
+------+-------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+
 
| id   | select_type | table     | type  | possible_keys                                                                                                                                                                                     | key     | key_len | ref  | rows    | Extra       |
 
+------+-------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+
 
|    1 | SIMPLE      | grid_jobs | range | PRIMARY,stat,job_end_logged,clusterid_stat_end_logged,clusterid_stat_start_time,stat_clusterid_exitInfo,stat_last_updated,clusterid_stat,clusterid,clusterid_stat_last_updated,clusterid_end_time | PRIMARY | 4       | NULL | 1019330 | Using where |
 
+------+-------------+-----------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+------+---------+-------------+
 
1 row in set (0.002 sec)


MariaDB [cacti]> explain UPDATE grid_jobs FORCE INDEX (clusterid_stat) SET job_end_logged=0 WHERE clusterid='86' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1';
 
+------+-------------+-----------+-------+----------------+----------------+---------+------+--------+-------------+
 
| id   | select_type | table     | type  | possible_keys  | key            | key_len | ref  | rows   | Extra       |
 
+------+-------------+-----------+-------+----------------+----------------+---------+------+--------+-------------+
 
|    1 | SIMPLE      | grid_jobs | range | clusterid_stat | clusterid_stat | 16      | NULL | 595960 | Using where |
 
+------+-------------+-----------+-------+----------------+----------------+---------+------+--------+-------------+
 
1 row in set (0.000 sec)


SET optimizer_trace='enabled=on';


 explain UPDATE grid_jobs SET job_end_logged=0 WHERE clusterid='86' AND stat NOT IN ('DONE', 'EXIT', 'PEND', 'PSUSP') AND job_end_logged='1' | {
 
  "steps": [
 
    {
 
      "table": "grid_jobs",
 
      "range_analysis": {
 
        "table_scan": {
 
          "rows": 3021357,
 
          "cost": 2222230.4
 
        },
 
        "potential_range_indexes": [
 
          {
 
            "index": "PRIMARY",
 
            "usable": true,
 
            "key_parts": ["clusterid", "jobid", "indexid", "submit_time"]
 
          },
 
          {
 
            "index": "user",
 
            "usable": true,
 
            "key_parts": ["user"]
 
          },
 
          {
 
            "index": "stat",
 
            "usable": true,
 
            "key_parts": ["stat"]
 
          },
 
          {
 
            "index": "queue",
 
            "usable": true,
 
            "key_parts": ["queue"]
 
          },
 
          {
 
            "index": "from_host",
 
            "usable": true,
 
            "key_parts": ["from_host"]
 
          },
 
          {
 
            "index": "exec_host",
 
            "usable": true,
 
            "key_parts": ["exec_host"]
 
          },
 
          {
 
            "index": "submit_time",
 
            "usable": true,
 
            "key_parts": ["submit_time"]
 
          },
 
          {
 
            "index": "projectName",
 
            "usable": true,
 
            "key_parts": ["projectName"]
 
          },
 
          {
 
            "index": "start_time",
 
            "usable": true,
 
            "key_parts": ["start_time"]
 
          },
 
          {
 
            "index": "end_time",
 
            "usable": true,
 
            "key_parts": ["end_time"]
 
          },
 
          {
 
            "index": "job_start_logged",
 
            "usable": true,
 
            "key_parts": ["job_start_logged"]
 
          },
 
          {
 
            "index": "job_end_logged",
 
            "usable": true,
 
            "key_parts": ["job_end_logged"]
 
          },
 
          {
 
            "index": "job_scan_logged",
 
            "usable": true,
 
            "key_parts": ["job_scan_logged"]
 
          },
 
          {
 
            "index": "jobname",
 
            "usable": true,
 
            "key_parts": ["jobname"]
 
          },
 
          {
 
            "index": "queue_clusterid",
 
            "usable": true,
 
            "key_parts": ["queue", "clusterid"]
 
          },
 
          {
 
            "index": "user_clusterid",
 
            "usable": true,
 
            "key_parts": ["user", "clusterid"]
 
          },
 
          {
 
            "index": "from_host_clusterid",
 
            "usable": true,
 
            "key_parts": ["from_host", "clusterid"]
 
          },
 
          {
 
            "index": "exec_host_clusterid",
 
            "usable": true,
 
            "key_parts": ["exec_host", "clusterid"]
 
          },
 
          {
 
            "index": "swap_used",
 
            "usable": true,
 
            "key_parts": ["swap_used"]
 
          },
 
          {
 
            "index": "cpu_used",
 
            "usable": true,
 
            "key_parts": ["cpu_used"]
 
          },
 
          {
 
            "index": "mem_used",
 
            "usable": true,
 
            "key_parts": ["mem_used"]
 
          },
 
          {
 
            "index": "num_cpus",
 
            "usable": true,
 
            "key_parts": ["num_cpus"]
 
          },
 
          {
 
            "index": "pend_time",
 
            "usable": true,
 
            "key_parts": ["pend_time"]
 
          },
 
          {
 
            "index": "run_time",
 
            "usable": true,
 
            "key_parts": ["run_time"]
 
          },
 
          {
 
            "index": "unkwn_time",
 
            "usable": true,
 
            "key_parts": ["unkwn_time"]
 
          },
 
          {
 
            "index": "prev_stat",
 
            "usable": true,
 
            "key_parts": ["prev_stat"]
 
          },
 
          {
 
            "index": "stat_changes",
 
            "usable": true,
 
            "key_parts": ["stat_changes"]
 
          },
 
          {
 
            "index": "flapping_logged",
 
            "usable": true,
 
            "key_parts": ["flapping_logged"]
 
          },
 
          {
 
            "index": "efficiency",
 
            "usable": true,
 
            "key_parts": ["efficiency"]
 
          },
 
          {
 
            "index": "effic_logged",
 
            "usable": true,
 
            "key_parts": ["effic_logged"]
 
          },
 
          {
 
            "index": "userGroup",
 
            "usable": true,
 
            "key_parts": ["userGroup"]
 
          },
 
          {
 
            "index": "indexid",
 
            "usable": true,
 
            "key_parts": ["indexid"]
 
          },
 
          {
 
            "index": "jobid",
 
            "usable": true,
 
            "key_parts": ["jobid"]
 
          },
 
          {
 
            "index": "pid_alarm_logged",
 
            "usable": true,
 
            "key_parts": ["pid_alarm_logged"]
 
          },
 
          {
 
            "index": "clusterid_stat_end_logged",
 
            "usable": true,
 
            "key_parts": ["clusterid", "stat", "job_end_logged"]
 
          },
 
          {
 
            "index": "clusterid_stat_start_time",
 
            "usable": true,
 
            "key_parts": ["clusterid", "stat", "start_time"]
 
          },
 
          {
 
            "index": "last_updated",
 
            "usable": true,
 
            "key_parts": ["last_updated"]
 
          },
 
          {
 
            "index": "licenseProject",
 
            "usable": true,
 
            "key_parts": ["licenseProject"]
 
          },
 
          {
 
            "index": "jobGroup",
 
            "usable": true,
 
            "key_parts": ["jobGroup"]
 
          },
 
          {
 
            "index": "sla",
 
            "usable": true,
 
            "key_parts": ["sla"]
 
          },
 
          {
 
            "index": "mem_reserved",
 
            "usable": true,
 
            "key_parts": ["mem_reserved"]
 
          },
 
          {
 
            "index": "stat_clusterid_exitInfo",
 
            "usable": true,
 
            "key_parts": ["stat", "clusterid", "exitInfo"]
 
          },
 
          {
 
            "index": "app",
 
            "usable": true,
 
            "key_parts": ["app"]
 
          },
 
          {
 
            "index": "exitInfo",
 
            "usable": true,
 
            "key_parts": ["exitInfo"]
 
          },
 
          {
 
            "index": "chargedSAAP",
 
            "usable": true,
 
            "key_parts": ["chargedSAAP"]
 
          },
 
          {
 
            "index": "stat_last_updated",
 
            "usable": true,
 
            "key_parts": ["stat", "last_updated"]
 
          },
 
          {
 
            "index": "clusterid_stat",
 
            "usable": true,
 
            "key_parts": ["clusterid", "stat"]
 
          },
 
          {
 
            "index": "clusterid",
 
            "usable": true,
 
            "key_parts": ["clusterid"]
 
          },
 
          {
 
            "index": "clusterid_stat_last_updated",
 
            "usable": true,
 
            "key_parts": ["clusterid", "stat", "last_updated"]
 
          },
 
          {
 
            "index": "clusterid_end_time",
 
            "usable": true,
 
            "key_parts": ["clusterid", "end_time"]
 
          }
 
        ],
 
        "setup_range_conditions": [],
 
        "analyzing_range_alternatives": {
 
          "range_scan_alternatives": [
 
            {
 
              "index": "PRIMARY",
 
              "ranges": ["(86) <= (clusterid) <= (86)"],
 
              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510678,
 
              "cost": 323642.7452,
 
              "chosen": true
 
            },
 
            {
 
              "index": "stat",
 
              "ranges": [
 
                "(stat) < (DONE)",
 
                "(DONE) < (stat) < (EXIT)",
 
                "(EXIT) < (stat) < (PEND)",
 
                "(PEND) < (stat) < (PSUSP)",
 
                "(PSUSP) < (stat)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510682,
 
              "cost": 1817331.119,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "job_end_logged",
 
              "ranges": ["(1) <= (job_end_logged) <= (1)"],
 
              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510678,
 
              "cost": 1815776.98,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "clusterid_stat_end_logged",
 
              "ranges": [
 
                "(86) <= (clusterid,stat) < (86,DONE)",
 
                "(86,DONE) < (clusterid,stat) < (86,EXIT)",
 
                "(86,EXIT) < (clusterid,stat) < (86,PEND)",
 
                "(86,PEND) < (clusterid,stat) < (86,PSUSP)",
 
                "(86,PSUSP) < (clusterid,stat) <= (86)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 756922,
 
              "cost": 910920.8956,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "clusterid_stat_start_time",
 
              "ranges": [
 
                "(86) <= (clusterid,stat) < (86,DONE)",
 
                "(86,DONE) < (clusterid,stat) < (86,EXIT)",
 
                "(86,EXIT) < (clusterid,stat) < (86,PEND)",
 
                "(86,PEND) < (clusterid,stat) < (86,PSUSP)",
 
                "(86,PSUSP) < (clusterid,stat) <= (86)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 676814,
 
              "cost": 814703.9004,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "stat_clusterid_exitInfo",
 
              "ranges": [
 
                "(stat) < (DONE)",
 
                "(DONE) < (stat) < (EXIT)",
 
                "(EXIT) < (stat) < (PEND)",
 
                "(PEND) < (stat) < (PSUSP)",
 
                "(PSUSP) < (stat)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510682,
 
              "cost": 1818459.638,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "stat_last_updated",
 
              "ranges": [
 
                "(stat) < (DONE)",
 
                "(DONE) < (stat) < (EXIT)",
 
                "(EXIT) < (stat) < (PEND)",
 
                "(PEND) < (stat) < (PSUSP)",
 
                "(PSUSP) < (stat)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510682,
 
              "cost": 1817898.878,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "clusterid_stat",
 
              "ranges": [
 
                "(86) <= (clusterid,stat) < (86,DONE)",
 
                "(86,DONE) < (clusterid,stat) < (86,EXIT)",
 
                "(86,EXIT) < (clusterid,stat) < (86,PEND)",
 
                "(86,PEND) < (clusterid,stat) < (86,PSUSP)",
 
                "(86,PSUSP) < (clusterid,stat) <= (86)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 595960,
 
              "cost": 717155.5255,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "clusterid",
 
              "ranges": ["(86) <= (clusterid) <= (86)"],
 
              "rowid_ordered": true,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510678,
 
              "cost": 1816199.299,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "clusterid_stat_last_updated",
 
              "ranges": [
 
                "(86) <= (clusterid,stat) < (86,DONE)",
 
                "(86,DONE) < (clusterid,stat) < (86,EXIT)",
 
                "(86,EXIT) < (clusterid,stat) < (86,PEND)",
 
                "(86,PEND) < (clusterid,stat) < (86,PSUSP)",
 
                "(86,PSUSP) < (clusterid,stat) <= (86)"
 
              ],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510682,
 
              "cost": 1818459.638,
 
              "chosen": false,
 
              "cause": "cost"
 
            },
 
            {
 
              "index": "clusterid_end_time",
 
              "ranges": ["(86) <= (clusterid) <= (86)"],
 
              "rowid_ordered": false,
 
              "using_mrr": false,
 
              "index_only": false,
 
              "rows": 1510678,
 
              "cost": 1816770.059,
 
              "chosen": false,
 
              "cause": "cost"
 
            }
 
          ],
 
          "analyzing_roworder_intersect": {
 
            "intersecting_indexes": [
 
              {
 
                "index": "job_end_logged",
 
                "index_scan_cost": 2965.23513,
 
                "cumulated_index_scan_cost": 2965.23513,
 
                "disk_sweep_cost": 1532184,
 
                "cumulative_total_cost": 1535149.235,
 
                "usable": true,
 
                "matching_rows_now": 1510678,
 
                "intersect_covering_with_this_index": false,
 
                "chosen": true
 
              },
 
              {
 
                "index": "clusterid",
 
                "index_scan_cost": 3387.554434,
 
                "cumulated_index_scan_cost": 6352.789564,
 
                "disk_sweep_cost": 766090.9859,
 
                "cumulative_total_cost": 772443.7754,
 
                "usable": true,
 
                "matching_rows_now": 755338.75,
 
                "intersect_covering_with_this_index": false,
 
                "chosen": true
 
              }
 
            ],
 
            "clustered_pk": {
 
              "index_scan_cost": 6042.712,
 
              "cumulated_index_scan_cost": 12395.50156,
 
              "disk_sweep_cost": 383045.4929,
 
              "clustered_pk_scan_added_to_intersect": true,
 
              "cumulated_cost": 395440.9945
 
            },
 
            "chosen": false,
 
            "cause": "cost"
 
          },
 
          "analyzing_index_merge_union": []
 
        },
 
        "group_index_range": {
 
          "chosen": false,
 
          "cause": "no join"
 
        },
 
        "chosen_range_access_summary": {
 
          "range_access_plan": {
 
            "type": "range_scan",
 
            "index": "PRIMARY",
 
            "rows": 1510678,
 
            "ranges": ["(86) <= (clusterid) <= (86)"]
 
          },
 
          "rows_for_plan": 1510678,
 
          "cost_for_plan": 323642.7452,
 
          "chosen": true
 
        }
 
      }
 
    }
 
  ]
 
}

Comment by Larry Adams [ 2022-02-25 ]

Though it says the cost for the PRIMARY is less, the query runs longer. Weird. The system is 100% 12 Disk NVMe Array, so the disk cost should be pretty close to 0.

Comment by Sergei Petrunia [ 2022-03-01 ]

TheWitness, thanks for sharing...

Taking the relevant parts of the trace:

         
 "range_scan_alternatives": [
 
    {
 
      "index": "PRIMARY",
 
      "ranges": ["(86) <= (clusterid) <= (86)"],
 
      "rowid_ordered": true,
 
      "using_mrr": false,
 
      "index_only": false,
 
      "rows": 1510678,
 
      "cost": 323642.7452,
 
      "chosen": true
 
    },

rows=1.5M, cost=323K 

    {
 
      "index": "clusterid_stat",
 
      "ranges": [
 
        "(86) <= (clusterid,stat) < (86,DONE)",
 
        "(86,DONE) < (clusterid,stat) < (86,EXIT)",
 
        "(86,EXIT) < (clusterid,stat) < (86,PEND)",
 
        "(86,PEND) < (clusterid,stat) < (86,PSUSP)",
 
        "(86,PSUSP) < (clusterid,stat) <= (86)"
 
      ],
 
      "rowid_ordered": false,
 
      "using_mrr": false,
 
      "index_only": false,
 
      "rows": 595960,
 
      "cost": 717155.5255,
 
      "chosen": false,
 
      "cause": "cost"
 
    },

rows=595K, cost=717K

And you've mentioned earlier

The MariaDB 10.5.12 server runs the query in > 1100 seconds
The MariaDB 10.5.12 server Using the FORCE INDEX (blah) runs the query in ~30 seconds

that the query without hint runs 40x slower? Is this so?

I think this is some different issue. What I saw in the original report were ORDER BY optimization issue and perhaps join operation costing issue, your query has neither.

Would it be possible for you to file another MDEV and more importantly, share the dataset with developers by uploading it to https://mariadb.com/kb/en/meta/mariadb-ftp-server/ ?

Comment by Larry Adams [ 2022-03-01 ]

Sergei,

There is some issue mixing going on in some of my earlier statements. I had a situation where the server has a lot of disk cache, but some tables were being swapped out for some reason anyway, and that resulted in longer queries on occasion, we also have the cursor issue that was causing some selects to hang due to them being locked though their status showed as running, which I believe will be fixed in 10.5.16.

Regardless, the case is still true, when it uses the PRIMARY, that query runs much slower (2x-3x at best case). Using the PRIMARY contains only the clusterid portion of the index, and there can be potentially many rows in that condition, but when you force the clusterid_stat, there are potentially in some cases only hundreds or thousands of rows. Additionally, and that index is used all the time, so the disk cost is kind of irrelevant. It would be nice if disk cost knew which indexes were already fully cached. Maybe that's there already. Big tool, and I love it, but I don't really want to get under the covers.

Comment by Larry Adams [ 2022-03-09 ]

Sergei,

Sharing the data set will be complicated as it has quite a bit of proprietary information in it. I would have to anonymize it first. Have to talk to Management. Once we have a contract, we will have an MSA in place and an NDA. Once that is in place, it should be easier.

Larry

Comment by JiraAutomate [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by Rick James [ 2023-12-05 ]

Perhaps the title should be "Picked the wrong table to start the NLJ with"?

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