Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21794

Optimizer flag rowid_filter leads to long query

    XMLWordPrintable

Details

    Description

      We noticed a slow query:

      SELECT /* ApiQueryExtLinksUsage::run */ el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501 /* ebd959f27d0c6fd989d1561b40ae8a3b db1107 enwiki 24s */
      

      This query involves the following tables:

      *************************** 1. row ***************************
             Table: pagelinks
      Create Table: CREATE TABLE `pagelinks` (
        `pl_from` int(8) unsigned NOT NULL DEFAULT 0,
        `pl_namespace` int(11) NOT NULL DEFAULT 0,
        `pl_title` varbinary(255) NOT NULL DEFAULT '',
        `pl_from_namespace` int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (`pl_from`,`pl_namespace`,`pl_title`),
        KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
        KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
      ) ENGINE=InnoDB DEFAULT CHARSET=binary
      *************************** 1. row ***************************
             Table: page
      Create Table: CREATE TABLE `page` (
        `page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
        `page_namespace` int(11) NOT NULL DEFAULT 0,
        `page_title` varbinary(255) NOT NULL DEFAULT '',
        `page_restrictions` tinyblob NOT NULL,
        `page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
        `page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
        `page_random` double unsigned NOT NULL DEFAULT 0,
        `page_touched` varbinary(14) NOT NULL DEFAULT '',
        `page_links_updated` varbinary(14) DEFAULT NULL,
        `page_latest` int(8) unsigned NOT NULL DEFAULT 0,
        `page_len` int(8) unsigned NOT NULL DEFAULT 0,
        `page_content_model` varbinary(32) DEFAULT NULL,
        `page_lang` varbinary(35) DEFAULT NULL,
        PRIMARY KEY (`page_id`),
        UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
        KEY `page_random` (`page_random`),
        KEY `page_len` (`page_len`),
        KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
      ) ENGINE=InnoDB AUTO_INCREMENT=63170169 DEFAULT CHARSET=binary
      

      The optimizer flags we had were:

      root@db1107.eqiad.wmnet[enwiki]> select @@optimizer_switch;
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      The following query has this execution plan:

      +------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
      | id   | select_type | table         | type         | possible_keys                                 | key                  | key_len | ref                          | rows     | Extra                                           |
      +------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
      |    1 | SIMPLE      | externallinks | range|filter | el_from,el_index,el_index_60,el_from_index_60 | el_index|el_index_60 | 62|62   | NULL                         | 553 (0%) | Using where; Using filesort; Using rowid filter |
      |    1 | SIMPLE      | page          | eq_ref       | PRIMARY                                       | PRIMARY              | 4       | enwiki.externallinks.el_from | 1        |                                                 |
      +------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
      2 rows in set (0.00 sec)
      

      That query was taking around 25 seconds.

      By turning off rowid_filter, the query doesn't change its query plan, but the execution time takes 0.01 to run:

      root@db1107.eqiad.wmnet[enwiki]> set session optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on';
      Query OK, 0 rows affected (0.00 sec)
       
      <query snip>
      501 rows in set (0.00 sec)
       
       
      root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryExtLinksUsage::run */ el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501 /* ebd959f27d0c6fd989d1561b40ae8a3b db1107 enwiki 24s */;
      +------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
      | id   | select_type | table         | type   | possible_keys                                 | key      | key_len | ref                          | rows | Extra                       |
      +------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
      |    1 | SIMPLE      | externallinks | range  | el_from,el_index,el_index_60,el_from_index_60 | el_index | 62      | NULL                         | 553  | Using where; Using filesort |
      |    1 | SIMPLE      | page          | eq_ref | PRIMARY                                       | PRIMARY  | 4       | enwiki.externallinks.el_from | 1    |                             |
      +------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
      2 rows in set (0.00 sec)
      
      

      Looks like that rowid_filter=ON is making the query super slow.

      This is a full optimizer trace with rowid_filter=ON:

      root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G
      *************************** 1. row ***************************
                                  QUERY: SELECT  el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501
                                  TRACE: {
        "steps": [
          {
            "join_preparation": {
              "select_id": 1,
              "steps": [
                {
                  "expanded_query": "select externallinks.el_index_60 AS el_index_60,externallinks.el_id AS el_id,`page`.page_id AS page_id,`page`.page_namespace AS page_namespace,`page`.page_title AS page_title,externallinks.el_to AS el_to from `page` join externallinks where `page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' order by externallinks.el_index_60,externallinks.el_id limit 501"
                }
              ]
            }
          },
          {
            "join_optimization": {
              "select_id": 1,
              "steps": [
                {
                  "condition_processing": {
                    "condition": "WHERE",
                    "original_condition": "`page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'",
                    "steps": [
                      {
                        "transformation": "equality_propagation",
                        "resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
                      },
                      {
                        "transformation": "constant_propagation",
                        "resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
                      },
                      {
                        "transformation": "trivial_condition_removal",
                        "resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
                      }
                    ]
                  }
                },
                {
                  "table_dependencies": [
                    {
                      "table": "page",
                      "row_may_be_null": false,
                      "map_bit": 0,
                      "depends_on_map_bits": []
                    },
                    {
                      "table": "externallinks",
                      "row_may_be_null": false,
                      "map_bit": 1,
                      "depends_on_map_bits": []
                    }
                  ]
                },
                {
                  "ref_optimizer_key_uses": [
                    {
                      "table": "page",
                      "field": "page_id",
                      "equals": "externallinks.el_from",
                      "null_rejecting": false
                    },
                    {
                      "table": "page",
                      "field": "page_id",
                      "equals": "externallinks.el_from",
                      "null_rejecting": false
                    },
                    {
                      "table": "externallinks",
                      "field": "el_from",
                      "equals": "`page`.page_id",
                      "null_rejecting": false
                    },
                    {
                      "table": "externallinks",
                      "field": "el_from",
                      "equals": "`page`.page_id",
                      "null_rejecting": false
                    },
                    {
                      "table": "externallinks",
                      "field": "el_from",
                      "equals": "`page`.page_id",
                      "null_rejecting": false
                    }
                  ]
                },
                {
                  "rows_estimation": [
                    {
                      "table": "page",
                      "table_scan": {
                        "rows": 42067094,
                        "cost": 405050
                      }
                    },
                    {
                      "table": "externallinks",
                      "range_analysis": {
                        "table_scan": {
                          "rows": 138944656,
                          "cost": 3.03e7
                        },
                        "potential_range_indexes": [
                          {
                            "index": "PRIMARY",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "el_from",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "el_to",
                            "usable": false,
                            "cause": "not applicable"
                          },
                          {
                            "index": "el_index",
                            "usable": true,
                            "key_parts": ["el_index"]
                          },
                          {
                            "index": "el_index_60",
                            "usable": true,
                            "key_parts": ["el_index_60", "el_id"]
                          },
                          {
                            "index": "el_from_index_60",
                            "usable": false,
                            "cause": "not applicable"
                          }
                        ],
                        "setup_range_conditions": [],
                        "group_index_range": {
                          "chosen": false,
                          "cause": "not single_table"
                        },
                        "analyzing_range_alternatives": {
                          "range_scan_alternatives": [
                            {
                              "index": "el_index",
                              "ranges": [
                                "(http://edu.utexas.cs.www./) <= (el_index) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                              ],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 553,
                              "cost": 696.95,
                              "chosen": true
                            },
                            {
                              "index": "el_index_60",
                              "ranges": [
                                "(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                              ],
                              "rowid_ordered": false,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 553,
                              "cost": 697.97,
                              "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": "el_index",
                            "rows": 553,
                            "ranges": [
                              "(http://edu.utexas.cs.www./) <= (el_index) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                            ]
                          },
                          "rows_for_plan": 553,
                          "cost_for_plan": 696.95,
                          "chosen": true
                        }
                      }
                    },
                    {
                      "selectivity_for_indexes": [
                        {
                          "index_name": "el_index",
                          "selectivity_from_index": 4e-6
                        },
                        {
                          "index_name": "el_index_60",
                          "selectivity_from_index": 4e-6
                        }
                      ],
                      "selectivity_for_columns": [],
                      "cond_selectivity": 2e-11
                    }
                  ]
                },
                {
                  "considered_execution_plans": [
                    {
                      "plan_prefix": [],
                      "table": "externallinks",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "range",
                            "resulting_rows": 0.0022,
                            "cost": 250.93,
                            "chosen": true,
                            "use_tmp_table": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "range",
                          "records": 0.0022,
                          "cost": 250.93,
                          "uses_join_buffering": false,
                          "filter_used": true
                        }
                      },
                      "rows_for_plan": 0.0022,
                      "cost_for_plan": 250.93,
                      "rest_of_plan": [
                        {
                          "plan_prefix": ["externallinks"],
                          "table": "page",
                          "best_access_path": {
                            "considered_access_paths": [
                              {
                                "access_type": "eq_ref",
                                "index": "PRIMARY",
                                "rows": 1,
                                "cost": 0.0022,
                                "chosen": true
                              },
                              {
                                "type": "scan",
                                "chosen": false,
                                "cause": "cost"
                              }
                            ],
                            "chosen_access_method": {
                              "type": "eq_ref",
                              "records": 1,
                              "cost": 0.0022,
                              "uses_join_buffering": false,
                              "filter_used": false
                            }
                          },
                          "rows_for_plan": 0.0022,
                          "cost_for_plan": 250.94,
                          "estimated_join_cardinality": 0.0022
                        }
                      ]
                    },
                    {
                      "plan_prefix": [],
                      "table": "page",
                      "best_access_path": {
                        "considered_access_paths": [
                          {
                            "access_type": "scan",
                            "resulting_rows": 4.21e7,
                            "cost": 405050,
                            "chosen": true
                          }
                        ],
                        "chosen_access_method": {
                          "type": "scan",
                          "records": 4.21e7,
                          "cost": 405050,
                          "uses_join_buffering": false,
                          "filter_used": false
                        }
                      },
                      "rows_for_plan": 4.21e7,
                      "cost_for_plan": 8.82e6,
                      "pruned_by_cost": true
                    }
                  ]
                },
                {
                  "best_join_order": ["externallinks", "page"]
                },
                {
                  "table": "externallinks",
                  "range_analysis": {
                    "table_scan": {
                      "rows": 138944656,
                      "cost": 2e308
                    },
                    "potential_range_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_from",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_to",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_index",
                        "usable": false,
                        "cause": "not applicable"
                      },
                      {
                        "index": "el_index_60",
                        "usable": true,
                        "key_parts": ["el_index_60", "el_id"]
                      },
                      {
                        "index": "el_from_index_60",
                        "usable": false,
                        "cause": "not applicable"
                      }
                    ],
                    "setup_range_conditions": [],
                    "analyzing_range_alternatives": {
                      "range_scan_alternatives": [
                        {
                          "index": "el_index_60",
                          "ranges": [
                            "(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                          ],
                          "rowid_ordered": false,
                          "using_mrr": false,
                          "index_only": false,
                          "rows": 553,
                          "cost": 697.97,
                          "chosen": true
                        }
                      ]
                    },
                    "chosen_range_access_summary": {
                      "range_access_plan": {
                        "type": "range_scan",
                        "index": "el_index_60",
                        "rows": 553,
                        "ranges": [
                          "(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
                        ]
                      },
                      "rows_for_plan": 553,
                      "cost_for_plan": 697.97,
                      "chosen": true
                    }
                  }
                },
                {
                  "attaching_conditions_to_tables": {
                    "original_condition": "`page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'",
                    "attached_conditions_computation": [],
                    "attached_conditions_summary": [
                      {
                        "table": "externallinks",
                        "attached": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'"
                      },
                      {
                        "table": "page",
                        "attached": null
                      }
                    ]
                  }
                },
                {
                  "reconsidering_access_paths_for_index_ordering": {
                    "clause": "ORDER BY",
                    "fanout": 1,
                    "read_time": 250.93,
                    "table": "externallinks",
                    "rows_estimation": 553,
                    "possible_keys": [
                      {
                        "index": "PRIMARY",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_from",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_to",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_index",
                        "can_resolve_order": false,
                        "cause": "not usable index for the query"
                      },
                      {
                        "index": "el_index_60",
                        "can_resolve_order": true,
                        "updated_limit": 138944656,
                        "range_scan_time": 697.97,
                        "index_scan_time": 697.97,
                        "usable": false,
                        "cause": "cost"
                      },
                      {
                        "index": "el_from_index_60",
                        "can_resolve_order": false,
                        "cause": "order can not be resolved by key"
                      }
                    ]
                  }
                }
              ]
            }
          },
          {
            "join_execution": {
              "select_id": 1,
              "steps": []
            }
          }
        ]
      }
      MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
                INSUFFICIENT_PRIVILEGES: 0
      

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              marostegui Manuel Arostegui
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.