revision-id: f6878cc0cfada28b9d3cf2abd12cab9df5b80b9a (mariadb-10.3.6-105-gf6878cc0cfa) parent(s): e09d8f66e26895eecc2ef77ca9ebbcff13fc6741 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-02-06 20:54:41 +0300 message: MDEV-18144: ANALYZE for statement support for PK filters ANALYZE and ANALYZE FORMAT=JSON structures are changed in the way that they show additional information when rowid filter is used: - r_selectivity_pct - the observed filter selectivity - r_buffer_size - the size of the rowid filter container buffer - r_filling_time_ms - how long it took to fill rowid filter container New class Rowid_filter_tracker was added. This class is needed to collect data about how rowid filter is executed. --- mysql-test/include/analyze-format.inc | 2 +- mysql-test/main/rowid_filter.result | 472 +++++++++++++++++++++++++++++++++- mysql-test/main/rowid_filter.test | 24 ++ sql/handler.h | 1 + sql/rowid_filter.cc | 3 + sql/rowid_filter.h | 12 +- sql/sql_analyze_stmt.h | 79 ++++++ sql/sql_class.cc | 2 +- sql/sql_explain.cc | 34 ++- sql/sql_explain.h | 3 + sql/sql_select.cc | 18 +- 11 files changed, 636 insertions(+), 14 deletions(-) diff --git a/mysql-test/include/analyze-format.inc b/mysql-test/include/analyze-format.inc index 65e61b81582..08006e26c79 100644 --- a/mysql-test/include/analyze-format.inc +++ b/mysql-test/include/analyze-format.inc @@ -1,3 +1,3 @@ # The time on ANALYSE FORMAT=JSON is rather variable ---replace_regex /("(r_total_time_ms|r_buffer_size)": )[^, \n]*/\1"REPLACED"/ +--replace_regex /("(r_total_time_ms|r_buffer_size|r_filling_time_ms)": )[^, \n]*/\1"REPLACED"/ diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 9420d7209a3..4ad83adcadb 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -58,7 +58,7 @@ set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using filter +1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; @@ -88,6 +88,50 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) 60.00 (3%) 11.02 100.00 Using index condition; Using where; Using rowid filter +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": ["i_l_shipdate", "i_l_quantity"], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rowid_filter": { + "range": { + "key": "i_l_quantity", + "used_key_parts": ["l_quantity"] + }, + "rows": 662, + "selectivity_pct": 11.024, + "r_rows": 605, + "r_selectivity_pct": 3.6855, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 1, + "rows": 509, + "r_rows": 60, + "r_total_time_ms": "REPLACED", + "filtered": 11.024, + "r_filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; @@ -178,6 +222,38 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 509 510.00 11.02 11.76 Using index condition; Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": ["i_l_shipdate", "i_l_quantity"], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 509, + "r_rows": 510, + "r_total_time_ms": "REPLACED", + "filtered": 11.024, + "r_filtered": 11.765, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; @@ -248,7 +324,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition -1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using filter +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -295,6 +371,73 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) 0.11 (10%) 5.40 100.00 Using where; Using rowid filter +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 98, + "r_rows": 98, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 81, + "selectivity_pct": 5.4, + "r_rows": 71, + "r_selectivity_pct": 10.417, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 98, + "rows": 1, + "r_rows": 0.1122, + "r_total_time_ms": "REPLACED", + "filtered": 5.4, + "r_filtered": 100, + "attached_condition": "orders.o_totalprice between 200000 and 230000" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -356,6 +499,61 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.40 11.22 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 98, + "r_rows": 98, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "r_loops": 98, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 5.4, + "r_filtered": 11.224, + "attached_condition": "orders.o_totalprice between 200000 and 230000" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -378,8 +576,8 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 AND o_totalprice between 180000 and 230000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using filter -1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (10%) Using where; Using filter +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) Using index condition; Using where; Using rowid filter +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (10%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -437,6 +635,89 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 509 (11%) 60.00 (3%) 11.02 100.00 Using index condition; Using where; Using rowid filter +1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (10%) 0.27 (25%) 10.13 100.00 Using where; Using rowid filter +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rowid_filter": { + "range": { + "key": "i_l_quantity", + "used_key_parts": ["l_quantity"] + }, + "rows": 662, + "selectivity_pct": 11.024, + "r_rows": 605, + "r_selectivity_pct": 3.6855, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 1, + "rows": 509, + "r_rows": 60, + "r_total_time_ms": "REPLACED", + "filtered": 11.024, + "r_filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "rowid_filter": { + "range": { + "key": "i_o_totalprice", + "used_key_parts": ["o_totalprice"] + }, + "rows": 152, + "selectivity_pct": 10.133, + "r_rows": 144, + "r_selectivity_pct": 25.424, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 60, + "rows": 1, + "r_rows": 0.2667, + "r_total_time_ms": "REPLACED", + "filtered": 10.133, + "r_filtered": 100, + "attached_condition": "orders.o_totalprice between 180000 and 230000" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -508,6 +789,65 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate 4 NULL 509 510.00 11.02 11.76 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 10.13 26.67 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 509, + "r_rows": 510, + "r_total_time_ms": "REPLACED", + "filtered": 11.024, + "r_filtered": 11.765, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "r_loops": 60, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 10.133, + "r_filtered": 26.667, + "attached_condition": "orders.o_totalprice between 180000 and 230000" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -536,7 +876,7 @@ WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 Using index condition -1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using filter +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -583,6 +923,73 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 71.00 100.00 100.00 Using index condition +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (8%) 0.52 (7%) 8.48 100.00 Using where; Using rowid filter +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "r_loops": 1, + "rows": 81, + "r_rows": 71, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "orders.o_totalprice between 200000 and 230000" + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "rowid_filter": { + "range": { + "key": "i_l_shipdate", + "used_key_parts": ["l_shipDATE"] + }, + "rows": 509, + "selectivity_pct": 8.4763, + "r_rows": 510, + "r_selectivity_pct": 7.7731, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 71, + "rows": 4, + "r_rows": 0.5211, + "r_total_time_ms": "REPLACED", + "filtered": 8.4763, + "r_filtered": 100, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -670,6 +1077,61 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 81 71.00 100.00 100.00 Using index condition +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.70 8.48 7.77 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "r_loops": 1, + "rows": 81, + "r_rows": 71, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "index_condition": "orders.o_totalprice between 200000 and 230000" + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "r_loops": 71, + "rows": 4, + "r_rows": 6.7042, + "r_total_time_ms": "REPLACED", + "filtered": 8.4763, + "r_filtered": 7.7731, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 6e378a7e93d..e1b0c69d470 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -46,11 +46,17 @@ SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem eval $with_filter EXPLAIN $q1; eval $with_filter EXPLAIN FORMAT=JSON $q1; +eval $with_filter ANALYZE $q1; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q1; --sorted_result eval $with_filter $q1; eval $without_filter EXPLAIN $q1; eval $without_filter EXPLAIN FORMAT=JSON $q1; +eval $without_filter ANALYZE $q1; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q1; --sorted_result eval $without_filter $q1; @@ -62,11 +68,17 @@ SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice eval $with_filter EXPLAIN $q2; eval $with_filter EXPLAIN FORMAT=JSON $q2; +eval $with_filter ANALYZE $q2; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q2; --sorted_result eval $with_filter $q2; eval $without_filter EXPLAIN $q2; eval $without_filter EXPLAIN FORMAT=JSON $q2; +eval $without_filter ANALYZE $q2; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q2; --sorted_result eval $without_filter $q2; @@ -79,11 +91,17 @@ SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice eval $with_filter EXPLAIN $q3; eval $with_filter EXPLAIN FORMAT=JSON $q3; +eval $with_filter ANALYZE $q3; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q3; --sorted_result eval $with_filter $q3; eval $without_filter EXPLAIN $q3; eval $without_filter EXPLAIN FORMAT=JSON $q3; +eval $without_filter ANALYZE $q3; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q3; --sorted_result eval $without_filter $q3; @@ -95,11 +113,17 @@ SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice eval $with_filter EXPLAIN $q4; eval $with_filter EXPLAIN FORMAT=JSON $q4; +eval $with_filter ANALYZE $q4; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q4; --sorted_result eval $with_filter $q4; eval $without_filter EXPLAIN $q4; eval $without_filter EXPLAIN FORMAT=JSON $q4; +eval $without_filter ANALYZE $q4; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q4; --sorted_result eval $without_filter $q4; diff --git a/sql/handler.h b/sql/handler.h index 27e38e9c36f..083636017db 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -3014,6 +3014,7 @@ class handler :public Sql_alloc Exec_time_tracker *tracker; public: void set_time_tracker(Exec_time_tracker *tracker_arg) { tracker=tracker_arg;} + Exec_time_tracker *get_time_tracker() { return tracker; } Item *pushed_idx_cond; uint pushed_idx_cond_keyno; /* The index which the above condition is for */ diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index 4348eea081d..b2349d26843 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -478,6 +478,8 @@ bool Range_rowid_filter::fill() file->position(quick->record); if (container->add(NULL, (char*) file->ref)) rc= 1; + else + tracker->increment_container_elements_count(); } } @@ -488,6 +490,7 @@ bool Range_rowid_filter::fill() file->pushed_idx_cond= pushed_idx_cond_save; file->pushed_idx_cond_keyno= pushed_idx_cond_keyno_save; file->in_range_check_pushed_down= in_range_check_pushed_down_save; + tracker->report_container_buff_size(table->file->ref_length); if (rc != HA_ERR_END_OF_FILE) return 1; diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h index 3cc2c31555c..de50fd36c4c 100644 --- a/sql/rowid_filter.h +++ b/sql/rowid_filter.h @@ -211,6 +211,8 @@ class Rowid_filter : public Sql_alloc /* The container to store info the set of elements in the filter */ Rowid_filter_container *container; + Rowid_filter_tracker *tracker; + public: Rowid_filter(Rowid_filter_container *container_arg) : container(container_arg) {} @@ -230,6 +232,9 @@ class Rowid_filter : public Sql_alloc virtual ~Rowid_filter() {} Rowid_filter_container *get_container() { return container; } + + void set_tracker(Rowid_filter_tracker *track_arg) { tracker= track_arg; } + Rowid_filter_tracker *get_tracker() { return tracker; } }; @@ -261,7 +266,12 @@ class Range_rowid_filter: public Rowid_filter bool build() { return fill(); } - bool check(char *elem) { return container->check(table, elem); } + bool check(char *elem) + { + bool was_checked= container->check(table, elem); + tracker->increment_checked_elements_count(was_checked); + return was_checked; + } bool fill(); diff --git a/sql/sql_analyze_stmt.h b/sql/sql_analyze_stmt.h index 27fd7fb6d6a..e490e7a7236 100644 --- a/sql/sql_analyze_stmt.h +++ b/sql/sql_analyze_stmt.h @@ -284,3 +284,82 @@ class Filesort_tracker : public Sql_alloc ulonglong sort_buffer_size; }; + +/** + A class to collect data about how rowid filter is executed. + + It stores information about how rowid filter container is filled, + containers size and observed selectivity. + + The observed selectivity is calculated in this way. + Some elements elem_set are checked if they belong to container. + Observed selectivity is calculated as the count of elem_set + elements that belong to container devided by all elem_set elements. +*/ + +class Rowid_filter_tracker : public Sql_alloc +{ +private: + /* A member to track the time to fill the rowid filter */ + Time_and_counter_tracker time_tracker; + + /* Size of the rowid filter container buffer */ + size_t container_buff_size; + + /* Count of elements that were used to fill the rowid filter container */ + uint container_elements; + + /* Elements counts used for observed selectivity calculation */ + uint n_checks; + uint n_positive_checks; +public: + Rowid_filter_tracker(bool do_timing) : + time_tracker(do_timing), container_buff_size(0), + container_elements(0), n_checks(0), n_positive_checks(0) + {} + + inline void start_tracking() + { + ANALYZE_START_TRACKING(&time_tracker); + } + + inline void stop_tracking() + { + ANALYZE_STOP_TRACKING(&time_tracker); + } + + /* Save container buffer size in bytes */ + inline void report_container_buff_size(uint elem_size) + { + container_buff_size= container_elements * elem_size / 8; + } + + Time_and_counter_tracker *get_time_tracker() + { + return &time_tracker; + } + + double get_time_fill_container_ms() + { + return time_tracker.get_time_ms(); + } + + void increment_checked_elements_count(bool was_checked) + { + n_checks++; + if (was_checked) + n_positive_checks++; + } + + inline void increment_container_elements_count() { container_elements++; } + + uint get_container_elements() { return container_elements; } + + double get_r_selectivity_pct() + { + return (double)n_positive_checks/(double)n_checks; + } + + size_t get_container_buff_size() { return container_buff_size; } +}; + diff --git a/sql/sql_class.cc b/sql/sql_class.cc index e6453415475..42b94d95933 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2728,7 +2728,7 @@ void THD::make_explain_field_list(List<Item> &field_list, uint8 explain_flags, if (is_analyze) { field_list.push_back(item= new (mem_root) - Item_float(this, "r_rows", 0.1234, 10, 4), + Item_empty_string(this, "r_rows", NAME_CHAR_LEN, cs), mem_root); item->maybe_null=1; } diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index c7c6f15f7cc..0c2f49338fe 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1324,6 +1324,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai item_list.push_back(item_null, mem_root); /* `r_rows` */ + StringBuffer<64> r_rows_str; if (is_analyze) { if (!tracker.has_scans()) @@ -1333,8 +1334,19 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai else { double avg_rows= tracker.get_avg_rows(); - item_list.push_back(new (mem_root) Item_float(thd, avg_rows, 2), - mem_root); + Item_float *fl= new (mem_root) Item_float(thd, avg_rows, 2); + String tmp; + String *res= fl->val_str(&tmp); + r_rows_str.append(res->ptr()); + if (rowid_filter) + { + r_rows_str.append(" ("); + r_rows_str.append_ulonglong(rowid_filter->tracker->get_r_selectivity_pct() * 100.0); + r_rows_str.append("%)"); + } + item_list.push_back(new (mem_root) + Item_string_sys(thd, r_rows_str.ptr(), + r_rows_str.length()), mem_root); } } @@ -1404,7 +1416,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai first= false; else extra_buf.append(STRING_WITH_LEN("; ")); - extra_buf.append(STRING_WITH_LEN("Using filter")); + extra_buf.append(STRING_WITH_LEN("Using rowid filter")); } item_list.push_back(new (mem_root) @@ -1604,6 +1616,16 @@ void Explain_rowid_filter::print_explain_json(Explain_query *query, quick->print_json(writer); writer->add_member("rows").add_ll(rows); writer->add_member("selectivity_pct").add_double(selectivity * 100.0); + if (is_analyze) + { + writer->add_member("r_rows").add_double(tracker->get_container_elements()); + writer->add_member("r_selectivity_pct"). + add_double(tracker->get_r_selectivity_pct() * 100.0); + writer->add_member("r_buffer_size"). + add_double(tracker->get_container_buff_size()); + writer->add_member("r_filling_time_ms"). + add_double(tracker->get_time_fill_container_ms()); + } writer->end_object(); // rowid_filter } @@ -1742,8 +1764,10 @@ void Explain_table_access::print_explain_json(Explain_query *query, if (op_tracker.get_loops()) { - writer->add_member("r_total_time_ms"). - add_double(op_tracker.get_time_ms()); + double total_time= op_tracker.get_time_ms(); + if (rowid_filter) + total_time+= rowid_filter->tracker->get_time_fill_container_ms(); + writer->add_member("r_total_time_ms").add_double(total_time); } } diff --git a/sql/sql_explain.h b/sql/sql_explain.h index a161f6c1049..08966b71faa 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -624,6 +624,9 @@ class Explain_rowid_filter : public Sql_alloc /* Expected selectivity for the filter */ double selectivity; + /* Tracker with the information about how rowid filter is executed */ + Rowid_filter_tracker *tracker; + void print_explain_json(Explain_query *query, Json_writer *writer, bool is_analyze); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a17682bd9c8..3d231345b73 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -12619,6 +12619,18 @@ void JOIN_TAB::build_range_rowid_filter_if_needed() { if (rowid_filter && !is_rowid_filter_built) { + /** + The same handler object (table->file) is used to build a filter + and to perfom a primary table access (by the main query). + + To estimate the time for filter building tracker should be changed + and after building of the filter has been finished it should be + switched back to the previos tracker. + */ + Exec_time_tracker *table_tracker= table->file->get_time_tracker(); + Rowid_filter_tracker *rowid_tracker= rowid_filter->get_tracker(); + table->file->set_time_tracker(rowid_tracker->get_time_tracker()); + rowid_tracker->start_tracking(); if (!rowid_filter->build()) { is_rowid_filter_built= true; @@ -12628,6 +12640,8 @@ void JOIN_TAB::build_range_rowid_filter_if_needed() delete rowid_filter; rowid_filter= 0; } + rowid_tracker->stop_tracking(); + table->file->set_time_tracker(table_tracker); } } @@ -25408,8 +25422,10 @@ bool JOIN_TAB::save_explain_data(Explain_table_access *eta, erf->quick= quick->get_explain(thd->mem_root); erf->selectivity= range_rowid_filter_info->selectivity; erf->rows= quick->records; + if (!(erf->tracker= new Rowid_filter_tracker(thd->lex->analyze_stmt))) + return 1; + rowid_filter->set_tracker(erf->tracker); eta->rowid_filter= erf; - //psergey-todo: also do setup for ANALYZE here. } if (tab_type == JT_NEXT)