mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
31536b2477
After MDEV-30830 has added block-nl-join.r_unpack_time_ms, it became apparent that there is some unaccounted-for time in BNL join operation, namely the time that is spent after unpacking the join buffer record. Fix this by adding a Gap_time_tracker to track the time that is spent after unpacking the join buffer record and before any next time tracking. The collected time is printed in block-nl-join.r_other_time_ms. Reviewed by: Monty <monty@mariadb.org>
127 lines
2.4 KiB
Text
127 lines
2.4 KiB
Text
#
|
|
# Tests to check that r_something_time_ms is non-zero in
|
|
# ANALYZE FORMAT=JSON <statement>
|
|
#
|
|
--source include/default_optimizer_switch.inc
|
|
--source include/have_sequence.inc
|
|
|
|
# The tests here are large so that we get non-zero timings
|
|
--source include/big_test.inc
|
|
|
|
--echo #
|
|
--echo # MDEV-30830: ANALYZE FORMAT=JSON: r_unpack_time_ms is empty for the hashed joins
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # First, check a regular BNL-join
|
|
--echo #
|
|
create table t1 (
|
|
a int,
|
|
b int
|
|
);
|
|
insert into t1 select seq, seq/3 from seq_0_to_99;
|
|
|
|
create table t2 (
|
|
a int,
|
|
b int
|
|
);
|
|
insert into t2 select seq, seq/5 from seq_0_to_99;
|
|
|
|
let $out=`
|
|
analyze format=json
|
|
select * from t1, t2
|
|
where
|
|
t1.a < 700 and
|
|
t2.a < 100
|
|
and t1.b=t2.b
|
|
`;
|
|
|
|
evalp set @js='$out';
|
|
set @out=(select json_extract(@js,'$**.block-nl-join.r_unpack_time_ms'));
|
|
select cast(json_extract(@out,'$[0]') as DOUBLE) > 0;
|
|
drop table t1,t2;
|
|
|
|
--echo #
|
|
--echo # Now, check the hashed, BNL-H join
|
|
--echo #
|
|
create table t1 (
|
|
a int,
|
|
b int
|
|
);
|
|
insert into t1 select seq, seq/3 from seq_0_to_499;
|
|
|
|
create table t2 (
|
|
a int,
|
|
b int
|
|
);
|
|
insert into t2 select seq, seq/5 from seq_0_to_499;
|
|
set @tmp=@@join_cache_level, join_cache_level=6;
|
|
|
|
let $out=`
|
|
analyze format=json
|
|
select * from t1, t2
|
|
where
|
|
t1.a < 700 and
|
|
t2.a < 100
|
|
and t1.b=t2.b
|
|
`;
|
|
|
|
--source include/analyze-format.inc
|
|
evalp select '$out' as X;
|
|
|
|
set @out=(select json_extract(@js,'$**.block-nl-join.r_unpack_time_ms'));
|
|
select cast(json_extract(@out,'$[0]') as DOUBLE) > 0;
|
|
|
|
set join_cache_level=@tmp;
|
|
drop table t1, t2;
|
|
|
|
--echo #
|
|
--echo # MDEV-30972: ANALYZE FORMAT=JSON: some time is unaccounted-for in BNL-H join
|
|
--echo #
|
|
|
|
create table t1 (
|
|
a int,
|
|
col1 varchar(100),
|
|
col2 varchar(100),
|
|
col3 varchar(100)
|
|
);
|
|
|
|
insert into t1 select
|
|
seq/100,
|
|
concat('col1-', seq),
|
|
concat('col1-', seq),
|
|
concat('col1-', seq)
|
|
from seq_1_to_1000;
|
|
|
|
create table t2 (
|
|
a int,
|
|
col1 varchar(100),
|
|
col2 varchar(100),
|
|
col3 varchar(100)
|
|
);
|
|
|
|
insert into t2 select
|
|
seq/100,
|
|
concat('col1-', seq),
|
|
concat('col1-', seq),
|
|
concat('col1-', seq)
|
|
from seq_1_to_2000;
|
|
|
|
set @tmp=@@join_cache_level, join_cache_level=6;
|
|
|
|
let $out=`
|
|
analyze format=json
|
|
select * from t1, t2
|
|
where
|
|
t1.a=t2.a
|
|
and concat(t1.col1, t1.col2, t1.col3)= concat(t2.col1, t2.col2, t2.col3)
|
|
`;
|
|
--source include/analyze-format.inc
|
|
evalp select '$out' as X;
|
|
|
|
set @out=(select json_extract(@js,'$**.block-nl-join.r_other_time_ms'));
|
|
select cast(json_extract(@out,'$[0]') as DOUBLE) > 0;
|
|
|
|
set join_cache_level=@tmp;
|
|
drop table t1, t2;
|
|
|