github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/ccl/logictestccl/testdata/logic_test/partitioning_constrained_scans (about) 1 # LogicTest: local fakedist 2 3 # Simple partitioning example. 4 statement ok 5 CREATE TABLE abc (a INT8, b INT8, c INT8, PRIMARY KEY (a, b, c)) 6 PARTITION BY LIST (a, b) (PARTITION small VALUES IN ((1, 1), (2, 2), (3, 3))) 7 8 # Insert one value in a partition, one value out of all partitions 9 # and one that doesn't satisfy the condition. 10 statement ok 11 INSERT INTO abc VALUES (1, 2, 4), (1, 1, 4), (0, 0, 0) 12 13 query III rowsort 14 SELECT * FROM abc 15 ---- 16 0 0 0 17 1 1 4 18 1 2 4 19 20 # Make sure 2 values can be seen even when the partition constraints are used. 21 # The (0, 0, 0) value should not be seen because the remaining filters 22 # will be applied properly. 23 query III rowsort 24 SELECT * FROM abc where c = 4 25 ---- 26 1 1 4 27 1 2 4 28 29 # Use the partition values to constrain the scan. 30 query T 31 EXPLAIN (OPT) SELECT * FROM abc where c = 4 32 ---- 33 select 34 ├── scan abc 35 │ └── constraint: /1/2/3 36 │ ├── [ - /1/0/4] 37 │ ├── [/1/1/4 - /1/1/4] 38 │ ├── [/1/2/4 - /2/1/4] 39 │ ├── [/2/2/4 - /2/2/4] 40 │ ├── [/2/3/4 - /3/2/4] 41 │ ├── [/3/3/4 - /3/3/4] 42 │ └── [/3/4/4 - ] 43 └── filters 44 └── c = 4 45 46 # Each partition has multiple spans. 47 query T 48 EXPLAIN (OPT) SELECT * FROM abc where c = 4 OR c = 6 49 ---- 50 select 51 ├── scan abc 52 │ └── constraint: /1/2/3 53 │ ├── [ - /1/0/6] 54 │ ├── [/1/1/4 - /1/1/4] 55 │ ├── [/1/1/6 - /1/1/6] 56 │ ├── [/1/2/4 - /2/1/6] 57 │ ├── [/2/2/4 - /2/2/4] 58 │ ├── [/2/2/6 - /2/2/6] 59 │ ├── [/2/3/4 - /3/2/6] 60 │ ├── [/3/3/4 - /3/3/4] 61 │ ├── [/3/3/6 - /3/3/6] 62 │ └── [/3/4/4 - ] 63 └── filters 64 └── (c = 4) OR (c = 6) 65 66 # The partition spans and the in between spans both can't simplify all filters 67 # and so we have the ((c % 2) = 1) filter remaining. This tests that the filter 68 # still remains but is also not duplicated. 69 query T 70 EXPLAIN (OPT) SELECT * FROM abc where (c > 0 AND c < 10) AND (c % 2 = 1) 71 ---- 72 select 73 ├── scan abc 74 │ └── constraint: /1/2/3 75 │ ├── [ - /1/0/9] 76 │ ├── [/1/1/1 - /1/1/9] 77 │ ├── [/1/2/1 - /2/1/9] 78 │ ├── [/2/2/1 - /2/2/9] 79 │ ├── [/2/3/1 - /3/2/9] 80 │ ├── [/3/3/1 - /3/3/9] 81 │ └── [/3/4/1 - ] 82 └── filters 83 ├── (c % 2) = 1 84 └── (c > 0) AND (c < 10) 85 86 # Perhaps an unintuitive example. The partition constraints don't really help 87 # constrain the index. None of the spans created by the partitioning are constrained 88 # and so this is equivalent to an unconstrained scan. 89 query T 90 EXPLAIN (OPT) SELECT * FROM abc where b = 3 91 ---- 92 select 93 ├── scan abc 94 └── filters 95 └── b = 3 96 97 # The orders example that inspired the partitioning index scan (with creation 98 # timestamp replaced with sequence number to reduce clutter in spans). 99 statement ok 100 CREATE TABLE orders ( 101 region STRING NOT NULL, id INT8 NOT NULL, total DECIMAL NOT NULL, seq_num INT NOT NULL, 102 PRIMARY KEY (region, id) 103 ) 104 105 # Create an index with the appropriate partitions. 106 statement ok 107 CREATE INDEX orders_by_seq_num 108 ON orders (region, seq_num, id) 109 STORING (total) 110 PARTITION BY LIST (region) 111 ( 112 PARTITION us_east1 VALUES IN ('us-east1'), 113 PARTITION us_west1 VALUES IN ('us-west1'), 114 PARTITION europe_west2 VALUES IN ('europe-west2') 115 ) 116 117 # The index is used instead of the table scan. 118 query T 119 EXPLAIN (OPT) SELECT sum(total) FROM "orders" WHERE seq_num >= 100 AND seq_num < 200; 120 ---- 121 scalar-group-by 122 ├── select 123 │ ├── scan orders@orders_by_seq_num 124 │ │ └── constraint: /1/4/2 125 │ │ ├── [ - /'europe-west2') 126 │ │ ├── [/'europe-west2'/100 - /'europe-west2'/199] 127 │ │ ├── [/e'europe-west2\x00'/100 - /'us-east1') 128 │ │ ├── [/'us-east1'/100 - /'us-east1'/199] 129 │ │ ├── [/e'us-east1\x00'/100 - /'us-west1') 130 │ │ ├── [/'us-west1'/100 - /'us-west1'/199] 131 │ │ └── [/e'us-west1\x00'/100 - ] 132 │ └── filters 133 │ └── (seq_num >= 100) AND (seq_num < 200) 134 └── aggregations 135 └── sum 136 └── total 137 138 # The partition values are not required as the index is constrained as is. 139 query T 140 EXPLAIN (OPT) SELECT sum(total) FROM "orders" WHERE region = 'us-east1' AND seq_num >= 100 AND seq_num < 200; 141 ---- 142 scalar-group-by 143 ├── scan orders@orders_by_seq_num 144 │ └── constraint: /1/4/2: [/'us-east1'/100 - /'us-east1'/199] 145 └── aggregations 146 └── sum 147 └── total 148 149 # Create a truly distributed and partitioned table where we use multi-column 150 # partitions. 151 statement ok 152 CREATE TABLE solar_system ( 153 planet STRING, region STRING, subregion STRING, val INT8, 154 PRIMARY KEY (planet, region, subregion, val) 155 ) 156 PARTITION BY LIST (planet, region, subregion) 157 ( 158 PARTITION westcoast VALUES IN (('earth', 'us', 'seatle'), ('earth', 'us', 'cali')), 159 PARTITION eu VALUES IN (('earth', 'eu', DEFAULT)), 160 PARTITION us VALUES IN (('earth', 'us', DEFAULT)), 161 PARTITION earth VALUES IN (('earth', DEFAULT, DEFAULT)), 162 PARTITION mars VALUES IN (('mars', DEFAULT, DEFAULT)), 163 PARTITION jupiter VALUES IN (('jupiter', DEFAULT, DEFAULT)), 164 PARTITION titan VALUES IN (('jupiter', 'titan', DEFAULT)), 165 PARTITION red_spot VALUES IN (('jupiter', 'titan', 'red spot')) 166 ) 167 168 statement ok 169 insert into solar_system values ('earth', 'us', 'seatle', 1); 170 171 statement ok 172 insert into solar_system values ('earth', 'us', 'cali', 2); 173 174 statement ok 175 insert into solar_system values ('earth', 'us', 'cali', 3); 176 177 statement ok 178 insert into solar_system values ('earth', 'eu', '', 4); 179 180 statement ok 181 insert into solar_system values ('mars', '', '', 5); 182 183 statement ok 184 insert into solar_system values ('jupiter', '', '', 6); 185 186 statement ok 187 insert into solar_system values ('jupiter', 'titan', '', 7); 188 189 statement ok 190 insert into solar_system values ('jupiter', 'titan', 'red spot', 8); 191 192 query TTTI 193 select * from solar_system where val < 9 order by (planet, region, subregion, val) 194 ---- 195 earth eu · 4 196 earth us cali 2 197 earth us cali 3 198 earth us seatle 1 199 jupiter · · 6 200 jupiter titan · 7 201 jupiter titan red spot 8 202 mars · · 5 203 204 query T 205 EXPLAIN (OPT) select * from solar_system where val = 8 206 ---- 207 select 208 ├── scan solar_system 209 │ └── constraint: /1/2/3/4 210 │ ├── [ - /'earth'/'us'/'cali') 211 │ ├── [/'earth'/'us'/'cali'/8 - /'earth'/'us'/'cali'/8] 212 │ ├── [/'earth'/'us'/e'cali\x00'/8 - /'earth'/'us'/'seatle') 213 │ ├── [/'earth'/'us'/'seatle'/8 - /'earth'/'us'/'seatle'/8] 214 │ ├── [/'earth'/'us'/e'seatle\x00'/8 - /'jupiter'/'titan'/'red spot') 215 │ ├── [/'jupiter'/'titan'/'red spot'/8 - /'jupiter'/'titan'/'red spot'/8] 216 │ └── [/'jupiter'/'titan'/e'red spot\x00'/8 - ] 217 └── filters 218 └── val = 8 219 220 query T 221 EXPLAIN (OPT) select * from solar_system where val > 6 AND val < 9 222 ---- 223 select 224 ├── scan solar_system 225 │ └── constraint: /1/2/3/4 226 │ ├── [ - /'earth'/'us'/'cali') 227 │ ├── [/'earth'/'us'/'cali'/7 - /'earth'/'us'/'cali'/8] 228 │ ├── [/'earth'/'us'/e'cali\x00'/7 - /'earth'/'us'/'seatle') 229 │ ├── [/'earth'/'us'/'seatle'/7 - /'earth'/'us'/'seatle'/8] 230 │ ├── [/'earth'/'us'/e'seatle\x00'/7 - /'jupiter'/'titan'/'red spot') 231 │ ├── [/'jupiter'/'titan'/'red spot'/7 - /'jupiter'/'titan'/'red spot'/8] 232 │ └── [/'jupiter'/'titan'/e'red spot\x00'/7 - ] 233 └── filters 234 └── (val > 6) AND (val < 9) 235 236 # Test telemetry about this optimization works. 237 query T 238 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name='sql.partitioning.partition-constrained-scan' AND usage_count > 0 239 ---- 240 sql.partitioning.partition-constrained-scan 241 242 # Regression test for #44154: a remaining filter that is not identical to an 243 # input filter should not be dropped. 244 statement ok 245 CREATE TABLE t0(c0 BOOL UNIQUE, c1 BOOL CHECK (true)) 246 247 statement ok 248 INSERT INTO t0(c0) VALUES (true) 249 250 query T 251 EXPLAIN (OPT) SELECT * FROM t0 WHERE t0.c0 AND (c1 OR (c0 > false AND c0 < false)) 252 ---- 253 select 254 ├── index-join t0 255 │ └── scan t0@t0_c0_key 256 │ └── constraint: /1: [/true - /true] 257 └── filters 258 └── c1 OR (c0 < false) 259 260 query BB 261 SELECT * FROM t0 WHERE t0.c0 AND (c1 OR (c0 > false AND c0 < false)) 262 ----