revision-id: 3955d2a1539e7e41e3457c99f773d90c90f4f90f (mariadb-10.3.6-112-g3955d2a1539) parent(s): 15fe81c571ef81ec834ac91f96c9adb4343ecd05 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-02-10 22:36:46 +0300 message: MDEV-18413: Find constraint correlated indexes Find indexes of one table which parts participate in one constraint. These indexes are called constraint correlated. New methods: TABLE::find_constraint_correlated_indexes() and virtual method check_index_dependence() were added. For each index it's own constraint correlated index map was created where all indexes that are constraint correlated with the current are marked. The results of this task are used for MDEV-16188 (Use in-memory PK filters built from range index scans). --- mysql-test/main/rowid_filter.result | 765 +++++++++++++++++++++++++++++ mysql-test/main/rowid_filter.test | 100 +++- mysql-test/main/rowid_filter_innodb.result | 765 +++++++++++++++++++++++++++++ sql/item.h | 7 + sql/rowid_filter.cc | 14 +- sql/structs.h | 2 + sql/table.cc | 76 +++ sql/table.h | 3 + 8 files changed, 1726 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 4ad83adcadb..34df3035051 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -1174,5 +1174,770 @@ o_orderkey l_linenumber l_shipdate o_totalprice 5895 4 1997-03-03 201419.83 5895 5 1997-04-30 201419.83 5895 6 1997-04-19 201419.83 +# +# MDEV-18413: find constraint correlated indexes +# +ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate); +# Filter on l_shipdate is not used because it participates in +# the same constraint as l_receiptdate. +# Access is made on l_receiptdate. +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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"], + "rows": 18, + "filtered": 0.5662, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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"], + "rows": 1, + "filtered": 8.7333, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 8.73 14.29 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_receiptdate", + "key_length": "4", + "used_key_parts": ["l_receiptDATE"], + "r_loops": 1, + "rows": 18, + "r_rows": 18, + "r_total_time_ms": "REPLACED", + "filtered": 0.5662, + "r_filtered": 38.889, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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": 7, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 8.7333, + "r_filtered": 14.286, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +l_shipdate l_receiptdate o_totalprice +1996-10-07 1996-10-08 202623.92 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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"], + "rows": 18, + "filtered": 0.5662, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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"], + "rows": 1, + "filtered": 8.7333, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 8.73 14.29 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_receiptdate", + "key_length": "4", + "used_key_parts": ["l_receiptDATE"], + "r_loops": 1, + "rows": 18, + "r_rows": 18, + "r_total_time_ms": "REPLACED", + "filtered": 0.5662, + "r_filtered": 38.889, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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": 7, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 8.7333, + "r_filtered": 14.286, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +l_shipdate l_receiptdate o_totalprice +1996-10-07 1996-10-08 202623.92 +ALTER TABLE orders ADD COLUMN o_totaldiscount double; +UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000); +CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount); +ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount); +# Filter on o_totalprice is not used because it participates in +# the same constraint as o_discount. +# Access is made on o_discount. +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 39, + "filtered": 3.2667, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.27 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 39, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 3.2667, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 39, + "filtered": 3.2667, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 3.27 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 39, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 3.2667, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +CREATE VIEW v1 AS +SELECT * FROM orders +WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01'; +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_o_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 39, + "filtered": 1.9905, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.99 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 39, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 1.9905, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_o_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 39, + "filtered": 1.9905, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 39 41.00 1.99 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 39, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 1.9905, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +ALTER TABLE lineitem DROP CONSTRAINT l_date; +ALTER TABLE orders DROP CONSTRAINT o_price; +ALTER TABLE orders DROP COLUMN o_totaldiscount; +DROP VIEW v1; DROP DATABASE dbt3_s001; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index e1b0c69d470..0e8a3de9c8f 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -127,7 +127,105 @@ eval $without_filter ANALYZE FORMAT=JSON $q4; --sorted_result eval $without_filter $q4; +--echo # +--echo # MDEV-18413: find constraint correlated indexes +--echo # + +ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate); + +--echo # Filter on l_shipdate is not used because it participates in +--echo # the same constraint as l_receiptdate. +--echo # Access is made on l_receiptdate. +let $q5= +SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND + l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND + l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND + o_totalprice BETWEEN 200000 AND 250000; + +eval $with_filter EXPLAIN $q5; +eval $with_filter EXPLAIN FORMAT=JSON $q5; +eval $with_filter ANALYZE $q5; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q5; +--sorted_result +eval $with_filter $q5; + +eval $without_filter EXPLAIN $q5; +eval $without_filter EXPLAIN FORMAT=JSON $q5; +eval $without_filter ANALYZE $q5; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q5; +--sorted_result +eval $without_filter $q5; + +ALTER TABLE orders ADD COLUMN o_totaldiscount double; +UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000); +CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount); + +ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount); + +--echo # Filter on o_totalprice is not used because it participates in +--echo # the same constraint as o_discount. +--echo # Access is made on o_discount. +let $q6= +SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND + o_totaldiscount BETWEEN 18000 AND 20000 AND + o_totalprice BETWEEN 200000 AND 220000 AND + l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; + +eval $with_filter EXPLAIN $q6; +eval $with_filter EXPLAIN FORMAT=JSON $q6; +eval $with_filter ANALYZE $q6; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q6; +--sorted_result +eval $with_filter $q6; + +eval $without_filter EXPLAIN $q6; +eval $without_filter EXPLAIN FORMAT=JSON $q6; +eval $without_filter ANALYZE $q6; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q6; +--sorted_result +eval $without_filter $q6; + +CREATE VIEW v1 AS +SELECT * FROM orders +WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01'; + +let $q7= +SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND + o_totaldiscount BETWEEN 18000 AND 20000 AND + o_totalprice BETWEEN 200000 AND 220000 AND + l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; + +eval $with_filter EXPLAIN $q7; +eval $with_filter EXPLAIN FORMAT=JSON $q7; +eval $with_filter ANALYZE $q7; +--source include/analyze-format.inc +eval $with_filter ANALYZE FORMAT=JSON $q7; +--sorted_result +eval $with_filter $q7; + +eval $without_filter EXPLAIN $q7; +eval $without_filter EXPLAIN FORMAT=JSON $q7; +eval $without_filter ANALYZE $q7; +--source include/analyze-format.inc +eval $without_filter ANALYZE FORMAT=JSON $q7; +--sorted_result +eval $without_filter $q7; + +ALTER TABLE lineitem DROP CONSTRAINT l_date; +ALTER TABLE orders DROP CONSTRAINT o_price; +ALTER TABLE orders DROP COLUMN o_totaldiscount; +DROP VIEW v1; + DROP DATABASE dbt3_s001; set @@use_stat_tables=@save_use_stat_tables; - diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index f72db6b6bc0..65f68968f6e 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1103,6 +1103,771 @@ o_orderkey l_linenumber l_shipdate o_totalprice 5895 4 1997-03-03 201419.83 5895 5 1997-04-30 201419.83 5895 6 1997-04-19 201419.83 +# +# MDEV-18413: find constraint correlated indexes +# +ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate); +# Filter on l_shipdate is not used because it participates in +# the same constraint as l_receiptdate. +# Access is made on l_receiptdate. +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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"], + "rows": 18, + "filtered": 0.5662, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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"], + "rows": 1, + "filtered": 5.6667, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.67 14.29 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_receiptdate", + "key_length": "4", + "used_key_parts": ["l_receiptDATE"], + "r_loops": 1, + "rows": 18, + "r_rows": 18, + "r_total_time_ms": "REPLACED", + "filtered": 0.5662, + "r_filtered": 38.889, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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": 7, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 5.6667, + "r_filtered": 14.286, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +l_shipdate l_receiptdate o_totalprice +1996-10-07 1996-10-08 202623.92 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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"], + "rows": 18, + "filtered": 0.5662, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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"], + "rows": 1, + "filtered": 5.6667, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.67 14.29 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +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_receiptdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_receiptdate", + "key_length": "4", + "used_key_parts": ["l_receiptDATE"], + "r_loops": 1, + "rows": 18, + "r_rows": 18, + "r_total_time_ms": "REPLACED", + "filtered": 0.5662, + "r_filtered": 38.889, + "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" + }, + "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": 7, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 5.6667, + "r_filtered": 14.286, + "attached_condition": "orders.o_totalprice between 200000 and 250000" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT l_shipdate, l_receiptdate, o_totalprice +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND +l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND +o_totalprice BETWEEN 200000 AND 250000; +l_shipdate l_receiptdate o_totalprice +1996-10-07 1996-10-08 202623.92 +ALTER TABLE orders ADD COLUMN o_totaldiscount double; +UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000); +CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount); +ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount); +# Filter on o_totalprice is not used because it participates in +# the same constraint as o_discount. +# Access is made on o_discount. +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 41, + "filtered": 3.3333, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 3.33 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 41, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 3.3333, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 41, + "filtered": 3.3333, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 3.33 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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", "i_o_totaldiscount"], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 41, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 3.3333, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM orders, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +CREATE VIEW v1 AS +SELECT * FROM orders +WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01'; +set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_o_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 41, + "filtered": 2.0711, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 2.07 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 41, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 2.0711, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_o_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "rows": 41, + "filtered": 2.0711, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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"], + "rows": 4, + "filtered": 3.0475, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 2.07 2.44 Using index condition; Using where +1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +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_orderdate", + "i_o_totalprice", + "i_o_totaldiscount" + ], + "key": "i_o_totaldiscount", + "key_length": "9", + "used_key_parts": ["o_totaldiscount"], + "r_loops": 1, + "rows": 41, + "r_rows": 41, + "r_total_time_ms": "REPLACED", + "filtered": 2.0711, + "r_filtered": 2.439, + "index_condition": "orders.o_totaldiscount between 18000 and 20000", + "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" + }, + "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": 1, + "rows": 4, + "r_rows": 6, + "r_total_time_ms": "REPLACED", + "filtered": 3.0475, + "r_filtered": 66.667, + "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" + } + } +} +set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate +FROM v1, lineitem +WHERE o_orderkey=l_orderkey AND +o_totaldiscount BETWEEN 18000 AND 20000 AND +o_totalprice BETWEEN 200000 AND 220000 AND +l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; +o_totaldiscount o_totalprice l_shipdate +18016.04288 219707.84 1996-10-02 +18016.04288 219707.84 1996-10-17 +18016.04288 219707.84 1996-11-04 +18016.04288 219707.84 1996-11-14 +ALTER TABLE lineitem DROP CONSTRAINT l_date; +ALTER TABLE orders DROP CONSTRAINT o_price; +ALTER TABLE orders DROP COLUMN o_totaldiscount; +DROP VIEW v1; DROP DATABASE dbt3_s001; set @@use_stat_tables=@save_use_stat_tables; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/sql/item.h b/sql/item.h index fd9261c3424..f8580a80edf 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1962,6 +1962,12 @@ class Item: public Value_source, virtual bool check_valid_arguments_processor(void *arg) { return 0; } virtual bool update_vcol_processor(void *arg) { return 0; } virtual bool set_fields_as_dependent_processor(void *arg) { return 0; } + /* + Find if some of the key parts of table keys (the reference on table is + passed as an argument) participate in the expression. + If there is some, sets a bit for this key in the proper key map. + */ + virtual bool check_index_dependence(void *arg) { return 0; } /*============== End of Item processor list ======================*/ virtual Item *get_copy(THD *thd)=0; @@ -3464,6 +3470,7 @@ class Item_field :public Item_ident, DBUG_ASSERT(field_type() == MYSQL_TYPE_GEOMETRY); return field->get_geometry_type(); } + bool check_index_dependence(void *arg); friend class Item_default_value; friend class Item_insert_value; friend class st_select_lex_unit; diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index c899236e336..cea00097665 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -159,9 +159,12 @@ void TABLE::prune_range_rowid_filters() Range_rowid_filter_cost_info **filter_ptr_2= filter_ptr_1 + 1; for (j= i+1; j < range_rowid_filter_cost_info_elems; j++, filter_ptr_2++) { - key_map map= key_info[key_no].overlapped; - map.intersect(key_info[(*filter_ptr_2)->key_no].overlapped); - if (map.is_clear_all()) + key_map map_1= key_info[key_no].overlapped; + map_1.merge(key_info[key_no].constraint_correlated); + key_map map_2= key_info[(*filter_ptr_2)->key_no].overlapped; + map_2.merge(key_info[(*filter_ptr_2)->key_no].constraint_correlated); + map_1.intersect(map_2); + if (map_1.is_clear_all()) { (*filter_ptr_1)->abs_independent.set_bit((*filter_ptr_2)->key_no); (*filter_ptr_2)->abs_independent.set_bit(key_no); @@ -390,7 +393,8 @@ TABLE::best_range_rowid_filter_for_partial_join(uint access_key_no, Range_rowid_filter_cost_info *best_filter= 0; double best_filter_gain= 0; - key_map *overlapped= &key_info[access_key_no].overlapped; + key_map no_filter_usage= key_info[access_key_no].overlapped; + no_filter_usage.merge(key_info[access_key_no].constraint_correlated); for (uint i= 0; i < range_rowid_filter_cost_info_elems ; i++) { double curr_gain = 0; @@ -401,7 +405,7 @@ TABLE::best_range_rowid_filter_for_partial_join(uint access_key_no, the index by which the table is accessed */ if ((filter->key_no == access_key_no) || - overlapped->is_set(filter->key_no)) + no_filter_usage.is_set(filter->key_no)) continue; if (records < filter->cross_x) diff --git a/sql/structs.h b/sql/structs.h index 8aec29bac41..3628cd01748 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -124,6 +124,8 @@ typedef struct st_key { (only key parts from key definitions are taken into account) */ key_map overlapped; + /* Set of keys constraint correlated with this key */ + key_map constraint_correlated; LEX_CSTRING name; uint block_size; enum ha_key_alg algorithm; diff --git a/sql/table.cc b/sql/table.cc index 6676ce5447d..c96a8db57e7 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -1194,6 +1194,8 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table, goto end; } + table->find_constraint_correlated_indexes(); + res=0; end: thd->restore_active_arena(table->expr_arena, &backup_arena); @@ -1274,6 +1276,80 @@ void TABLE_SHARE::set_overlapped_keys() } +bool Item_field::check_index_dependence(void *arg) +{ + TABLE *table= (TABLE *)arg; + + KEY *key= table->key_info; + for (uint j= 0; j < table->s->keys; j++, key++) + { + if (table->constraint_dependent_keys.is_set(j)) + continue; + + KEY_PART_INFO *key_part= key->key_part; + uint n= key->user_defined_key_parts; + + for (uint k= 0; k < n; k++, key_part++) + { + if (this->field == key_part->field) + { + table->constraint_dependent_keys.set_bit(j); + break; + } + } + } + return false; +} + + +/** + @brief + Find keys that occur in the same constraint on this table + + @details + Constraints on this table are checked only. + + The method goes through constraints list trying to find at + least two keys which parts participate in some constraint. + These keys are called constraint correlated. + + Each key has its own key map with the information about with + which keys it is constraint correlated. Bit in this map is set + only if keys are constraint correlated. + This method fills each keys constraint correlated key map. +*/ + +void TABLE::find_constraint_correlated_indexes() +{ + if (s->keys == 0) + return; + + KEY *key= key_info; + for (uint i= 0; i < s->keys; i++, key++) + { + key->constraint_correlated.clear_all(); + key->constraint_correlated.set_bit(i); + } + + if (!check_constraints) + return; + + for (Virtual_column_info **chk= check_constraints ; *chk ; chk++) + { + constraint_dependent_keys.clear_all(); + (*chk)->expr->walk(&Item::check_index_dependence, 0, this); + + if (constraint_dependent_keys.bits_set() <= 1) + continue; + + uint key_no= 0; + key_map::Iterator ki(constraint_dependent_keys); + while ((key_no= ki++) != key_map::Iterator::BITMAP_END) + key_info[key_no].constraint_correlated.merge(constraint_dependent_keys); + } +} + + /** Read data from a binary .frm file image into a TABLE_SHARE diff --git a/sql/table.h b/sql/table.h index 0a2dc63b068..377176fd10e 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1133,6 +1133,8 @@ struct TABLE key_map keys_in_use_for_group_by; /* Map of keys that can be used to calculate ORDER BY without sorting */ key_map keys_in_use_for_order_by; + /* Map of keys dependent on some constraint */ + key_map constraint_dependent_keys; KEY *key_info; /* data of keys in database */ Field **field; /* Pointer to fields */ @@ -1566,6 +1568,7 @@ struct TABLE int delete_row(); void vers_update_fields(); void vers_update_end(); + void find_constraint_correlated_indexes(); /** Number of additional fields used in versioned tables */ #define VERSIONING_FIELDS 2