Inefficient optimizer plan

[yoshinorim]

How to repeat:

CREATE TABLE `linktable` (
  `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id1_type` int(10) unsigned NOT NULL DEFAULT '0',
  `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id2_type` int(10) unsigned NOT NULL DEFAULT '0',
  `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
  `visibility` tinyint(3) NOT NULL DEFAULT '0',
  `data` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  `version` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`link_type`,`id1`,`id2`) COMMENT 'cf_link_pk',
  KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'rev:cf_link_id1_type'
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

insert into linktable values (1,1,1,1,1,1,1,1,1); 
insert into linktable values (1,1,2,1,1,1,1,1,1); 
insert into linktable values (1,1,3,1,1,1,1,1,1); 
insert into linktable values (1,1,4,1,1,1,1,1,1); 

then

mysql> explain select id1, id2, link_type, visibility, data, time,  version from linktable where id1 = 1 and link_type = 1 and id2 in (1, 2);
+----+-------------+-----------+------+------------------+----------+---------+-------------+------+--------------------------+
| id | select_type | table     | type | possible_keys    | key      | key_len | ref         | rows | Extra                    |
+----+-------------+-----------+------+------------------+----------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | linktable | ref  | PRIMARY,id1_type | id1_type | 16      | const,const |    1 | Using where; Using index |
+----+-------------+-----------+------+------------------+----------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

PRIMARY key should be used here. If id1_type is used, "id2 in (1,2)" can't be used to filter rows by the index.

[spetrunia]

so, if the optimizer chose PK, there would be three components

  link_type=1, id1=1, id2 in (1,2) 

while for id1_type there are two:

    id1=1, link_type=1

If one forces the use of PK:

MySQL [test]> explain select id1, id2, link_type, visibility, data, time,  version from linktable force index(PRIMARY) where id1 = 1 and link_type = 1 and id2 in (1, 2);                                                                                                                       
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | linktable | range | PRIMARY       | PRIMARY | 24      | NULL |    2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+

Then all three conditions are used to construct range access (note key_len=24). ICP would not have helped here.

EXPLAIN also shows Index Condition Pushdown is not used. This is because MyRocks doesn't support ICP on the primary key. Doing index scan on PK translates into rocksdb::DB->Get(lookup_tuple) calls. The calls retrieve both PK values and the record, there is no point where MyRocks could check ICP and decide not to read the record.

[spetrunia]

So, I think that ICP in MyRocks has nothing to do with this bug.

Still, using index id1_type instead of PRIMARY looks like a non-optimal choice. Two possible sources of problem:

1. ha_rocksdb::records_in_range() calls for PRIMARY and id1_type produce poor estimates that cause wrong index choice.

2. records_in_range() estimates are adequate, but then the optimizer calls handler->index_only_read_time() and handler->read_time() for PK and id1_type, and the returned costs are such that the wrong index is used.

@yoshinorim , can you post an optimizer trace for the EXPLAIN of the query?

[yoshinorim]

Right, I was wrong... this is nothing to do with ICP:)

Optimizer statistics was as follows.

            "rows_estimation": [
              {
                "table": "`linktable`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,
                    "cost": 262.1
                  },
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "link_type",
                        "id1",
                        "id2"
                      ]
                    },
                    {
                      "index": "id1_type",
                      "usable": true,
                      "key_parts": [
                        "id1",
                        "link_type",
                        "visibility",
                        "time",
                        "version",
                        "data",
                        "id2"
                      ]
                    }
                  ],
                  "best_covering_index_scan": {
                    "index": "id1_type",
                    "cost": 1200,
                    "chosen": false,
                    "cause": "cost"
                  },
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "1 <= link_type <= 1 AND 1 <= id1 <= 1 AND 1 <= id2 <= 1",
                          "1 <= link_type <= 1 AND 1 <= id1 <= 1 AND 2 <= id2 <= 2"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2,
                        "cost": 1.51,
                        "chosen": true
                      },
                      {
                        "index": "id1_type",
                        "ranges": [
                          "1 <= id1 <= 1 AND 1 <= link_type <= 1"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 1.26,
                        "chosen": true
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "id1_type",
                      "rows": 1,
                      "ranges": [
                        "1 <= id1 <= 1 AND 1 <= link_type <= 1"
                      ]
                    },
                    "rows_for_plan": 1,
                    "cost_for_plan": 1.26,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`linktable`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "rows": 2,
                      "cost": 2.4,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "id1_type",
                      "rows": 1,
                      "cost": 1.2,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "cause": "heuristic_index_cheaper",
                      "chosen": false
                    }
                  ]
                },
                "cost_for_plan": 1.2,
                "rows_for_plan": 1,
                "chosen": true
              }
            ]
          },

[yoshinorim]

https://reviews.facebook.net/D39705