github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/external/ycsb (about) 1 import file=ycsb_schema 2 ---- 3 4 # -------------------------------------------------- 5 # Workload A: Update heavy 6 # 7 # This workload has a mix of 50/50 reads and writes. Application example: 8 # a session store recording recent actions. 9 # -------------------------------------------------- 10 11 # 50% of the workload. 12 opt 13 SELECT * FROM usertable WHERE ycsb_key = 'user123' 14 ---- 15 scan usertable 16 ├── columns: ycsb_key:1!null field0:2 field1:3 field2:4 field3:5 field4:6 field5:7 field6:8 field7:9 field8:10 field9:11 17 ├── constraint: /1: [/'user123' - /'user123'] 18 ├── cardinality: [0 - 1] 19 ├── key: () 20 └── fd: ()-->(1-11) 21 22 # 50% of the workload. 23 opt 24 UPDATE usertable SET field5 = 'field5data' WHERE ycsb_key = 'user123' 25 ---- 26 update usertable 27 ├── columns: <none> 28 ├── fetch columns: ycsb_key:12 field5:18 29 ├── update-mapping: 30 │ └── field5_new:23 => field5:7 31 ├── cardinality: [0 - 0] 32 ├── volatile, side-effects, mutations 33 └── project 34 ├── columns: field5_new:23!null ycsb_key:12!null field5:18 35 ├── cardinality: [0 - 1] 36 ├── key: () 37 ├── fd: ()-->(12,18,23) 38 ├── scan usertable 39 │ ├── columns: ycsb_key:12!null field5:18 40 │ ├── constraint: /12: [/'user123' - /'user123'] 41 │ ├── cardinality: [0 - 1] 42 │ ├── key: () 43 │ └── fd: ()-->(12,18) 44 └── projections 45 └── 'field5data' [as=field5_new:23] 46 47 # -------------------------------------------------- 48 # Workload B: Read mostly 49 # 50 # This workload has a 95/5 reads/write mix. Application example: photo 51 # tagging; add a tag is an update, but most operations are to read tags. 52 # -------------------------------------------------- 53 54 # NOTE: same statements as Workload A, just a different mix. 95% of the 55 # workload is the SELECT statement and 5% of the workload is the UPDATE 56 # statement. 57 58 # -------------------------------------------------- 59 # Workload C: Read only 60 # 61 # This workload is 100% read. Application example: user profile cache, 62 # where profiles are constructed elsewhere (e.g., Hadoop). 63 # -------------------------------------------------- 64 65 # NOTE: consists entirely of the SELECT statement from workload A. 66 67 # -------------------------------------------------- 68 # Workload D: Read latest 69 # 70 # In this workload, new records are inserted, and the most recently 71 # inserted records are the most popular. Application example: user 72 # status updates; people want to read the latest. 73 # -------------------------------------------------- 74 75 # NOTE: 95% of the workload is the SELECT statement from workload A. 76 77 # 5% of the workload. 78 opt 79 INSERT INTO usertable VALUES ( 80 'user123', 81 'field0data', 82 'field1data', 83 'field2data', 84 'field3data', 85 'field4data', 86 'field5data', 87 'field6data', 88 'field7data', 89 'field8data', 90 'field9data' 91 ) 92 ---- 93 insert usertable 94 ├── columns: <none> 95 ├── insert-mapping: 96 │ ├── column1:12 => ycsb_key:1 97 │ ├── column2:13 => field0:2 98 │ ├── column3:14 => field1:3 99 │ ├── column4:15 => field2:4 100 │ ├── column5:16 => field3:5 101 │ ├── column6:17 => field4:6 102 │ ├── column7:18 => field5:7 103 │ ├── column8:19 => field6:8 104 │ ├── column9:20 => field7:9 105 │ ├── column10:21 => field8:10 106 │ └── column11:22 => field9:11 107 ├── cardinality: [0 - 0] 108 ├── volatile, side-effects, mutations 109 └── values 110 ├── columns: column1:12!null column2:13!null column3:14!null column4:15!null column5:16!null column6:17!null column7:18!null column8:19!null column9:20!null column10:21!null column11:22!null 111 ├── cardinality: [1 - 1] 112 ├── key: () 113 ├── fd: ()-->(12-22) 114 └── ('user123', 'field0data', 'field1data', 'field2data', 'field3data', 'field4data', 'field5data', 'field6data', 'field7data', 'field8data', 'field9data') 115 116 # -------------------------------------------------- 117 # Workload E: Short ranges 118 # 119 # In this workload, short ranges of records are queried, instead of 120 # individual records. Application example: threaded conversations, 121 # where each scan is for the posts in a given thread (assumed to be 122 # clustered by thread id). 123 # -------------------------------------------------- 124 125 # NOTE: 5% of the workload is the INSERT statement from workload D. 126 127 # 95% of the workload. 128 opt 129 SELECT * FROM usertable WHERE ycsb_key >= 'user123' LIMIT 321 130 ---- 131 scan usertable 132 ├── columns: ycsb_key:1!null field0:2 field1:3 field2:4 field3:5 field4:6 field5:7 field6:8 field7:9 field8:10 field9:11 133 ├── constraint: /1: [/'user123' - ] 134 ├── limit: 321 135 ├── key: (1) 136 └── fd: (1)-->(2-11) 137 138 # -------------------------------------------------- 139 # Workload F: Read-modify-write 140 # 141 # In this workload, the client will read a record, modify it, and write 142 # back the changes. Application example: user database, where user 143 # records are read and modified by the user or to record user activity. 144 # -------------------------------------------------- 145 146 # NOTE: 50% of the workload is the SELECT statement from workload A. 147 148 # NOTE: the following two statements are run together in a transaction 149 # to perform a read-modify-write operation. This makes up 50% of the 150 # workload. 151 152 opt 153 SELECT field5 FROM usertable WHERE ycsb_key = 'user123' 154 ---- 155 project 156 ├── columns: field5:7 157 ├── cardinality: [0 - 1] 158 ├── key: () 159 ├── fd: ()-->(7) 160 └── scan usertable 161 ├── columns: ycsb_key:1!null field5:7 162 ├── constraint: /1: [/'user123' - /'user123'] 163 ├── cardinality: [0 - 1] 164 ├── key: () 165 └── fd: ()-->(1,7) 166 167 opt 168 UPDATE usertable SET field5 = 'field5data' WHERE ycsb_key = 'user123' 169 ---- 170 update usertable 171 ├── columns: <none> 172 ├── fetch columns: ycsb_key:12 field5:18 173 ├── update-mapping: 174 │ └── field5_new:23 => field5:7 175 ├── cardinality: [0 - 0] 176 ├── volatile, side-effects, mutations 177 └── project 178 ├── columns: field5_new:23!null ycsb_key:12!null field5:18 179 ├── cardinality: [0 - 1] 180 ├── key: () 181 ├── fd: ()-->(12,18,23) 182 ├── scan usertable 183 │ ├── columns: ycsb_key:12!null field5:18 184 │ ├── constraint: /12: [/'user123' - /'user123'] 185 │ ├── cardinality: [0 - 1] 186 │ ├── key: () 187 │ └── fd: ()-->(12,18) 188 └── projections 189 └── 'field5data' [as=field5_new:23]