SET SESSION STORAGE_ENGINE='InnoDB'; DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; CREATE INDEX i_l_quantity ON lineitem(l_quantity); CREATE INDEX i_o_totalprice ON orders(o_totalprice); set @save_use_stat_tables= @@use_stat_tables; set @@use_stat_tables=preferably; ANALYZE TABLE lineitem, orders; show create table lineitem; Table Create Table lineitem CREATE TABLE `lineitem` ( `l_orderkey` int(11) NOT NULL DEFAULT 0, `l_partkey` int(11) DEFAULT NULL, `l_suppkey` int(11) DEFAULT NULL, `l_linenumber` int(11) NOT NULL DEFAULT 0, `l_quantity` double DEFAULT NULL, `l_extendedprice` double DEFAULT NULL, `l_discount` double DEFAULT NULL, `l_tax` double DEFAULT NULL, `l_returnflag` char(1) DEFAULT NULL, `l_linestatus` char(1) DEFAULT NULL, `l_shipDATE` date DEFAULT NULL, `l_commitDATE` date DEFAULT NULL, `l_receiptDATE` date DEFAULT NULL, `l_shipinstruct` char(25) DEFAULT NULL, `l_shipmode` char(10) DEFAULT NULL, `l_comment` varchar(44) DEFAULT NULL, PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `i_l_shipdate` (`l_shipDATE`), KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), KEY `i_l_partkey` (`l_partkey`), KEY `i_l_suppkey` (`l_suppkey`), KEY `i_l_receiptdate` (`l_receiptDATE`), KEY `i_l_orderkey` (`l_orderkey`), KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), KEY `i_l_commitdate` (`l_commitDATE`), KEY `i_l_quantity` (`l_quantity`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 show create table orders; Table Create Table orders CREATE TABLE `orders` ( `o_orderkey` int(11) NOT NULL, `o_custkey` int(11) DEFAULT NULL, `o_orderstatus` char(1) DEFAULT NULL, `o_totalprice` double DEFAULT NULL, `o_orderDATE` date DEFAULT NULL, `o_orderpriority` char(15) DEFAULT NULL, `o_clerk` char(15) DEFAULT NULL, `o_shippriority` int(11) DEFAULT NULL, `o_comment` varchar(79) DEFAULT NULL, PRIMARY KEY (`o_orderkey`), KEY `i_o_orderdate` (`o_orderDATE`), KEY `i_o_custkey` (`o_custkey`), KEY `i_o_totalprice` (`o_totalprice`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 set optimizer_use_condition_selectivity=2; set statement optimizer_switch='rowid_filter=on' for EXPLAIN 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 Extra 1 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 510 (10%) 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; EXPLAIN { "query_block": { "select_id": 1, "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": 605, "selectivity_pct": 10.075 }, "rows": 510, "filtered": 10.075, "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 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 510 (10%) 60.00 (11%) 10.07 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": 605, "selectivity_pct": 10.075, "r_rows": 605, "r_selectivity_pct": 11.765, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, "r_loops": 1, "rows": 510, "r_rows": 60, "r_total_time_ms": "REPLACED", "filtered": 10.075, "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; l_orderkey l_linenumber l_shipdate l_quantity 1121 5 1997-04-27 47 1121 6 1997-04-21 50 1441 7 1997-06-07 50 1443 1 1997-02-05 47 1473 1 1997-05-05 50 1568 2 1997-04-06 46 1632 1 1997-01-25 47 1632 3 1997-01-29 47 1954 7 1997-06-04 49 1959 1 1997-05-05 46 2151 3 1997-01-20 49 2177 5 1997-05-10 46 2369 2 1997-01-02 47 2469 3 1997-01-11 48 2469 6 1997-03-03 49 2470 2 1997-06-02 50 260 1 1997-03-24 50 288 2 1997-04-19 49 289 4 1997-03-14 48 3009 1 1997-03-19 48 3105 3 1997-02-28 48 3106 2 1997-02-27 49 3429 1 1997-04-08 48 3490 2 1997-06-27 50 3619 1 1997-01-22 49 3619 3 1997-01-31 46 3969 3 1997-05-29 46 4005 4 1997-01-31 49 4036 1 1997-06-21 46 4066 4 1997-02-17 49 4098 1 1997-01-26 46 422 3 1997-06-21 46 4258 3 1997-01-02 46 4421 2 1997-04-21 46 4421 3 1997-05-25 46 4453 3 1997-05-29 48 4484 7 1997-03-17 50 4609 3 1997-02-11 46 484 1 1997-03-06 49 484 3 1997-01-24 50 484 5 1997-03-05 48 485 1 1997-03-28 50 4868 1 1997-04-29 47 4868 3 1997-04-23 49 4934 1 1997-05-20 48 4967 1 1997-05-27 50 5090 2 1997-04-05 46 5152 2 1997-03-10 50 5158 4 1997-04-10 49 5606 3 1997-03-11 46 5606 7 1997-02-01 46 5762 4 1997-03-02 47 581 3 1997-02-27 49 5829 5 1997-01-31 49 5831 4 1997-02-24 46 5895 2 1997-04-27 47 5895 3 1997-03-15 49 5952 1 1997-06-30 49 705 1 1997-04-18 46 836 3 1997-03-21 46 set statement optimizer_switch='rowid_filter=off' for EXPLAIN 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 Extra 1 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 510 Using index condition; Using where set statement optimizer_switch='rowid_filter=off' 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; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 510, "filtered": 10.075, "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 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 510 510.00 10.07 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": 510, "r_rows": 510, "r_total_time_ms": "REPLACED", "filtered": 10.075, "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; l_orderkey l_linenumber l_shipdate l_quantity 1121 5 1997-04-27 47 1121 6 1997-04-21 50 1441 7 1997-06-07 50 1443 1 1997-02-05 47 1473 1 1997-05-05 50 1568 2 1997-04-06 46 1632 1 1997-01-25 47 1632 3 1997-01-29 47 1954 7 1997-06-04 49 1959 1 1997-05-05 46 2151 3 1997-01-20 49 2177 5 1997-05-10 46 2369 2 1997-01-02 47 2469 3 1997-01-11 48 2469 6 1997-03-03 49 2470 2 1997-06-02 50 260 1 1997-03-24 50 288 2 1997-04-19 49 289 4 1997-03-14 48 3009 1 1997-03-19 48 3105 3 1997-02-28 48 3106 2 1997-02-27 49 3429 1 1997-04-08 48 3490 2 1997-06-27 50 3619 1 1997-01-22 49 3619 3 1997-01-31 46 3969 3 1997-05-29 46 4005 4 1997-01-31 49 4036 1 1997-06-21 46 4066 4 1997-02-17 49 4098 1 1997-01-26 46 422 3 1997-06-21 46 4258 3 1997-01-02 46 4421 2 1997-04-21 46 4421 3 1997-05-25 46 4453 3 1997-05-29 48 4484 7 1997-03-17 50 4609 3 1997-02-11 46 484 1 1997-03-06 49 484 3 1997-01-24 50 484 5 1997-03-05 48 485 1 1997-03-28 50 4868 1 1997-04-29 47 4868 3 1997-04-23 49 4934 1 1997-05-20 48 4967 1 1997-05-27 50 5090 2 1997-04-05 46 5152 2 1997-03-10 50 5158 4 1997-04-10 49 5606 3 1997-03-11 46 5606 7 1997-02-01 46 5762 4 1997-03-02 47 581 3 1997-02-27 49 5829 5 1997-01-31 49 5831 4 1997-02-24 46 5895 2 1997-04-27 47 5895 3 1997-03-15 49 5952 1 1997-06-30 49 705 1 1997-04-18 46 836 3 1997-03-21 46 set statement optimizer_switch='rowid_filter=on' for EXPLAIN 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 Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using where; Using index 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 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; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 98, "filtered": 100, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", "using_index": true }, "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": 4.7333, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } } 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 where; Using index 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.73 11.22 Using where 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, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", "using_index": true }, "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": 4.7333, "r_filtered": 11.224, "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 o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 6 1997-01-25 222274.54 484 3 1997-01-24 219920.62 5606 6 1997-01-11 219959.08 set statement optimizer_switch='rowid_filter=off' for EXPLAIN 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 Extra 1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using where; Using index 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 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; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 98, "filtered": 100, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", "using_index": true }, "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": 4.7333, "attached_condition": "orders.o_totalprice between 200000 and 230000" } } } 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 where; Using index 1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.73 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, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", "using_index": true }, "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": 4.7333, "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 o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 6 1997-01-25 222274.54 484 3 1997-01-24 219920.62 5606 6 1997-01-11 219959.08 set statement optimizer_switch='rowid_filter=on' for EXPLAIN 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 Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 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 l_quantity > 45 AND o_totalprice between 180000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 144, "filtered": 100, "attached_condition": "orders.o_totalprice between 180000 and 230000", "using_index": true }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": 0.8557, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" } } } 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 orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 144.00 100.00 100.00 Using where; Using index 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.62 0.86 1.68 Using where 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": "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": 144, "r_rows": 144, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "orders.o_totalprice between 180000 and 230000", "using_index": true }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 144, "rows": 4, "r_rows": 6.625, "r_total_time_ms": "REPLACED", "filtered": 0.8557, "r_filtered": 1.6771, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" } } } 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 l_quantity > 45 AND o_totalprice between 180000 and 230000; o_orderkey l_linenumber l_shipdate l_quantity o_totalprice 1632 1 1997-01-25 47 183286.33 1632 3 1997-01-29 47 183286.33 2177 5 1997-05-10 46 183493.42 2469 3 1997-01-11 48 192074.23 2469 6 1997-03-03 49 192074.23 3619 1 1997-01-22 49 222274.54 3619 3 1997-01-31 46 222274.54 484 1 1997-03-06 49 219920.62 484 3 1997-01-24 50 219920.62 484 5 1997-03-05 48 219920.62 4934 1 1997-05-20 48 180478.16 5606 3 1997-03-11 46 219959.08 5606 7 1997-02-01 46 219959.08 5829 5 1997-01-31 49 183734.56 5895 2 1997-04-27 47 201419.83 5895 3 1997-03-15 49 201419.83 set statement optimizer_switch='rowid_filter=off' for EXPLAIN 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 Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where set statement optimizer_switch='rowid_filter=off' 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 l_quantity > 45 AND o_totalprice between 180000 and 230000; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 144, "filtered": 100, "attached_condition": "orders.o_totalprice between 180000 and 230000", "using_index": true }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "rows": 4, "filtered": 0.8557, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" } } } 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 orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 144.00 100.00 100.00 Using where; Using index 1 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.62 0.86 1.68 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": "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": 144, "r_rows": 144, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "orders.o_totalprice between 180000 and 230000", "using_index": true }, "table": { "table_name": "lineitem", "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity", "i_l_quantity" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["l_orderkey"], "ref": ["dbt3_s001.orders.o_orderkey"], "r_loops": 144, "rows": 4, "r_rows": 6.625, "r_total_time_ms": "REPLACED", "filtered": 0.8557, "r_filtered": 1.6771, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" } } } 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 l_quantity > 45 AND o_totalprice between 180000 and 230000; o_orderkey l_linenumber l_shipdate l_quantity o_totalprice 1632 1 1997-01-25 47 183286.33 1632 3 1997-01-29 47 183286.33 2177 5 1997-05-10 46 183493.42 2469 3 1997-01-11 48 192074.23 2469 6 1997-03-03 49 192074.23 3619 1 1997-01-22 49 222274.54 3619 3 1997-01-31 46 222274.54 484 1 1997-03-06 49 219920.62 484 3 1997-01-24 50 219920.62 484 5 1997-03-05 48 219920.62 4934 1 1997-05-20 48 180478.16 5606 3 1997-03-11 46 219959.08 5606 7 1997-02-01 46 219959.08 5829 5 1997-01-31 49 183734.56 5895 2 1997-04-27 47 201419.83 5895 3 1997-03-15 49 201419.83 set statement optimizer_switch='rowid_filter=on' for EXPLAIN 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 Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 Using where; Using index 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_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; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 71, "filtered": 100, "attached_condition": "orders.o_totalprice between 200000 and 230000", "using_index": true }, "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": 8.4929, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" } } } 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 71 71.00 100.00 100.00 Using where; Using index 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.49 7.77 Using where 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": 71, "r_rows": 71, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "orders.o_totalprice between 200000 and 230000", "using_index": true }, "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.4929, "r_filtered": 7.7731, "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 o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 1890 1 1997-04-02 202364.58 1890 3 1997-02-09 202364.58 1890 4 1997-04-08 202364.58 1890 5 1997-04-15 202364.58 1890 6 1997-02-13 202364.58 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 4 1997-03-18 222274.54 3619 6 1997-01-25 222274.54 453 1 1997-06-30 216826.73 453 2 1997-06-30 216826.73 484 1 1997-03-06 219920.62 484 2 1997-04-09 219920.62 484 3 1997-01-24 219920.62 484 4 1997-04-29 219920.62 484 5 1997-03-05 219920.62 484 6 1997-04-06 219920.62 5606 2 1997-02-23 219959.08 5606 3 1997-03-11 219959.08 5606 4 1997-02-06 219959.08 5606 6 1997-01-11 219959.08 5606 7 1997-02-01 219959.08 5859 2 1997-05-15 210643.96 5859 5 1997-05-28 210643.96 5859 6 1997-06-15 210643.96 5895 1 1997-04-05 201419.83 5895 2 1997-04-27 201419.83 5895 3 1997-03-15 201419.83 5895 4 1997-03-03 201419.83 5895 5 1997-04-30 201419.83 5895 6 1997-04-19 201419.83 set statement optimizer_switch='rowid_filter=off' for EXPLAIN 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 Extra 1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 Using where; Using index 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_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; EXPLAIN { "query_block": { "select_id": 1, "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"], "rows": 71, "filtered": 100, "attached_condition": "orders.o_totalprice between 200000 and 230000", "using_index": true }, "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": 8.4929, "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" } } } 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 71 71.00 100.00 100.00 Using where; Using index 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.49 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": 71, "r_rows": 71, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, "attached_condition": "orders.o_totalprice between 200000 and 230000", "using_index": true }, "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.4929, "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 o_totalprice between 200000 and 230000; o_orderkey l_linenumber l_shipdate o_totalprice 1156 3 1997-01-24 217682.81 1156 4 1997-01-18 217682.81 1156 6 1997-01-27 217682.81 1156 7 1997-01-01 217682.81 1890 1 1997-04-02 202364.58 1890 3 1997-02-09 202364.58 1890 4 1997-04-08 202364.58 1890 5 1997-04-15 202364.58 1890 6 1997-02-13 202364.58 2180 2 1997-01-03 208481.57 2180 3 1997-01-03 208481.57 3619 1 1997-01-22 222274.54 3619 3 1997-01-31 222274.54 3619 4 1997-03-18 222274.54 3619 6 1997-01-25 222274.54 453 1 1997-06-30 216826.73 453 2 1997-06-30 216826.73 484 1 1997-03-06 219920.62 484 2 1997-04-09 219920.62 484 3 1997-01-24 219920.62 484 4 1997-04-29 219920.62 484 5 1997-03-05 219920.62 484 6 1997-04-06 219920.62 5606 2 1997-02-23 219959.08 5606 3 1997-03-11 219959.08 5606 4 1997-02-06 219959.08 5606 6 1997-01-11 219959.08 5606 7 1997-02-01 219959.08 5859 2 1997-05-15 210643.96 5859 5 1997-05-28 210643.96 5859 6 1997-06-15 210643.96 5895 1 1997-04-05 201419.83 5895 2 1997-04-27 201419.83 5895 3 1997-03-15 201419.83 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; use test; # # MDEV-18816: potential range filter for one join table with # impossible WHERE for another # create table t1 ( pk int not null primary key, c2 varchar(10) , i1 int,key (c2) ) engine=myisam; insert into t1 values (1,'a',-5),(2,'a',null); create table t2 ( pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) ) engine=myisam; insert into t2 values (1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), (7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), (13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); select 1 from t1 left join t2 join t1 as t1_a on t2.i1 = t1_a.pk on t1.c2 = t2.c1 where t1_a.pk is null and t1_a.i1 != 3; 1 explain extended select 1 from t1 left join t2 join t1 as t1_a on t2.i1 = t1_a.pk on t1.c2 = t2.c1 where t1_a.pk is null and t1_a.i1 != 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 drop table t1,t2; # # MDEV-18640: TABLE::prune_range_rowid_filters: Conditional jump or # move depends on uninitialized value # CREATE TABLE t1 ( pk INT, i INT, PRIMARY KEY (pk), KEY (pk,i) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,10), (7,70), (2,20); SELECT * FROM t1 WHERE pk < 5; pk i 1 10 2 20 DROP TABLE t1; # # MDEV-18956: Possible rowid filter for subquery for which # in_to_exists strategy has been chosen # CREATE TABLE t1 (pk int) engine=myisam ; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 ( pk int auto_increment PRIMARY KEY, i1 int, i2 int, c2 varchar(1), KEY (i1), KEY (i2) ) engine=myisam; INSERT INTO t2 VALUES (1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'), (6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'), (11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u'); SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); pk EXPLAIN EXTENDED SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; # # MDEV-19255: rowid range filter built for range condition # that uses in expensive subquery # CREATE TABLE t1 ( pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), (17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), (24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), (31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), (38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), (45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), (52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), (59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), (66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), (73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), (80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), (87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), (94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), (101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), (107,8,'z'),(108,3,'k'),(109,65,NULL); CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,1,'x'); INSERT INTO t2 SELECT * FROM t1; SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); pk1 a1 b1 pk2 a2 b2 65 2 a 109 65 NULL EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where 1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); EXPLAIN { "query_block": { "select_id": 1, "table": { "table_name": "t2", "access_type": "ALL", "rows": 101, "filtered": 100, "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" }, "table": { "table_name": "t1", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "b1"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk1"], "ref": ["test.t2.a2"], "rowid_filter": { "range": { "key": "b1", "used_key_parts": ["b1"] }, "rows": 87, "selectivity_pct": 87 }, "rows": 1, "filtered": 87, "attached_condition": "t1.b1 <= (subquery#2)" }, "subqueries": [ { "query_block": { "select_id": 2, "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk2"], "rows": 1, "filtered": 100, "index_condition": "t2.pk2 <= 1" } } } ] } } DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter # create table t1 ( pk int not null primary key, f1 varchar(10), f2 varchar(30), a int(10), key (f1), key (f2) ) engine=innodb; insert into t1 values (2,'a','a',2),(3,'a','a',null),(4,'a','a',55),(5,'a','a',4),(6,'a','a',0), (7,'a','a',1),(8,'a','a',4),(9,'a','a',null),(10,'a','a',0),(11,'a','a',0), (12,'a','a',null),(13,'a','a',49778),(14,'a','a',6),(15,'a','a',3), (16,'a','a',233),(17,'a','a',-1),(18,'a','a',5),(19,'a','a',-1), (20,'a','a',null),(21,'a','a',0),(22,'a','a',null),(23,'a','a',53840), (24,'a','a',null),(25,'a','a',null),(26,'a','a',5),(27,'a','a',43454), (28,'a','a',0),(29,'a','a',0),(30,'a','a',null),(59,'a','a',null), (60,'a','a',null),(61,'a','a',-1),(62,'a','a',null),(63,'a','a',0), (64,'a','a',14468),(65,'a','a',0),(66,'a','a',28),(67,'a','a',null), (68,'a','a',14983),(69,'a','a',null),(70,'a','a',3),(71,'a','a',null), (72,'a','a',null),(73,'a','a',237),(74,'a','a',2),(75,'a','a',0), (76,'a','a',6),(77,'a','a',5),(78,'a','a',0),(79,'a','a',1),(80,'a','a',-1), (81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null), (85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160), (89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null); ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); pk f1 f2 a explain ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index_merge f1,f2 f1,f2 13,33 NULL 1 Using intersect(f1,f2); Using where 2 UNION t1 index_merge f1,f2 f1,f2 13,33 NULL 1 Using intersect(f1,f2); Using where NULL UNION RESULT ALL NULL NULL NULL NULL NULL explain format=json ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) union ( select * from t1 where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); EXPLAIN { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "query_specifications": [ { "query_block": { "select_id": 1, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": ["f1", "f2"], "key_length": "13,33", "index_merge": { "intersect": { "range": { "key": "f1", "used_key_parts": ["f1"] }, "range": { "key": "f2", "used_key_parts": ["f2"] } } }, "rows": 1, "filtered": 1.5873, "attached_condition": "t1.f1 is null and t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" } } }, { "query_block": { "select_id": 2, "operation": "UNION", "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": ["f1", "f2"], "key_length": "13,33", "index_merge": { "intersect": { "range": { "key": "f1", "used_key_parts": ["f1"] }, "range": { "key": "f2", "used_key_parts": ["f2"] } } }, "rows": 1, "filtered": 1.5873, "attached_condition": "t1.f1 is null and t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" } } } ] } } } drop table t1; # # MDEV-19195: possible RORI-plan and possible plan with range filter # for not first joined table # create table t1 (id int not null primary key) engine=innodb; insert into t1 values (2),(1); create table t2 (y int,x int,index (x),index (y)) engine=innodb; insert into t2 values (4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1), (555,555),(666,1); select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; id y x 1 2 1 explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index 1 SIMPLE t2 index_merge x,y y,x 5,5 NULL 1 100.00 Using intersect(y,x); Using where; Using index Warnings: Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 drop table t1, t2; # # MDEV-19820: use of rowid filter for innodb table without primary key # create table t1 (a int, b int, key (b), key (a)) engine=innodb; insert into t1 select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK set @save_optimizer_switch= @@optimizer_switch; set optimizer_switch='rowid_filter=off'; select count(*) from t1 where a in (22,83,11) and b=2; count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref b,a b 5 const 59 3.30 Using where Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; a b 11 2 11 2 83 2 11 2 83 2 22 2 set optimizer_switch='rowid_filter=on'; select count(*) from t1 where a in (22,83,11) and b=2; count(*) 6 explain extended select count(*) from t1 where a in (22,83,11) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 3.30 Using where; Using rowid filter Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) select * from t1 where a in (22,83,11) and b=2; a b 11 2 11 2 83 2 11 2 83 2 22 2 drop table t1; set optimizer_switch=@save_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT;