1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
#
# Bug#35120119 Hypergraph: rescan_cost for hash-join is too low
#
CREATE TABLE t (x INT);
INSERT INTO t VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
CREATE TABLE t1 (x INT);
CREATE TABLE t2 (x INT);
CREATE TABLE t3 (x INT);
INSERT INTO t1 SELECT 10*tens.x + ones.x FROM t AS ones, t AS tens;
INSERT INTO t2 SELECT 10*tens.x + ones.x FROM t AS ones, t AS tens;
INSERT INTO t3 SELECT 10*tens.x + ones.x FROM t AS ones, t AS tens;
SET optimizer_switch = 'hypergraph_optimizer=on';
SET DEBUG='+d,subplan_tokens,force_subplan_0xe22c12cb96055ddc,force_subplan_0x8ab90bde1dd6f68c';
SET @old_join_buffer_size = @@join_buffer_size;
SET SESSION join_buffer_size = 128;
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.x = t2.x JOIN t3 ON t2.x = t3.x;
EXPLAIN
-> [0x4851cb7a4071c70b] Nested loop inner join (cost=1.91..19050 rows=10000)
-> [0xeed2c0bd3e39ba93] Table scan on t1 (cost=0.0025..0.25 rows=100)
-> [0xd2475c90e2c07330] Filter: (t1.x = t3.x) (cost=12.1..191 rows=100)
-> [0x8ab90bde1dd6f68c] Inner hash join (t2.x = t3.x) (cost=10.3..90.5 rows=1000)
-> [0x079e429c703ec298] Table scan on t2 (cost=0.0025..0.25 rows=100)
-> Hash
-> [0xfc0c3330f16b65b0] Table scan on t3 (cost=0.0025..0.25 rows=100)
To force this plan, use:
SET DEBUG='+d,subplan_tokens,force_subplan_0xeed2c0bd3e39ba93,force_subplan_0x079e429c703ec298,force_subplan_0xfc0c3330f16b65b0,force_subplan_0x8ab90bde1dd6f68c,force_subplan_0xd2475c90e2c07330,force_subplan_0x4851cb7a4071c70b';
SET SESSION join_buffer_size = 1024;
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.x = t2.x JOIN t3 ON t2.x = t3.x;
EXPLAIN
-> [0x4851cb7a4071c70b] Nested loop inner join (cost=1.91..19050 rows=10000)
-> [0xeed2c0bd3e39ba93] Table scan on t1 (cost=0.0025..0.25 rows=100)
-> [0xd2475c90e2c07330] Filter: (t1.x = t3.x) (cost=12.1..191 rows=100)
-> [0x8ab90bde1dd6f68c] Inner hash join (t2.x = t3.x) (cost=10.3..90.5 rows=1000)
-> [0x079e429c703ec298] Table scan on t2 (cost=0.0025..0.25 rows=100)
-> Hash
-> [0xfc0c3330f16b65b0] Table scan on t3 (cost=0.0025..0.25 rows=100)
To force this plan, use:
SET DEBUG='+d,subplan_tokens,force_subplan_0xeed2c0bd3e39ba93,force_subplan_0x079e429c703ec298,force_subplan_0xfc0c3330f16b65b0,force_subplan_0x8ab90bde1dd6f68c,force_subplan_0xd2475c90e2c07330,force_subplan_0x4851cb7a4071c70b';
SET SESSION join_buffer_size = 8192;
EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.x = t2.x JOIN t3 ON t2.x = t3.x;
EXPLAIN
-> [0x4851cb7a4071c70b] Nested loop inner join (cost=1.82..18154 rows=10000)
-> [0xeed2c0bd3e39ba93] Table scan on t1 (cost=0.0025..0.25 rows=100)
-> [0xd2475c90e2c07330] Filter: (t1.x = t3.x) (cost=12.1..191 rows=100)
-> [0x8ab90bde1dd6f68c] Inner hash join (t2.x = t3.x) (cost=10.3..90.5 rows=1000)
-> [0x079e429c703ec298] Table scan on t2 (cost=0.0025..0.25 rows=100)
-> Hash
-> [0xfc0c3330f16b65b0] Table scan on t3 (cost=0.0025..0.25 rows=100)
To force this plan, use:
SET DEBUG='+d,subplan_tokens,force_subplan_0xeed2c0bd3e39ba93,force_subplan_0x079e429c703ec298,force_subplan_0xfc0c3330f16b65b0,force_subplan_0x8ab90bde1dd6f68c,force_subplan_0xd2475c90e2c07330,force_subplan_0x4851cb7a4071c70b';
SET DEBUG='-d,subplan_tokens,force_subplan_0xe22c12cb96055ddc,force_subplan_0x8ab90bde1dd6f68c';
SET join_buffer_size = @old_join_buffer_size;
DROP TABLE t, t1, t2, t3;
|
