Page MenuHomePhabricator

ApiQueryRecentChanges chooses a different plan on 10.4
Closed, ResolvedPublic

Description

I have found this query on 10.4 on enwiki:

SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001;

It takes a totally different plan from what 10.1 does:
10.4:

root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001 ;
+------+-------------+---------------+------+-------------------------------------------------------------------------------------------------------+----------+---------+-------------------------------+----------+------------------------------------+
| id   | select_type | table         | type | possible_keys                                                                                         | key      | key_len | ref                           | rows     | Extra                              |
+------+-------------+---------------+------+-------------------------------------------------------------------------------------------------------+----------+---------+-------------------------------+----------+------------------------------------+
|    1 | SIMPLE      | actor_rc_user | ALL  | PRIMARY                                                                                               | NULL     | NULL    | NULL                          | 74948417 | Using temporary; Using filesort    |
|    1 | SIMPLE      | recentchanges | ref  | rc_timestamp,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_actor | 8       | enwiki.actor_rc_user.actor_id | 8        | Using index condition; Using where |
+------+-------------+---------------+------+-------------------------------------------------------------------------------------------------------+----------+---------+-------------------------------+----------+------------------------------------+
2 rows in set (0.01 sec)

10.1:

root@db1106.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001 ;
+------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+
| id   | select_type | table         | type   | possible_keys                                                                                   | key          | key_len | ref                           | rows    | Extra       |
+------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+
|    1 | SIMPLE      | recentchanges | range  | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_timestamp | 16      | NULL                          | 3896296 | Using where |
|    1 | SIMPLE      | actor_rc_user | eq_ref | PRIMARY                                                                                         | PRIMARY      | 8       | enwiki.recentchanges.rc_actor |       1 |             |
+------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+
2 rows in set (0.00 sec)

Optimizer trace:

