mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
cd03bf5c53
MDEV-35958 Cost estimates for materialized derived tables are poor
(Backport 11.8->11.4, the same patch)
Estimate_scan_time() calculates the cost of scanning a derivied table.
The old code did not take into account that the temporary table heap table
may be converted to Aria.
Things fixed:
- Added checking if the temporary tables data will fit in the heap.
If not, then calculate the cost based on the designated internal
temporary table engine (Aria).
- Removed MY_MAX(records, 1000) and instead trust the optimizer's
estimate of records. This reduces the cost of temporary tables a bit
for small tables, which caused a few changes in mtr results.
- Fixed cost calculation for HEAP.
- HEAP costs->row_next_find_cost was not set. This does not affect old
costs calculation as this cost slot was not used anywhere.
Now HEAP cost->row_next_find_cost is set, which allowed me to remove
some duplicated computation in ha_heap::scan_time()
Reviewed by: Sergei Petrunia <sergey@mariadb.com>
347 lines
14 KiB
Text
347 lines
14 KiB
Text
select table_name,engine from information_schema.tables where table_name="optimizer_costs";
|
|
table_name engine
|
|
OPTIMIZER_COSTS MEMORY
|
|
show create table information_schema.optimizer_costs;
|
|
Table Create Table
|
|
OPTIMIZER_COSTS CREATE TEMPORARY TABLE `OPTIMIZER_COSTS` (
|
|
`ENGINE` varchar(192) NOT NULL,
|
|
`OPTIMIZER_DISK_READ_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_INDEX_BLOCK_COPY_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_KEY_COMPARE_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_KEY_COPY_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_KEY_LOOKUP_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_KEY_NEXT_FIND_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_DISK_READ_RATIO` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_ROW_COPY_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_ROW_LOOKUP_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_ROW_NEXT_FIND_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_ROWID_COMPARE_COST` decimal(9,6) NOT NULL,
|
|
`OPTIMIZER_ROWID_COPY_COST` decimal(9,6) NOT NULL
|
|
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
|
select * from information_schema.optimizer_costs where engine in
|
|
("memory","innodb","aria","default") order by engine;
|
|
ENGINE Aria
|
|
OPTIMIZER_DISK_READ_COST 10.240000
|
|
OPTIMIZER_INDEX_BLOCK_COPY_COST 0.035600
|
|
OPTIMIZER_KEY_COMPARE_COST 0.011361
|
|
OPTIMIZER_KEY_COPY_COST 0.015685
|
|
OPTIMIZER_KEY_LOOKUP_COST 0.435777
|
|
OPTIMIZER_KEY_NEXT_FIND_COST 0.082347
|
|
OPTIMIZER_DISK_READ_RATIO 0.020000
|
|
OPTIMIZER_ROW_COPY_COST 0.060866
|
|
OPTIMIZER_ROW_LOOKUP_COST 0.130839
|
|
OPTIMIZER_ROW_NEXT_FIND_COST 0.045916
|
|
OPTIMIZER_ROWID_COMPARE_COST 0.002653
|
|
OPTIMIZER_ROWID_COPY_COST 0.002653
|
|
ENGINE default
|
|
OPTIMIZER_DISK_READ_COST 10.240000
|
|
OPTIMIZER_INDEX_BLOCK_COPY_COST 0.035600
|
|
OPTIMIZER_KEY_COMPARE_COST 0.011361
|
|
OPTIMIZER_KEY_COPY_COST 0.015685
|
|
OPTIMIZER_KEY_LOOKUP_COST 0.435777
|
|
OPTIMIZER_KEY_NEXT_FIND_COST 0.082347
|
|
OPTIMIZER_DISK_READ_RATIO 0.020000
|
|
OPTIMIZER_ROW_COPY_COST 0.060866
|
|
OPTIMIZER_ROW_LOOKUP_COST 0.130839
|
|
OPTIMIZER_ROW_NEXT_FIND_COST 0.045916
|
|
OPTIMIZER_ROWID_COMPARE_COST 0.002653
|
|
OPTIMIZER_ROWID_COPY_COST 0.002653
|
|
ENGINE InnoDB
|
|
OPTIMIZER_DISK_READ_COST 10.240000
|
|
OPTIMIZER_INDEX_BLOCK_COPY_COST 0.035600
|
|
OPTIMIZER_KEY_COMPARE_COST 0.011361
|
|
OPTIMIZER_KEY_COPY_COST 0.015685
|
|
OPTIMIZER_KEY_LOOKUP_COST 0.791120
|
|
OPTIMIZER_KEY_NEXT_FIND_COST 0.099000
|
|
OPTIMIZER_DISK_READ_RATIO 0.020000
|
|
OPTIMIZER_ROW_COPY_COST 0.060870
|
|
OPTIMIZER_ROW_LOOKUP_COST 0.765970
|
|
OPTIMIZER_ROW_NEXT_FIND_COST 0.070130
|
|
OPTIMIZER_ROWID_COMPARE_COST 0.002653
|
|
OPTIMIZER_ROWID_COPY_COST 0.002653
|
|
ENGINE MEMORY
|
|
OPTIMIZER_DISK_READ_COST 0.000000
|
|
OPTIMIZER_INDEX_BLOCK_COPY_COST 0.000000
|
|
OPTIMIZER_KEY_COMPARE_COST 0.011361
|
|
OPTIMIZER_KEY_COPY_COST 0.000000
|
|
OPTIMIZER_KEY_LOOKUP_COST 0.000000
|
|
OPTIMIZER_KEY_NEXT_FIND_COST 0.000000
|
|
OPTIMIZER_DISK_READ_RATIO 0.000000
|
|
OPTIMIZER_ROW_COPY_COST 0.002334
|
|
OPTIMIZER_ROW_LOOKUP_COST 0.000000
|
|
OPTIMIZER_ROW_NEXT_FIND_COST 0.008017
|
|
OPTIMIZER_ROWID_COMPARE_COST 0.002653
|
|
OPTIMIZER_ROWID_COPY_COST 0.002653
|
|
show variables like "optimizer%cost";
|
|
Variable_name Value
|
|
optimizer_disk_read_cost 10.240000
|
|
optimizer_index_block_copy_cost 0.035600
|
|
optimizer_key_compare_cost 0.011361
|
|
optimizer_key_copy_cost 0.015685
|
|
optimizer_key_lookup_cost 0.435777
|
|
optimizer_key_next_find_cost 0.082347
|
|
optimizer_row_copy_cost 0.060866
|
|
optimizer_row_lookup_cost 0.130839
|
|
optimizer_row_next_find_cost 0.045916
|
|
optimizer_rowid_compare_cost 0.002653
|
|
optimizer_rowid_copy_cost 0.002653
|
|
optimizer_scan_setup_cost 10.000000
|
|
optimizer_where_cost 0.032000
|
|
show variables like "optimizer_disk_read_ratio";
|
|
Variable_name Value
|
|
optimizer_disk_read_ratio 0.020000
|
|
#
|
|
# Test change some 'default' variables
|
|
#
|
|
SELECT @@optimizer_disk_read_ratio,@@optimizer_index_block_copy_cost;
|
|
@@optimizer_disk_read_ratio @@optimizer_index_block_copy_cost
|
|
0.020000 0.035600
|
|
SET global optimizer_disk_read_ratio=0.8;
|
|
SET global optimizer_index_block_copy_cost=0.1;
|
|
SELECT @@optimizer_disk_read_ratio,@@optimizer_index_block_copy_cost;
|
|
@@optimizer_disk_read_ratio @@optimizer_index_block_copy_cost
|
|
0.800000 0.100000
|
|
select optimizer_disk_read_ratio,optimizer_index_block_copy_cost from information_schema.optimizer_costs where engine='default';
|
|
optimizer_disk_read_ratio optimizer_index_block_copy_cost
|
|
0.800000 0.100000
|
|
SET global optimizer_disk_read_ratio=default;
|
|
SET global optimizer_index_block_copy_cost=default;
|
|
SELECT @@optimizer_disk_read_ratio,@@optimizer_index_block_copy_cost;
|
|
@@optimizer_disk_read_ratio @@optimizer_index_block_copy_cost
|
|
0.020000 0.035600
|
|
#
|
|
# Test change some 'engine' variables
|
|
#
|
|
select @@MEMORY.optimizer_row_lookup_cost;
|
|
@@MEMORY.optimizer_row_lookup_cost
|
|
0.000000
|
|
set @tmp=@@MEMORY.optimizer_row_lookup_cost;
|
|
set @@global.MEMORY.optimizer_row_lookup_cost=1;
|
|
select @@MEMORY.optimizer_row_lookup_cost;
|
|
@@MEMORY.optimizer_row_lookup_cost
|
|
1.000000
|
|
set @@global.MEMORY.optimizer_row_lookup_cost=default;
|
|
select @@MEMORY.optimizer_row_lookup_cost;
|
|
@@MEMORY.optimizer_row_lookup_cost
|
|
0.130839
|
|
set @@global.MEMORY.optimizer_row_lookup_cost=@tmp;
|
|
select @@MEMORY.optimizer_row_lookup_cost;
|
|
@@MEMORY.optimizer_row_lookup_cost
|
|
0.000000
|
|
#
|
|
# Print variables with different syntaxes
|
|
#
|
|
SHOW VARIABLES like "optimizer_row_lookup_cost";
|
|
Variable_name Value
|
|
optimizer_row_lookup_cost 0.130839
|
|
SELECT @@optimizer_row_lookup_cost;
|
|
@@optimizer_row_lookup_cost
|
|
0.130839
|
|
SELECT @@global.default.optimizer_row_lookup_cost;
|
|
@@global.default.optimizer_row_lookup_cost
|
|
0.130839
|
|
SELECT @@global.default.`optimizer_row_lookup_cost`;
|
|
@@global.default.`optimizer_row_lookup_cost`
|
|
0.130839
|
|
SELECT @@MEMORY.optimizer_row_lookup_cost;
|
|
@@MEMORY.optimizer_row_lookup_cost
|
|
0.000000
|
|
SELECT @@memory.optimizer_row_lookup_cost;
|
|
@@memory.optimizer_row_lookup_cost
|
|
0.000000
|
|
SELECT @@InnoDB.optimizer_row_lookup_cost;
|
|
@@InnoDB.optimizer_row_lookup_cost
|
|
0.765970
|
|
#
|
|
# Accessing not existing cost
|
|
#
|
|
SELECT @@not_existing.optimizer_row_lookup_cost;
|
|
@@not_existing.optimizer_row_lookup_cost
|
|
0.130839
|
|
SELECT @@NOT_existing.optimizer_row_lookup_cost;
|
|
@@NOT_existing.optimizer_row_lookup_cost
|
|
0.130839
|
|
select engine from information_schema.optimizer_costs where engine like '%existing';
|
|
engine
|
|
#
|
|
# Creating a new cost structure
|
|
#
|
|
SET global new_engine.optimizer_disk_read_cost=100;
|
|
select * from information_schema.optimizer_costs where engine like 'new_engine';
|
|
ENGINE OPTIMIZER_DISK_READ_COST OPTIMIZER_INDEX_BLOCK_COPY_COST OPTIMIZER_KEY_COMPARE_COST OPTIMIZER_KEY_COPY_COST OPTIMIZER_KEY_LOOKUP_COST OPTIMIZER_KEY_NEXT_FIND_COST OPTIMIZER_DISK_READ_RATIO OPTIMIZER_ROW_COPY_COST OPTIMIZER_ROW_LOOKUP_COST OPTIMIZER_ROW_NEXT_FIND_COST OPTIMIZER_ROWID_COMPARE_COST OPTIMIZER_ROWID_COPY_COST
|
|
new_engine 100.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000 -1.000000
|
|
select @@new_engine.optimizer_disk_read_cost, @@new_engine.optimizer_row_copy_cost;
|
|
@@new_engine.optimizer_disk_read_cost @@new_engine.optimizer_row_copy_cost
|
|
100.000000 -1.000000
|
|
#
|
|
# Errors
|
|
#
|
|
SELECT @@default.optimizer_disk_read_cost;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'default.optimizer_disk_read_cost' at line 1
|
|
set global Aria.optimizer_disk_read_cost=NULL;
|
|
ERROR 42000: Incorrect argument type to variable 'optimizer_disk_read_cost'
|
|
set @tmp=@@Aria.optimizer_disk_read_cost;
|
|
SET global Aria.optimizer_disk_read_cost=-1;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect optimizer_disk_read_cost value: '-1'
|
|
select @@Aria.optimizer_disk_read_cost;
|
|
@@Aria.optimizer_disk_read_cost
|
|
0.000000
|
|
SET global Aria.optimizer_disk_read_cost=200000;
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect optimizer_disk_read_cost value: '200000'
|
|
select @@Aria.optimizer_disk_read_cost;
|
|
@@Aria.optimizer_disk_read_cost
|
|
10000.000000
|
|
set global Aria.optimizer_disk_read_cost=@tmp;
|
|
select @@Aria.optimizer_disk_read_cost;
|
|
@@Aria.optimizer_disk_read_cost
|
|
10.240000
|
|
#
|
|
# Test of cost of ref compared to table scan + join_cache
|
|
#
|
|
create or replace table t1 (p int primary key, a char(10)) engine=myisam;
|
|
create or replace table t2 (p int primary key, i int, a char(10), key k2(a)) engine=myisam;
|
|
insert into t2 select seq,seq,'a' from seq_1_to_512;
|
|
insert into t1 select seq,'a' from seq_1_to_4;
|
|
explain select count(*) from t1, t2 where t1.p = t2.i;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 512 Using where; Using join buffer (flat, BNL join)
|
|
insert into t1 select seq,'a' from seq_5_to_10;
|
|
explain select count(*) from t1, t2 where t1.p = t2.i;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 512 Using where
|
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.i 1 Using index
|
|
drop table t1,t2;
|
|
#
|
|
# Test of optimizer_scan_setup_cost
|
|
#
|
|
create table t1 (p int primary key, a char(10)) engine=myisam;
|
|
create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)) engine=myisam;
|
|
insert into t1 values (2, 'qqqq'), (11, 'yyyy');
|
|
insert into t2 values (1, 2, 'qqqq'), (2, 2, 'pppp'),
|
|
(3, 2, 'yyyy'), (4, 3, 'zzzz');
|
|
set @org_myisam_disk_read_ratio=@@myisam.optimizer_disk_read_ratio;
|
|
set @@optimizer_scan_setup_cost=10,@@global.myisam.optimizer_disk_read_ratio=0.2;
|
|
flush tables;
|
|
explain select sum(t2.p+length(t1.a)) from t1, t2 where t1.p = t2.i;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2
|
|
1 SIMPLE t2 ref k1 k1 5 test.t1.p 1
|
|
set @@optimizer_scan_setup_cost=0.0, @@global.myisam.optimizer_disk_read_ratio=0.0;
|
|
flush tables;
|
|
explain select sum(t2.p+length(t1.a)) from t1, t2 where t1.p = t2.i;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 2
|
|
1 SIMPLE t2 ALL k1 NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
|
|
set @@optimizer_scan_setup_cost=default,@@global.myisam.optimizer_disk_read_ratio=@org_myisam_disk_read_ratio;
|
|
flush tables;
|
|
drop table t1,t2;
|
|
#
|
|
# Test of group by optimization
|
|
#
|
|
set @@optimizer_scan_setup_cost=0;
|
|
CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) engine=myisam;
|
|
INSERT INTO t1 values (1,'2001-01-01'),(1,'2001-01-02'),
|
|
(1,'2001-01-03'),(1,'2001-01-04'),
|
|
(2,'2001-01-01'),(2,'2001-01-02'),
|
|
(2,'2001-01-03'),(2,'2001-01-04'),
|
|
(3,'2001-01-01'),(3,'2001-01-02'),
|
|
(3,'2001-01-03'),(3,'2001-01-04'),
|
|
(4,'2001-01-01'),(4,'2001-01-02'),
|
|
(4,'2001-01-03'),(4,'2001-01-04');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index NULL id 8 NULL 16 Using where; Using index
|
|
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
|
|
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
|
|
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
|
|
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range NULL id 8 NULL 5 Using where; Using index for group-by
|
|
drop table t1;
|
|
set @@optimizer_scan_setup_cost=default;
|
|
#
|
|
# Test of straight join costs
|
|
#
|
|
create table t1 (l_orderkey int(11) NOT NULL,
|
|
l_partkey int(11) DEFAULT NULL,
|
|
l_suppkey int(11) DEFAULT NULL,
|
|
PRIMARY KEY (l_orderkey)) engine=aria;
|
|
insert into t1 select seq,seq,seq from seq_1_to_1000;
|
|
explain select straight_join count(*) from seq_1_to_10000,t1 where seq=l_orderkey;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE seq_1_to_10000 index PRIMARY PRIMARY 8 NULL 10000 Using index
|
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.seq_1_to_10000.seq 1 Using where; Using index
|
|
show status like "last_query_cost";
|
|
Variable_name Value
|
|
Last_query_cost 5.641229
|
|
set @org_cost=@@aria.optimizer_key_next_find_cost;
|
|
set global aria.optimizer_key_next_find_cost=1000;
|
|
flush tables;
|
|
explain select count(*) from seq_1_to_10000,t1 where seq=l_orderkey;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE seq_1_to_10000 index PRIMARY PRIMARY 8 NULL 10000 Using index
|
|
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.seq_1_to_10000.seq 1 Using where; Using index
|
|
show status like "last_query_cost";
|
|
Variable_name Value
|
|
Last_query_cost 5.641229
|
|
set global aria.optimizer_key_next_find_cost=@org_cost;
|
|
drop table t1;
|
|
#
|
|
# Testing distinct group optimization
|
|
#
|
|
create table t1 (a int, b int, key(a,b));
|
|
insert into t1 select seq,seq from seq_1_to_1000;
|
|
explain select count(distinct a,b) from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range NULL a 10 NULL 1000 Using index for group-by (scanning)
|
|
explain select count(distinct a,b) from t1 where a>100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 10 NULL 901 Using where; Using index for group-by (scanning)
|
|
explain select count(distinct a,b) from t1 where a>800;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL 206 Using where; Using index
|
|
update t1 set a=mod(a,10);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
explain select count(distinct a,b) from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range NULL a 10 NULL 1000 Using index for group-by (scanning)
|
|
explain select count(distinct a,b) from t1 where a>1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 10 NULL 788 Using where; Using index for group-by (scanning)
|
|
explain select count(distinct a,b) from t1 where a>8;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 5 NULL 109 Using where; Using index
|
|
update t1 set b=mod(b,2);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
explain select count(distinct a,b) from t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range NULL a 10 NULL 11 Using index for group-by
|
|
explain select count(distinct a,b) from t1 where a>1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 10 NULL 9 Using where; Using index for group-by
|
|
explain select count(distinct a,b) from t1 where a>8;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range a a 10 NULL 1 Using where; Using index for group-by
|
|
drop table t1;
|
|
#
|
|
# cleanup
|
|
#
|
|
"New cost structures: 1 (should be 1)"
|