mirror of
https://github.com/MariaDB/server.git
synced 2025-12-11 14:55:44 +01:00
8916aeed28
(Patch provided by Monty, Testcase by Rex Johnston) get_tmp_table_costs() computes the cost of using a temporary (work table) for certain cases, including semi-join subquery materialization. The computed cost value was very low, it used this formula: key_lookup_cost * (disk_read_ratio= 0.02) Use the correct formula: key_lookup_cost // Index lookup is always done + disk_read_cost * disk_read_ratio disk_read_cost is incurred when the lookup has to go to disk. We assume this doesn't occur for every lookup. It happens only with disk_read_ratio=0.02 frequency. The fix is controlled by @@new_mode='FIX_DISK_TMPTABLE_COSTS' flag. It is OFF by default in this patch.
271 lines
9.4 KiB
Text
271 lines
9.4 KiB
Text
create table t1 (a int primary key, b int, c int, d int, e int, key ba (b,a), key bda (b,d,a), key cba (c,b,a), key cb (c,b), key d (d)) engine=aria;
|
|
insert into t1 select seq,seq,seq,seq,seq from seq_1_to_10;
|
|
insert into t1 values(20,2,2,2,2),(21,3,4,5,6);
|
|
#
|
|
# Get different scan costs
|
|
#
|
|
explain select sum(e) as "table_scan" from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 12
|
|
Last_query_cost 0.012556
|
|
explain select sum(a) as "index scan" from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL PRIMARY 4 NULL 12 Using index
|
|
Last_query_cost 0.007441
|
|
#
|
|
# Range scans should be used if we don't examine all rows in the table
|
|
#
|
|
explain select count(a) from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
|
|
Last_query_cost 0.000000
|
|
explain select count(*) from t1 where a > 0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 12 Using where; Using index
|
|
Last_query_cost 0.002877
|
|
explain select count(*) from t1 where a > 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 12 Using where; Using index
|
|
Last_query_cost 0.002877
|
|
explain select count(*) from t1 where a > 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 11 Using where; Using index
|
|
Last_query_cost 0.002747
|
|
#
|
|
# Shorter indexes are prefered over longer indexs
|
|
#
|
|
explain select sum(a+b) from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL ba 9 NULL 12 Using index
|
|
Last_query_cost 0.007441
|
|
explain select count(*) from t1 where b between 5 and 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range ba,bda ba 5 NULL 6 Using where; Using index
|
|
Last_query_cost 0.002097
|
|
explain select sum(b+c) from t1 where b between 5 and 6 and c between 5 and 6;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range ba,bda,cba,cb cba 10 NULL 2 Using where; Using index
|
|
Last_query_cost 0.001577
|
|
# Cost of 'd' should be slightly smaller as key 'ba' is longer than 'd'
|
|
explain select count(*) from t1 where b > 6;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range ba,bda ba 5 NULL 5 Using where; Using index
|
|
Last_query_cost 0.001967
|
|
explain select count(*) from t1 where d > 6;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range d d 5 NULL 5 Using where; Using index
|
|
Last_query_cost 0.001967
|
|
#
|
|
# Check covering index usage
|
|
#
|
|
explain select a,b,c from t1 where a=b;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL cba 14 NULL 12 Using where; Using index
|
|
Last_query_cost 0.007441
|
|
#
|
|
# Prefer ref keys over ranges
|
|
#
|
|
explain select count(*) from t1 where b=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref ba,bda ba 5 const 2 Using index
|
|
Last_query_cost 0.001141
|
|
explain select count(*) from t1 where b=2 and c=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref ba,bda,cba,cb cba 10 const,const 2 Using index
|
|
Last_query_cost 0.001141
|
|
explain select count(*) from t1 where b=3 and c between 3 and 4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range ba,bda,cba,cb cba 10 NULL 2 Using where; Using index
|
|
Last_query_cost 0.001577
|
|
#
|
|
# Prefer eq keys over ref keys
|
|
#
|
|
explain select a,b,e from t1 where a=10 or a=11;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition
|
|
Last_query_cost 0.003126
|
|
explain select a,b,e from t1 where d=10 or d=11;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range d d 5 NULL 2 Using index condition
|
|
Last_query_cost 0.003291
|
|
drop table t1;
|
|
#
|
|
# MDEV-30328 Assertion `avg_io_cost != 0.0 || index_cost.io + row_cost.io == 0' failed in
|
|
# Cost_estimate::total_cost()
|
|
#
|
|
set @save=@@InnoDB.optimizer_disk_read_ratio;
|
|
set global InnoDB.optimizer_disk_read_ratio=0;
|
|
create table t1 (
|
|
`l_orderkey` int(11) NOT NULL,
|
|
`l_partkey` int(11) DEFAULT NULL,
|
|
`l_suppkey` int(11) DEFAULT NULL,
|
|
`l_linenumber` int(11) NOT NULL,
|
|
`l_extra` int(11) NOT NULL,
|
|
`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`),
|
|
UNIQUE (`l_linenumber`),
|
|
UNIQUE (`l_extra`) ,
|
|
KEY `l_suppkey` (l_suppkey, l_partkey),
|
|
KEY `long_suppkey` (l_partkey, l_suppkey, l_linenumber, l_extra) )
|
|
ENGINE= InnoDB;
|
|
explain select count(*) from test.t1 force index (l_suppkey) where l_suppkey >= 0 and l_partkey >=0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range l_suppkey l_suppkey 10 NULL 1 Using where; Using index
|
|
drop table t1;
|
|
set global InnoDB.optimizer_disk_read_ratio=@save;
|
|
#
|
|
# MDEV-37723 In MDEV-36861, analyze Q4
|
|
# Alter disk based tmp table lookup cost formula
|
|
#
|
|
create table t1 (a int primary key,
|
|
b date, c char(15),
|
|
d blob,
|
|
key t1_ix1 (b)) ENGINE= InnoDB;
|
|
create table t2 (e int not null,
|
|
f int not null,
|
|
g date, h date,
|
|
primary key (e, f)) ENGINE= InnoDB;
|
|
insert into t1 select seq, date('1993-06-01') + interval seq second,
|
|
chr(65+mod(seq, 4)), NULL from seq_1_to_7000;
|
|
insert into t2 select a.seq, b.seq, date('1993-06-01') + interval b.seq day,
|
|
if (mod(a.seq,2), date('1993-06-01') + interval b.seq+1 day,
|
|
date('1993-06-01') - interval b.seq-1 day)
|
|
from seq_1_to_7000 a, seq_1_to_3 b;
|
|
set
|
|
@save_mhts= @@max_heap_table_size,
|
|
@@max_heap_table_size=16384;
|
|
set @save_new_mode=@@new_mode;
|
|
set new_mode=CONCAT(@@new_mode, ',FIX_DISK_TMPTABLE_COSTS');
|
|
# This should use: t1, FirstMatch(t2)
|
|
explain format=json
|
|
select c, count(*) as dc
|
|
from t1
|
|
where
|
|
b >= date '1993-06-01' and b < date '1993-06-01' + interval '3' month and
|
|
exists (select * from t2 where e = t1.a and g < h)
|
|
group by c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": 12.53886553,
|
|
"filesort": {
|
|
"sort_key": "t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": ["PRIMARY", "t1_ix1"],
|
|
"loops": 1,
|
|
"rows": 7000,
|
|
"cost": 1.1518548,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b >= DATE'1993-06-01' and t1.b < <cache>(DATE'1993-06-01' + interval '3' month)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ref",
|
|
"possible_keys": ["PRIMARY"],
|
|
"key": "PRIMARY",
|
|
"key_length": "4",
|
|
"used_key_parts": ["e"],
|
|
"ref": ["test.t1.a"],
|
|
"loops": 7000,
|
|
"rows": 1,
|
|
"cost": 6.7528092,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.g < t2.h",
|
|
"first_match": "t1"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
SET new_mode = REPLACE(@@new_mode, 'FIX_DISK_TMPTABLE_COSTS', '');
|
|
# This should use: t1, SJ-Materialization(t2)
|
|
explain format=json
|
|
select c, count(*) as dc
|
|
from t1
|
|
where
|
|
b >= date '1993-06-01' and b < date '1993-06-01' + interval '3' month and
|
|
exists (select * from t2 where e = t1.a and g < h)
|
|
group by c;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": 12.39219425,
|
|
"filesort": {
|
|
"sort_key": "t1.c",
|
|
"temporary_table": {
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1",
|
|
"access_type": "ALL",
|
|
"possible_keys": ["PRIMARY", "t1_ix1"],
|
|
"loops": 1,
|
|
"rows": 7000,
|
|
"cost": 1.1518548,
|
|
"filtered": 100,
|
|
"attached_condition": "t1.b >= DATE'1993-06-01' and t1.b < <cache>(DATE'1993-06-01' + interval '3' month)"
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "<subquery2>",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": ["distinct_key"],
|
|
"key": "distinct_key",
|
|
"key_length": "4",
|
|
"used_key_parts": ["e"],
|
|
"ref": ["func"],
|
|
"rows": 1,
|
|
"filtered": 100,
|
|
"materialized": {
|
|
"unique": 1,
|
|
"query_block": {
|
|
"select_id": 2,
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t2",
|
|
"access_type": "ALL",
|
|
"possible_keys": ["PRIMARY"],
|
|
"loops": 1,
|
|
"rows": 21000,
|
|
"cost": 3.4338548,
|
|
"filtered": 100,
|
|
"attached_condition": "t2.g < t2.h"
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
set new_mode=@save_new_mode;
|
|
set max_heap_table_size=@save_mhts;
|
|
drop table t1, t2;
|