root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT  rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select recentchanges.rc_id AS rc_id,recentchanges.rc_timestamp AS rc_timestamp,recentchanges.rc_namespace AS rc_namespace,recentchanges.rc_title AS rc_title,recentchanges.rc_cur_id AS rc_cur_id,recentchanges.rc_type AS rc_type,recentchanges.rc_deleted AS rc_deleted,actor_rc_user.actor_user AS rc_user,actor_rc_user.actor_name AS rc_user_text,recentchanges.rc_actor AS rc_actor from (recentchanges join actor actor_rc_user on(actor_rc_user.actor_id = recentchanges.rc_actor)) where recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 order by recentchanges.rc_timestamp,recentchanges.rc_id limit 1001"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 and actor_rc_user.actor_id = recentchanges.rc_actor",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "recentchanges",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "actor_rc_user",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "recentchanges",
                "field": "rc_actor",
                "equals": "actor_rc_user.actor_id",
                "null_rejecting": false
              },
              {
                "table": "recentchanges",
                "field": "rc_actor",
                "equals": "actor_rc_user.actor_id",
                "null_rejecting": false
              },
              {
                "table": "actor_rc_user",
                "field": "actor_id",
                "equals": "recentchanges.rc_actor",
                "null_rejecting": false
              },
              {
                "table": "actor_rc_user",
                "field": "actor_id",
                "equals": "recentchanges.rc_actor",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "recentchanges",
                "range_analysis": {
                  "table_scan": {
                    "rows": 7996261,
                    "cost": 1.74e6
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_timestamp",
                      "usable": true,
                      "key_parts": ["rc_timestamp", "rc_id"]
                    },
                    {
                      "index": "rc_cur_id",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "new_name_timestamp",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_ip",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "tmp_2",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "tmp_3",
                      "usable": true,
                      "key_parts": ["rc_namespace", "rc_timestamp", "rc_id"]
                    },
                    {
                      "index": "rc_name_type_patrolled_timestamp",
                      "usable": true,
                      "key_parts": [
                        "rc_namespace",
                        "rc_type",
                        "rc_patrolled",
                        "rc_timestamp",
                        "rc_id"
                      ]
                    },
                    {
                      "index": "rc_ns_actor",
                      "usable": true,
                      "key_parts": ["rc_namespace", "rc_actor", "rc_id"]
                    },
                    {
                      "index": "rc_actor",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_namespace_title_timestamp",
                      "usable": true,
                      "key_parts": [
                        "rc_namespace",
                        "rc_title",
                        "rc_timestamp",
                        "rc_id"
                      ]
                    },
                    {
                      "index": "rc_this_oldid",
                      "usable": false,
                      "cause": "not applicable"
                    }
                  ],
                  "setup_range_conditions": [],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "rc_timestamp",
                        "ranges": [
                          "(20200206000000) <= (rc_timestamp) <= (20200220190025)"
                        ],
                        "rowid_ordered": false,
                        "using_mrr": true,
                        "index_only": false,
                        "rows": 3998130,
                        "cost": 4.34e6,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "tmp_3",
                        "ranges": [
                          "(0,020200206000000) <= (rc_namespace,rc_timestamp) <= (0,020200220190025)",
                          "(10,1020200206000000) <= (rc_namespace,rc_timestamp) <= (10,1020200220190025)",
                          "(14,1420200206000000) <= (rc_namespace,rc_timestamp) <= (14,1420200220190025)",
                          "(118,11820200206000000) <= (rc_namespace,rc_timestamp) <= (118,11820200220190025)"
                        ],
                        "rowid_ordered": false,
                        "using_mrr": true,
                        "index_only": false,
                        "rows": 6662832,
                        "cost": 7.06e6,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "rc_name_type_patrolled_timestamp",
                        "ranges": [
                          "(0,1) <= (rc_namespace,rc_type) <= (0,1)",
                          "(10,1) <= (rc_namespace,rc_type) <= (10,1)",
                          "(14,1) <= (rc_namespace,rc_type) <= (14,1)",
                          "(118,1) <= (rc_namespace,rc_type) <= (118,1)"
                        ],
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 139882,
                        "cost": 175345,
                        "chosen": true
                      },
                      {
                        "index": "rc_ns_actor",
                        "ranges": [
                          "(0) <= (rc_namespace) <= (0)",
                          "(10) <= (rc_namespace) <= (10)",
                          "(14) <= (rc_namespace) <= (14)",
                          "(118) <= (rc_namespace) <= (118)"
                        ],
                        "rowid_ordered": false,
                        "using_mrr": true,
                        "index_only": false,
                        "rows": 7996261,
                        "cost": 8.41e6,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "rc_namespace_title_timestamp",
                        "ranges": [
                          "(0) <= (rc_namespace) <= (0)",
                          "(10) <= (rc_namespace) <= (10)",
                          "(14) <= (rc_namespace) <= (14)",
                          "(118) <= (rc_namespace) <= (118)"
                        ],
                        "rowid_ordered": false,
                        "using_mrr": true,
                        "index_only": false,
                        "rows": 7996261,
                        "cost": 8.69e6,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "rc_name_type_patrolled_timestamp",
                      "rows": 139882,
                      "ranges": [
                        "(0,1) <= (rc_namespace,rc_type) <= (0,1)",
                        "(10,1) <= (rc_namespace,rc_type) <= (10,1)",
                        "(14,1) <= (rc_namespace,rc_type) <= (14,1)",
                        "(118,1) <= (rc_namespace,rc_type) <= (118,1)"
                      ]
                    },
                    "rows_for_plan": 139882,
                    "cost_for_plan": 175345,
                    "chosen": true
                  }
                }
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "tmp_3",
                    "selectivity_from_index": 0.8332
                  }
                ],
                "selectivity_for_columns": [],
                "cond_selectivity": 0.8332
              },
              {
                "table": "actor_rc_user",
                "table_scan": {
                  "rows": 74948426,
                  "cost": 227008
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "recentchanges",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "range",
                      "resulting_rows": 6.66e6,
                      "cost": 2e308,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "range",
                    "records": 6.66e6,
                    "cost": 2e308,
                    "uses_join_buffering": false,
                    "filter_used": false
                  }
                },
                "rows_for_plan": 6.66e6,
                "cost_for_plan": 2e308,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["recentchanges"],
                    "table": "actor_rc_user",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 6.66e6,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "resulting_rows": 7.49e7,
                          "cost": 1.68e9,
                          "chosen": false
                        }
                      ],
                      "chosen_access_method": {
                        "type": "eq_ref",
                        "records": 1,
                        "cost": 6.66e6,
                        "uses_join_buffering": false,
                        "filter_used": false
                      }
                    },
                    "rows_for_plan": 6.66e6,
                    "cost_for_plan": 2e308,
                    "estimated_join_cardinality": 6.66e6
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "actor_rc_user",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 7.49e7,
                      "cost": 227008,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 7.49e7,
                    "cost": 227008,
                    "uses_join_buffering": false,
                    "filter_used": false
                  }
                },
                "rows_for_plan": 7.49e7,
                "cost_for_plan": 1.52e7,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["actor_rc_user"],
                    "table": "recentchanges",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "ref",
                          "index": "rc_actor",
                          "rows": 8,
                          "cost": 6.75e8,
                          "chosen": true
                        },
                        {
                          "access_type": "range",
                          "resulting_rows": 6.66e6,
                          "cost": 2e308,
                          "chosen": false
                        }
                      ],
                      "chosen_access_method": {
                        "type": "ref",
                        "records": 8,
                        "cost": 6.75e8,
                        "uses_join_buffering": false,
                        "filter_used": false
                      }
                    },
                    "rows_for_plan": 6e8,
                    "cost_for_plan": 8.1e8,
                    "selectivity": 0.8332,
                    "estimated_join_cardinality": 5e8
                  }
                ]
              }
            ]
          },
          {
            "best_join_order": ["actor_rc_user", "recentchanges"]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "recentchanges.rc_type = 1 and recentchanges.rc_actor = actor_rc_user.actor_id and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)",
              "attached_conditions_computation": [],
              "attached_conditions_summary": [
                {
                  "table": "actor_rc_user",
                  "attached": null
                },
                {
                  "table": "recentchanges",
                  "attached": "recentchanges.rc_type = 1 and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)"
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0

Forcing the indexes 10.1 chooses, makes the query a lot faster and the explain makes more sense as it scans less rows:

root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` USE INDEX (rc_timestamp )JOIN `actor` `actor_rc_user` USE INDEX (PRIMARY) ON ((actor_rc_user.actor_id = rc_actor))  WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001;
+------+-------------+---------------+--------+---------------+--------------+---------+-------------------------------+---------+------------------------------------+
| id   | select_type | table         | type   | possible_keys | key          | key_len | ref                           | rows    | Extra                              |
+------+-------------+---------------+--------+---------------+--------------+---------+-------------------------------+---------+------------------------------------+
|    1 | SIMPLE      | recentchanges | range  | rc_timestamp  | rc_timestamp | 16      | NULL                          | 3998351 | Using index condition; Using where |
|    1 | SIMPLE      | actor_rc_user | eq_ref | PRIMARY       | PRIMARY      | 8       | enwiki.recentchanges.rc_actor | 1       |                                    |
+------+-------------+---------------+--------+---------------+--------------+---------+-------------------------------+---------+------------------------------------+
2 rows in set (0.00 sec)

The trace for this query is much better and the cost is smaller and the execution plan is faster:

root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
                            QUERY: SELECT  rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` USE INDEX (rc_timestamp )JOIN `actor` `actor_rc_user` USE INDEX (PRIMARY) ON ((actor_rc_user.actor_id = rc_actor))  WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select_id": 1,
        "steps": [
          {
            "expanded_query": "select recentchanges.rc_id AS rc_id,recentchanges.rc_timestamp AS rc_timestamp,recentchanges.rc_namespace AS rc_namespace,recentchanges.rc_title AS rc_title,recentchanges.rc_cur_id AS rc_cur_id,recentchanges.rc_type AS rc_type,recentchanges.rc_deleted AS rc_deleted,actor_rc_user.actor_user AS rc_user,actor_rc_user.actor_name AS rc_user_text,recentchanges.rc_actor AS rc_actor from (recentchanges USE INDEX (rc_timestamp) join actor actor_rc_user USE INDEX (PRIMARY) on(actor_rc_user.actor_id = recentchanges.rc_actor)) where recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 order by recentchanges.rc_timestamp,recentchanges.rc_id limit 1001"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select_id": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and recentchanges.rc_type = 1 and actor_rc_user.actor_id = recentchanges.rc_actor",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118) and multiple equal(1, recentchanges.rc_type) and multiple equal(actor_rc_user.actor_id, recentchanges.rc_actor)"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "recentchanges",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": []
              },
              {
                "table": "actor_rc_user",
                "row_may_be_null": false,
                "map_bit": 1,
                "depends_on_map_bits": []
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "recentchanges",
                "field": "rc_actor",
                "equals": "actor_rc_user.actor_id",
                "null_rejecting": false
              },
              {
                "table": "actor_rc_user",
                "field": "actor_id",
                "equals": "recentchanges.rc_actor",
                "null_rejecting": false
              },
              {
                "table": "actor_rc_user",
                "field": "actor_id",
                "equals": "recentchanges.rc_actor",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "recentchanges",
                "range_analysis": {
                  "table_scan": {
                    "rows": 7996798,
                    "cost": 1.74e6
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_timestamp",
                      "usable": true,
                      "key_parts": ["rc_timestamp", "rc_id"]
                    },
                    {
                      "index": "rc_cur_id",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "new_name_timestamp",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_ip",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "tmp_2",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "tmp_3",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_name_type_patrolled_timestamp",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_ns_actor",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_actor",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_namespace_title_timestamp",
                      "usable": false,
                      "cause": "not applicable"
                    },
                    {
                      "index": "rc_this_oldid",
                      "usable": false,
                      "cause": "not applicable"
                    }
                  ],
                  "setup_range_conditions": [],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not single_table"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "rc_timestamp",
                        "ranges": [
                          "(20200206000000) <= (rc_timestamp) <= (20200220190025)"
                        ],
                        "rowid_ordered": false,
                        "using_mrr": true,
                        "index_only": false,
                        "rows": 3998399,
                        "cost": 4.34e6,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "cause": "too few roworder scans"
                    },
                    "analyzing_index_merge_union": []
                  }
                }
              },
              {
                "selectivity_for_indexes": [
                  {
                    "index_name": "rc_timestamp",
                    "selectivity_from_index": 0.5
                  }
                ],
                "selectivity_for_columns": [],
                "cond_selectivity": 0.5
              },
              {
                "table": "actor_rc_user",
                "table_scan": {
                  "rows": 74948426,
                  "cost": 227008
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [],
                "table": "recentchanges",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 4e6,
                      "cost": 942209,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 4e6,
                    "cost": 942209,
                    "uses_join_buffering": false,
                    "filter_used": false
                  }
                },
                "rows_for_plan": 4e6,
                "cost_for_plan": 1.74e6,
                "rest_of_plan": [
                  {
                    "plan_prefix": ["recentchanges"],
                    "table": "actor_rc_user",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "access_type": "eq_ref",
                          "index": "PRIMARY",
                          "rows": 1,
                          "cost": 4e6,
                          "chosen": true
                        },
                        {
                          "access_type": "scan",
                          "resulting_rows": 7.49e7,
                          "cost": 1.01e9,
                          "chosen": false
                        }
                      ],
                      "chosen_access_method": {
                        "type": "eq_ref",
                        "records": 1,
                        "cost": 4e6,
                        "uses_join_buffering": false,
                        "filter_used": false
                      }
                    },
                    "rows_for_plan": 4e6,
                    "cost_for_plan": 6.54e6,
                    "estimated_join_cardinality": 4e6
                  }
                ]
              },
              {
                "plan_prefix": [],
                "table": "actor_rc_user",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "resulting_rows": 7.49e7,
                      "cost": 227008,
                      "chosen": true
                    }
                  ],
                  "chosen_access_method": {
                    "type": "scan",
                    "records": 7.49e7,
                    "cost": 227008,
                    "uses_join_buffering": false,
                    "filter_used": false
                  }
                },
                "rows_for_plan": 7.49e7,
                "cost_for_plan": 1.52e7,
                "pruned_by_cost": true
              }
            ]
          },
          {
            "best_join_order": ["recentchanges", "actor_rc_user"]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "recentchanges.rc_type = 1 and actor_rc_user.actor_id = recentchanges.rc_actor and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)",
              "attached_conditions_computation": [
                {
                  "table": "recentchanges",
                  "range_analysis": {
                    "table_scan": {
                      "rows": 7996798,
                      "cost": 9.6e6
                    },
                    "potential_range_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_timestamp",
                        "usable": true,
                        "key_parts": ["rc_timestamp", "rc_id"]
                      },
                      {
                        "index": "rc_cur_id",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "new_name_timestamp",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_ip",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "tmp_2",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "tmp_3",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_name_type_patrolled_timestamp",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_ns_actor",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_actor",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_namespace_title_timestamp",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "rc_this_oldid",
                        "usable": false,
                        "cause": "not applicable"
                      }
                    ],
                    "setup_range_conditions": [],
                    "group_index_range": {
                      "chosen": false,
                      "cause": "not single_table"
                    },
                    "analyzing_range_alternatives": {
                      "range_scan_alternatives": [
                        {
                          "index": "rc_timestamp",
                          "ranges": [
                            "(20200206000000) <= (rc_timestamp) <= (20200220190025)"
                          ],
                          "rowid_ordered": false,
                          "using_mrr": true,
                          "index_only": false,
                          "rows": 3998399,
                          "cost": 4.34e6,
                          "chosen": true
                        }
                      ],
                      "analyzing_roworder_intersect": {
                        "cause": "too few roworder scans"
                      },
                      "analyzing_index_merge_union": []
                    },
                    "chosen_range_access_summary": {
                      "range_access_plan": {
                        "type": "range_scan",
                        "index": "rc_timestamp",
                        "rows": 3998399,
                        "ranges": [
                          "(20200206000000) <= (rc_timestamp) <= (20200220190025)"
                        ]
                      },
                      "rows_for_plan": 3998399,
                      "cost_for_plan": 4.34e6,
                      "chosen": true
                    }
                  }
                }
              ],
              "attached_conditions_summary": [
                {
                  "table": "recentchanges",
                  "attached": "recentchanges.rc_type = 1 and recentchanges.rc_timestamp >= '20200206000000' and recentchanges.rc_timestamp <= '20200220190025' and recentchanges.rc_namespace in (0,10,14,118)"
                },
                {
                  "table": "actor_rc_user",
                  "attached": null
                }
              ]
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select_id": 1,
        "steps": []
      }
    }
  ]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

It might make sense to report this to MariaDB with the full trace

Event Timeline

Marostegui created this task.
Marostegui moved this task from Triage to In progress on the DBA board.

I am going to run analyze on this table, just in case, if it doesn't make any difference, I will report this to MariaDB.

Fixed by analyzing the tables and the stats got refreshed:

root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryRecentChanges::run */ rc_id, rc_timestamp, rc_namespace, rc_title, rc_cur_id, rc_type, rc_deleted, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor FROM `recentchanges` JOIN `actor` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) WHERE (rc_timestamp>='20200206000000') AND (rc_timestamp<='20200220190025') AND rc_namespace IN (0, 10, 14, 118) AND rc_type = 1 ORDER BY rc_timestamp ASC, rc_id ASC LIMIT 1001;
+------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+
| id   | select_type | table         | type   | possible_keys                                                                                         | key          | key_len | ref                           | rows    | Extra       |
+------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+
|    1 | SIMPLE      | recentchanges | range  | rc_timestamp,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp | rc_timestamp | 16      | NULL                          | 4136617 | Using where |
|    1 | SIMPLE      | actor_rc_user | eq_ref | PRIMARY                                                                                               | PRIMARY      | 8       | enwiki.recentchanges.rc_actor | 1       |             |
+------+-------------+---------------+--------+-------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------+---------+-------------+
2 rows in set (0.00 sec)


<snip>
1001 rows in set (0.16 sec)