mirror of
https://github.com/MariaDB/server.git
synced 2025-10-30 18:36:12 +01:00
0940a96940
Part#2, variant 2: Make the printed r_ values in JSON output consistent. After this patch, ANALYZE output has: - r_index_rows (NEW) - Observed number of rows before ICP or Rowid Filtering checks. This is a per-scan average. like r_rows and "rows" are. - r_rows (AS BEFORE) - Observed number of rows after ICP and Rowid Filtering. - r_icp_filtered (NEW) - Observed selectivity of ICP condition. - (AS BEFORE) observed selectivity of Rowid Filter is in $.rowid_filter.r_selectivity_pct - r_total_filtered - Observed combined selectivity: fraction of rows left after applying ICP condition, Rowid Filter, and attached_condition. This is now comparable with "filtered" and is printed right after it. - r_filtered (AS BEFORE) - Observed selectivity of "attached_condition". Tabular ANALYZE output is not changed. Note that JSON's r_filtered and r_rows have the same meanings as before and have the same meaning as in tabular output.
131 lines
3.8 KiB
Text
131 lines
3.8 KiB
Text
#
|
|
# MDEV-32351: Significant slowdown for query with many outer joins
|
|
#
|
|
CREATE TABLE t1 (b int NOT NULL, PRIMARY KEY (b)) ENGINE=MYISAM;
|
|
INSERT INTO t1 select seq from seq_1_to_10000;
|
|
CREATE TABLE t2 (b int NOT NULL, d varchar(255), KEY (b)) ENGINE=MYISAM ;
|
|
INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
|
|
CREATE TABLE t3 (c int NOT NULL, PRIMARY KEY (c)) ENGINE=MYISAM ;
|
|
INSERT INTO t3 select seq from seq_1_to_3000;
|
|
CREATE TABLE t4 (c int NOT NULL, PRIMARY KEY (c)) ENGINE=MYISAM;
|
|
INSERT INTO t4 select seq from seq_1_to_3000;
|
|
ANALYZE TABLE t1,t2,t3,t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status Engine-independent statistics collected
|
|
test.t3 analyze status OK
|
|
test.t4 analyze status Engine-independent statistics collected
|
|
test.t4 analyze status OK
|
|
create table t1_t2 as
|
|
select
|
|
t1.b as t1_b, t2.b as t2_b, t2.d as t2_d
|
|
FROM t1
|
|
LEFT JOIN t2 ON t1.b = t2.b;
|
|
SET statement debug_dbug='+d,analyze_print_r_unpack_ops' for
|
|
analyze
|
|
format=json
|
|
SELECT COUNT(*)
|
|
FROM t1_t2
|
|
LEFT JOIN t3 ON t2_d = t3.c
|
|
LEFT JOIN t4 ON t3.c=1
|
|
select '$js' as JSON;
|
|
JSON
|
|
{
|
|
"query_optimization": {
|
|
"r_total_time_ms": "REPLACED"
|
|
},
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost": "REPLACED",
|
|
"r_loops": 1,
|
|
"r_total_time_ms": "REPLACED",
|
|
"const_condition": "1",
|
|
"nested_loop": [
|
|
{
|
|
"table": {
|
|
"table_name": "t1_t2",
|
|
"access_type": "ALL",
|
|
"loops": 1,
|
|
"r_loops": 1,
|
|
"rows": 10000,
|
|
"r_rows": 10000,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_filtered": 100,
|
|
"r_filtered": 100
|
|
}
|
|
},
|
|
{
|
|
"table": {
|
|
"table_name": "t3",
|
|
"access_type": "eq_ref",
|
|
"possible_keys": ["PRIMARY"],
|
|
"key": "PRIMARY",
|
|
"key_length": "4",
|
|
"used_key_parts": ["c"],
|
|
"ref": ["test.t1_t2.t2_d"],
|
|
"loops": 10000,
|
|
"r_loops": 10000,
|
|
"r_table_loops": 3,
|
|
"rows": 1,
|
|
"r_rows": 0.0003,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_filtered": 100,
|
|
"attached_condition": "trigcond(t1_t2.t2_d = t3.c and trigcond(t1_t2.t2_d is not null))",
|
|
"r_filtered": 100,
|
|
"using_index": true
|
|
}
|
|
},
|
|
{
|
|
"block-nl-join": {
|
|
"table": {
|
|
"table_name": "t4",
|
|
"access_type": "index",
|
|
"key": "PRIMARY",
|
|
"key_length": "4",
|
|
"used_key_parts": ["c"],
|
|
"loops": 10000,
|
|
"r_loops": 1,
|
|
"rows": 3000,
|
|
"r_rows": 3000,
|
|
"cost": "REPLACED",
|
|
"r_table_time_ms": "REPLACED",
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_engine_stats": REPLACED,
|
|
"filtered": 100,
|
|
"r_total_filtered": 100,
|
|
"r_filtered": 100,
|
|
"using_index": true
|
|
},
|
|
"buffer_type": "flat",
|
|
"buffer_size": "256Kb",
|
|
"join_type": "BNL",
|
|
"attached_condition": "trigcond(trigcond(t3.c = 1))",
|
|
"r_loops": 10000,
|
|
"r_filtered": 0.04333,
|
|
"r_unpack_time_ms": "REPLACED",
|
|
"r_unpack_ops": 3000,
|
|
"r_other_time_ms": "REPLACED",
|
|
"r_effective_rows": 3000
|
|
}
|
|
}
|
|
]
|
|
}
|
|
}
|
|
# This must show 3000, not 30000000:
|
|
select json_extract('$js', '\$**.r_unpack_ops') as R_UNPACK_OPS;
|
|
R_UNPACK_OPS
|
|
[3000]
|
|
SET debug_dbug=@old_debug;
|
|
drop table t1,t2,t3,t4;
|
|
drop table t1_t2;
|