Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
Description
Take a test dataset from MDEV-7833. Let's run it so that join buffer is used:
analyze format=json select * from t1, t2 where t2.key1+1 between t1.lb and t1.rb;
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 49.407,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 3,
|
"r_rows": 3,
|
"r_total_time_ms": 0.2984,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1010,
|
"r_rows": 1010,
|
"r_total_time_ms": 20.465,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "flat",
|
"join_type": "BNL",
|
"attached_condition": "((t2.key1 + 1) between t1.lb and t1.rb)",
|
"r_filtered": 33.201
|
}
|
}
|
} |
|
Here, we see that t2.r_loops=1. This is the effect of join buffering.
However, it would be nice to see join what join buffer was used. (TODO: it is always the same, right? If it is a subquery, it allocates the buffer once and never frees, right? Check this)