-
Notifications
You must be signed in to change notification settings - Fork 3.8k
/
Copy pathfoo
152 lines (150 loc) · 7.96 KB
/
foo
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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
exec-ddl
CREATE TABLE customers (
id INT8 NOT NULL,
name STRING NOT NULL,
address STRING NULL,
CONSTRAINT "primary" PRIMARY KEY (id ASC),
FAMILY "primary" (id, name, address)
)
----
exec-ddl
CREATE TABLE orders (
id INT8 NOT NULL,
customer_id INT8 NULL,
status STRING NOT NULL,
CONSTRAINT "primary" PRIMARY KEY (id ASC),
CONSTRAINT fk_customer_id_ref_customers FOREIGN KEY (customer_id) REFERENCES customers(id),
INDEX orders_auto_index_fk_customer_id_ref_customers (customer_id ASC),
FAMILY "primary" (id, customer_id, status),
CONSTRAINT check_status CHECK (status IN ('open':::STRING, 'complete':::STRING, 'cancelled':::STRING))
)
----
optsteps
SELECT * FROM orders LEFT JOIN customers ON customer_id = customers.id
----
================================================================================
Initial expression
Cost: 2160.05
================================================================================
left-join (hash)
├── columns: id:1(int!null) customer_id:2(int) status:3(string!null) id:4(int) name:5(string) address:6(string)
├── key: (1,4)
├── fd: (1)-->(2,3), (4)-->(5,6)
├── scan orders
│ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
│ ├── key: (1)
│ └── fd: (1)-->(2,3)
├── scan customers
│ ├── columns: customers.id:4(int!null) name:5(string!null) address:6(string)
│ ├── key: (4)
│ └── fd: (4)-->(5,6)
└── filters
└── customer_id = customers.id [type=bool, outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ]), fd=(2)==(4), (4)==(2)]
--------------------------------------------------------------------------------
NormalizeInConst (no changes)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
GenerateIndexScans (no changes)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
GenerateIndexScans (no changes)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
CommuteLeftJoin (higher cost)
--------------------------------------------------------------------------------
-left-join (hash)
+right-join (hash)
├── columns: id:1(int!null) customer_id:2(int) status:3(string!null) id:4(int) name:5(string) address:6(string)
├── key: (1,4)
├── fd: (1)-->(2,3), (4)-->(5,6)
+ ├── scan customers
+ │ ├── columns: customers.id:4(int!null) name:5(string!null) address:6(string)
+ │ ├── key: (4)
+ │ └── fd: (4)-->(5,6)
├── scan orders
│ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
│ ├── key: (1)
│ └── fd: (1)-->(2,3)
- ├── scan customers
- │ ├── columns: customers.id:4(int!null) name:5(string!null) address:6(string)
- │ ├── key: (4)
- │ └── fd: (4)-->(5,6)
└── filters
└── customer_id = customers.id [type=bool, outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ]), fd=(2)==(4), (4)==(2)]
--------------------------------------------------------------------------------
GenerateMergeJoins (no changes)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
GenerateLookupJoins (higher cost)
--------------------------------------------------------------------------------
-left-join (hash)
+left-join (lookup customers)
├── columns: id:1(int!null) customer_id:2(int) status:3(string!null) id:4(int) name:5(string) address:6(string)
+ ├── key columns: [2] = [4]
+ ├── lookup columns are key
├── key: (1,4)
├── fd: (1)-->(2,3), (4)-->(5,6)
├── scan orders
│ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
│ ├── key: (1)
│ └── fd: (1)-->(2,3)
- ├── scan customers
- │ ├── columns: customers.id:4(int!null) name:5(string!null) address:6(string)
- │ ├── key: (4)
- │ └── fd: (4)-->(5,6)
- └── filters
- └── customer_id = customers.id [type=bool, outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ]), fd=(2)==(4), (4)==(2)]
+ └── filters (true)
--------------------------------------------------------------------------------
CommuteRightJoin (no changes)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
GenerateMergeJoins (higher cost)
--------------------------------------------------------------------------------
-left-join (hash)
+right-join (merge)
├── columns: id:1(int!null) customer_id:2(int) status:3(string!null) id:4(int) name:5(string) address:6(string)
+ ├── left ordering: +4
+ ├── right ordering: +2
├── key: (1,4)
├── fd: (1)-->(2,3), (4)-->(5,6)
- ├── scan orders
- │ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
- │ ├── key: (1)
- │ └── fd: (1)-->(2,3)
├── scan customers
│ ├── columns: customers.id:4(int!null) name:5(string!null) address:6(string)
│ ├── key: (4)
- │ └── fd: (4)-->(5,6)
- └── filters
- └── customer_id = customers.id [type=bool, outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ]), fd=(2)==(4), (4)==(2)]
+ │ ├── fd: (4)-->(5,6)
+ │ └── ordering: +4
+ ├── sort
+ │ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
+ │ ├── key: (1)
+ │ ├── fd: (1)-->(2,3)
+ │ ├── ordering: +2
+ │ └── scan orders
+ │ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
+ │ ├── key: (1)
+ │ └── fd: (1)-->(2,3)
+ └── filters (true)
================================================================================
Final best expression
Cost: 2160.05
================================================================================
left-join (hash)
├── columns: id:1(int!null) customer_id:2(int) status:3(string!null) id:4(int) name:5(string) address:6(string)
├── key: (1,4)
├── fd: (1)-->(2,3), (4)-->(5,6)
├── scan orders
│ ├── columns: orders.id:1(int!null) customer_id:2(int) status:3(string!null)
│ ├── key: (1)
│ └── fd: (1)-->(2,3)
├── scan customers
│ ├── columns: customers.id:4(int!null) name:5(string!null) address:6(string)
│ ├── key: (4)
│ └── fd: (4)-->(5,6)
└── filters
└── customer_id = customers.id [type=bool, outer=(2,4), constraints=(/2: (/NULL - ]; /4: (/NULL - ]), fd=(2)==(4), (4)==(2)]