revision-id: 4a9f2e73d140ae8cb7c52486305cf00641c378e4 (mariadb-10.3.6-99-g4a9f2e73d14) parent(s): 90708fc15cf835d03b511a953ae6939081a0f9e1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2018-12-05 01:43:31 +0300 message: MDEV-16188: Rowid filters Add support for EXPLAIN FORMAT=JSON. --- mysql-test/main/rowid_filter.result | 50 +++++++++++++++++++++++++- mysql-test/main/rowid_filter.test | 6 ++++ sql/sql_explain.cc | 72 +++++++++++++++++++++---------------- sql/sql_explain.h | 44 +++++++++++++++++------ sql/sql_select.cc | 36 +++++-------------- sql/sql_select.h | 1 - 6 files changed, 138 insertions(+), 71 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index e1296c35f88..c5267c1a6ed 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -146,6 +146,54 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-09' AND '1997-01-10' AND l_receiptdate BETWEEN '1997-01-09' AND '1997-01-10'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 6 (1%) Using index condition; Using where; Using filter +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate|i_l_shipdate 4|4 NULL 6 (0%) Using index condition; Using where; Using filter 1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 +# Same as above but EXPLAIN FORMAT=JSON: +EXPLAIN FORMAT=JSON SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-09' AND '1997-01-10' AND +l_receiptdate BETWEEN '1997-01-09' AND '1997-01-10'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_receiptdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_receiptdate", + "key_length": "4", + "used_key_parts": ["l_receiptDATE"], + "rowid_filter": { + "range": { + "key": "i_l_shipdate", + "used_key_parts": ["l_shipDATE"] + }, + "rows": 8, + "selectivity_pct": 0.1332 + }, + "rows": 6, + "filtered": 100, + "index_condition": "lineitem.l_receiptDATE between '1997-01-09' and '1997-01-10'", + "attached_condition": "lineitem.l_shipDATE between '1997-01-09' and '1997-01-10'" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rows": 1, + "filtered": 100 + } + } +} DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 4f41c408d94..73d7326623c 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -139,4 +139,10 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-09' AND '1997-01-10' AND l_receiptdate BETWEEN '1997-01-09' AND '1997-01-10'; +--echo # Same as above but EXPLAIN FORMAT=JSON: +EXPLAIN FORMAT=JSON SELECT * +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-09' AND '1997-01-10' AND + l_receiptdate BETWEEN '1997-01-09' AND '1997-01-10'; + DROP DATABASE dbt3_s001; diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index c7e9d6c906b..f7262e2624b 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1115,12 +1115,14 @@ void Explain_table_access::fill_key_str(String *key_str, bool is_json) const - this is just used key length for ref/range - for index_merge, it is a comma-separated list of lengths. - for hash join, it is key_len:pseudo_key_len + - [tabular form only] rowid filter length is added after "|". - The column looks identical in tabular and json forms. In JSON, we consider - the column legacy, it is superceded by used_key_parts. + In JSON, we consider this column to be legacy, it is superceded by + used_key_parts. */ -void Explain_table_access::fill_key_len_str(String *key_len_str) const +void Explain_table_access::fill_key_len_str(String *key_len_str, + bool is_json) const { bool is_hj= (type == JT_HASH || type == JT_HASH_NEXT || type == JT_HASH_RANGE || type == JT_HASH_INDEX_MERGE); @@ -1149,13 +1151,12 @@ void Explain_table_access::fill_key_len_str(String *key_len_str) const key_len_str->append(buf, length); } - if (key.get_filter_len() != (uint)-1) + if (!is_json && rowid_filter) { - char buf[64]; - size_t length; key_len_str->append('|'); - length= longlong10_to_str(key.get_filter_len(), buf, 10) - buf; - key_len_str->append(buf, length); + StringBuffer<64> filter_key_len; + rowid_filter->quick->print_key_len(&filter_key_len); + key_len_str->append(filter_key_len); } } @@ -1179,20 +1180,6 @@ bool Explain_index_use::set(MEM_ROOT *mem_root, KEY *key, uint key_len_arg) return 0; } -bool Explain_index_use::set_filter(MEM_ROOT *mem_root, KEY *key, uint key_len_arg) -{ - if (!(filter_name= strdup_root(mem_root, key->name.str))) - return 1; - filter_len= key_len_arg; - uint len= 0; - for (uint i= 0; i < key->usable_key_parts; i++) - { - len += key->key_part[i].store_length; - if (len >= key_len_arg) - break; - } - return 0; -} bool Explain_index_use::set_pseudo_key(MEM_ROOT *root, const char* key_name_arg) { @@ -1257,14 +1244,16 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai /* `type` column */ StringBuffer<64> join_type_buf; - if (!is_filter_set()) + if (rowid_filter == NULL) push_str(thd, &item_list, join_type_str[type]); else { join_type_buf.append(join_type_str[type]); join_type_buf.append("|filter"); item_list.push_back(new (mem_root) - Item_string_sys(thd, join_type_buf.ptr(), join_type_buf.length()), mem_root); + Item_string_sys(thd, join_type_buf.ptr(), + join_type_buf.length()), + mem_root); } /* `possible_keys` column */ @@ -1277,10 +1266,13 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai /* `key` */ StringBuffer<64> key_str; fill_key_str(&key_str, false); - if (key.get_filter_name()) + + if (rowid_filter) { key_str.append("|"); - key_str.append(key.get_filter_name()); + StringBuffer<64> rowid_key_str; + rowid_filter->quick->print_key(&rowid_key_str); + key_str.append(rowid_key_str); } if (key_str.length() > 0) @@ -1290,7 +1282,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai /* `key_len` */ StringBuffer<64> key_len_str; - fill_key_len_str(&key_len_str); + fill_key_len_str(&key_len_str, false); if (key_len_str.length() > 0) push_string(thd, &item_list, &key_len_str); @@ -1318,10 +1310,10 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai { rows_str.append_ulonglong((ulonglong)rows); - if (is_filter_set()) + if (rowid_filter) { rows_str.append(" ("); - rows_str.append_ulonglong(filter_perc); + rows_str.append_ulonglong(round(rowid_filter->selectivity * 100.0)); rows_str.append("%)"); } item_list.push_back(new (mem_root) @@ -1406,7 +1398,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai extra_buf.append(STRING_WITH_LEN("Using filesort")); } - if (is_filter_set()) + if (rowid_filter) { if (first) first= false; @@ -1603,6 +1595,19 @@ void add_json_keyset(Json_writer *writer, const char *elem_name, } +void Explain_rowid_filter::print_explain_json(Explain_query *query, + Json_writer *writer, + bool is_analyze) +{ + Json_writer_nesting_guard guard(writer); + writer->add_member("rowid_filter").start_object(); + quick->print_json(writer); + writer->add_member("rows").add_ll(rows); + writer->add_member("selectivity_pct").add_double(selectivity * 100.0); + writer->end_object(); // rowid_filter +} + + void Explain_table_access::print_explain_json(Explain_query *query, Json_writer *writer, bool is_analyze) @@ -1675,7 +1680,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, /* `key_length` */ StringBuffer<64> key_len_str; - fill_key_len_str(&key_len_str); + fill_key_len_str(&key_len_str, true); if (key_len_str.length()) writer->add_member("key_length").add_str(key_len_str); @@ -1700,6 +1705,11 @@ void Explain_table_access::print_explain_json(Explain_query *query, if (!ref_list.is_empty()) print_json_array(writer, "ref", ref_list); + if (rowid_filter) + { + rowid_filter->print_explain_json(query, writer, is_analyze); + } + /* r_loops (not present in tabular output) */ if (is_analyze) { diff --git a/sql/sql_explain.h b/sql/sql_explain.h index fc4e3462162..89fbd0f757d 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -602,12 +602,38 @@ class Explain_index_use : public Sql_alloc } bool set(MEM_ROOT *root, KEY *key_name, uint key_len_arg); bool set_pseudo_key(MEM_ROOT *root, const char *key_name); - bool set_filter(MEM_ROOT *root, KEY *key, uint key_len_arg); inline const char *get_key_name() const { return key_name; } inline uint get_key_len() const { return key_len; } - inline const char *get_filter_name() const { return filter_name; } - inline uint get_filter_len() const { return filter_len; } + //inline const char *get_filter_name() const { return filter_name; } +}; + + +/* + Query Plan data structure for Rowid filter. +*/ +class Explain_rowid_filter : public Sql_alloc +{ +public: + /* Quick select used to collect the rowids into filter */ + Explain_quick_select *quick; + + /* How many rows the above quick select is expected to return */ + ha_rows rows; + + /* Expected selectivity for the filter */ + double selectivity; + + void print_explain_json(Explain_query *query, Json_writer *writer, + bool is_analyze); + + /* + TODO: + Here should be ANALYZE members: + - r_rows for the quick select + - An object that tracked the table access time + - real selectivity of the filter. + */ }; @@ -677,6 +703,7 @@ class Explain_range_checked_fer : public Sql_alloc void print_json(Json_writer *writer, bool is_analyze); }; + /* EXPLAIN data structure for a single JOIN_TAB. */ @@ -697,7 +724,7 @@ class Explain_table_access : public Sql_alloc pushed_index_cond(NULL), sjm_nest(NULL), pre_join_sort(NULL), - filter_perc(UINT_MAX) + rowid_filter(NULL) {} ~Explain_table_access() { delete sjm_nest; } @@ -804,12 +831,7 @@ class Explain_table_access : public Sql_alloc Exec_time_tracker op_tracker; Table_access_tracker jbuf_tracker; - /** - How many rows are left after the filter was applied - to the initial rows count in percentages. - */ - double filter_perc; - inline bool is_filter_set() const { return (filter_perc != UINT_MAX); } + Explain_rowid_filter *rowid_filter; int print_explain(select_result_sink *output, uint8 explain_flags, bool is_analyze, @@ -821,7 +843,7 @@ class Explain_table_access : public Sql_alloc private: void append_tag_name(String *str, enum explain_extra_tag tag); void fill_key_str(String *key_str, bool is_json) const; - void fill_key_len_str(String *key_len_str) const; + void fill_key_len_str(String *key_len_str, bool is_json) const; double get_r_filtered(); void tag_to_json(Json_writer *writer, enum explain_extra_tag tag); }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c194fdb8824..2cb357c1eff 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25167,21 +25167,6 @@ int append_possible_keys(MEM_ROOT *alloc, String_list &list, TABLE *table, } -/** - This method saves the data that should be printed in EXPLAIN - if any filter was used for this table. -*/ - -bool JOIN_TAB::save_filter_explain_data(Explain_table_access *eta) -{ - if (!filter) - return 0; - (filter->selectivity*100 >= 1) ? eta->filter_perc= round(filter->selectivity*100) : - eta->filter_perc= 1; - return 0; -} - - bool JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tables, bool distinct_arg, JOIN_TAB *first_top_tab) @@ -25217,7 +25202,7 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta, filesort))) return 1; } - + // psergey-todo: data for filtering! tracker= &eta->tracker; jbuf_tracker= &eta->jbuf_tracker; @@ -25318,12 +25303,16 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta, // psergey-todo: ^ check for error return code /* Build "key", "key_len", and "ref" */ - if (filter) { - eta->key.set_filter(thd->mem_root, - &filter->table->key_info[filter->key_no], - rowid_filter->get_container()->get_select()->quick->max_used_key_length); + QUICK_SELECT_I *quick= rowid_filter->get_container()->get_select()->quick; + + Explain_rowid_filter *erf= new (thd->mem_root) Explain_rowid_filter; + erf->quick= quick->get_explain(thd->mem_root); + erf->selectivity= filter->selectivity; + erf->rows= quick->records; + eta->rowid_filter= erf; + //psergey-todo: also do setup for ANALYZE here. } if (tab_type == JT_NEXT) @@ -25445,13 +25434,6 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta, eta->filtered= f; } - if ((tab_select && tab_select->quick && tab_type != JT_CONST) || - (key_info && ref.key_parts && tab_type != JT_FT)) - { - if (save_filter_explain_data(eta)) - return 1; - } - /* Build "Extra" field and save it */ key_read= table->file->keyread_enabled(); if ((tab_type == JT_NEXT || tab_type == JT_CONST) && diff --git a/sql/sql_select.h b/sql/sql_select.h index 1df7e5e9f5b..2fea84d0bab 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -664,7 +664,6 @@ typedef struct st_join_table { SplM_plan_info *choose_best_splitting(double record_count, table_map remaining_tables); bool fix_splitting(SplM_plan_info *spl_plan, table_map remaining_tables); - bool save_filter_explain_data(Explain_table_access *eta); } JOIN_TAB;