github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/memo/testdata/stats/scan (about) 1 exec-ddl 2 CREATE TABLE a (x INT PRIMARY KEY, y INT, s STRING, d DECIMAL NOT NULL, b BOOL, UNIQUE (s DESC, d)) 3 ---- 4 5 exec-ddl 6 ALTER TABLE a INJECT STATISTICS '[ 7 { 8 "columns": ["x"], 9 "created_at": "2018-01-01 1:00:00.00000+00:00", 10 "row_count": 2000, 11 "distinct_count": 2000 12 } 13 ]' 14 ---- 15 16 build 17 SELECT * FROM a 18 ---- 19 scan a 20 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) b:5(bool) 21 ├── stats: [rows=2000] 22 ├── key: (1) 23 └── fd: (1)-->(2-5), (3,4)~~>(1,2,5) 24 25 # Check that boolean columns have distinct count 2 when there are no stats 26 # available. 27 build 28 SELECT * FROM a WHERE b 29 ---- 30 select 31 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) b:5(bool!null) 32 ├── stats: [rows=990, distinct(5)=1, null(5)=0] 33 ├── key: (1) 34 ├── fd: ()-->(5), (1)-->(2-4), (3,4)~~>(1,2) 35 ├── scan a 36 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) b:5(bool) 37 │ ├── stats: [rows=2000, distinct(1)=2000, null(1)=0, distinct(4)=200, null(4)=0, distinct(5)=3, null(5)=20] 38 │ ├── key: (1) 39 │ └── fd: (1)-->(2-5), (3,4)~~>(1,2,5) 40 └── filters 41 └── b:5 [type=bool, outer=(5), constraints=(/5: [/true - /true]; tight), fd=()-->(5)] 42 43 exec-ddl 44 ALTER TABLE a INJECT STATISTICS '[ 45 { 46 "columns": ["x"], 47 "created_at": "2018-01-01 1:00:00.00000+00:00", 48 "row_count": 2000, 49 "distinct_count": 2000 50 }, 51 { 52 "columns": ["x","y"], 53 "created_at": "2018-01-01 1:00:00.00000+00:00", 54 "row_count": 2000, 55 "distinct_count": 2000 56 }, 57 { 58 "columns": ["y"], 59 "created_at": "2018-01-01 1:30:00.00000+00:00", 60 "row_count": 2000, 61 "distinct_count": 400 62 }, 63 { 64 "columns": ["y"], 65 "created_at": "2018-01-01 2:00:00.00000+00:00", 66 "row_count": 3000, 67 "distinct_count": 500 68 }, 69 { 70 "columns": ["s"], 71 "created_at": "2018-01-01 2:00:00.00000+00:00", 72 "row_count": 3000, 73 "distinct_count": 2 74 }, 75 { 76 "columns": ["d"], 77 "created_at": "2018-01-01 2:00:00.00000+00:00", 78 "row_count": 3000, 79 "distinct_count": 2000 80 } 81 ]' 82 ---- 83 84 build 85 SELECT * FROM a 86 ---- 87 scan a 88 ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) b:5(bool) 89 ├── stats: [rows=3000] 90 ├── key: (1) 91 └── fd: (1)-->(2-5), (3,4)~~>(1,2,5) 92 93 # Test constrained scan. 94 opt 95 SELECT s, x FROM a WHERE x > 0 AND x <= 100 96 ---- 97 scan a 98 ├── columns: s:3(string) x:1(int!null) 99 ├── constraint: /1: [/1 - /100] 100 ├── cardinality: [0 - 100] 101 ├── stats: [rows=100, distinct(1)=100, null(1)=0] 102 ├── key: (1) 103 └── fd: (1)-->(3) 104 105 # Test limited scan. 106 opt 107 SELECT s, x FROM a WHERE x > 5 AND x <= 10 LIMIT 2 108 ---- 109 scan a 110 ├── columns: s:3(string) x:1(int!null) 111 ├── constraint: /1: [/6 - /10] 112 ├── limit: 2 113 ├── stats: [rows=2] 114 ├── key: (1) 115 └── fd: (1)-->(3) 116 117 opt 118 SELECT count(*), y, x FROM a WHERE x > 0 AND x <= 100 GROUP BY x, y 119 ---- 120 group-by 121 ├── columns: count:6(int!null) y:2(int) x:1(int!null) 122 ├── grouping columns: x:1(int!null) 123 ├── internal-ordering: +1 124 ├── cardinality: [0 - 100] 125 ├── stats: [rows=100, distinct(1)=100, null(1)=0] 126 ├── key: (1) 127 ├── fd: (1)-->(2,6) 128 ├── scan a 129 │ ├── columns: x:1(int!null) y:2(int) 130 │ ├── constraint: /1: [/1 - /100] 131 │ ├── cardinality: [0 - 100] 132 │ ├── stats: [rows=100, distinct(1)=100, null(1)=0] 133 │ ├── key: (1) 134 │ ├── fd: (1)-->(2) 135 │ └── ordering: +1 136 └── aggregations 137 ├── count-rows [as=count_rows:6, type=int] 138 └── const-agg [as=y:2, type=int, outer=(2)] 139 └── y:2 [type=int] 140 141 # Test calculation of multi-column stats. 142 opt 143 SELECT y, s FROM a GROUP BY y, s 144 ---- 145 distinct-on 146 ├── columns: y:2(int) s:3(string) 147 ├── grouping columns: y:2(int) s:3(string) 148 ├── stats: [rows=1000, distinct(2,3)=1000, null(2,3)=0] 149 ├── key: (2,3) 150 └── scan a 151 ├── columns: y:2(int) s:3(string) 152 └── stats: [rows=3000, distinct(2,3)=1000, null(2,3)=0] 153 154 opt 155 SELECT s, d, x FROM a WHERE (s <= 'aaa') OR (s >= 'bar' AND s <= 'foo') 156 ---- 157 scan a@secondary 158 ├── columns: s:3(string!null) d:4(decimal!null) x:1(int!null) 159 ├── constraint: /-3/4 160 │ ├── [/'foo' - /'bar'] 161 │ └── [/'aaa' - /NULL) 162 ├── stats: [rows=1500, distinct(3)=1, null(3)=0] 163 ├── key: (1) 164 └── fd: (1)-->(3,4), (3,4)-->(1) 165 166 opt 167 SELECT s, d, x FROM a WHERE (s <= 'aaa') OR (s >= 'bar' AND s <= 'foo') OR s IS NULL 168 ---- 169 scan a@secondary 170 ├── columns: s:3(string) d:4(decimal!null) x:1(int!null) 171 ├── constraint: /-3/4 172 │ ├── [/'foo' - /'bar'] 173 │ └── [/'aaa' - /NULL] 174 ├── stats: [rows=1500, distinct(3)=1, null(3)=0] 175 ├── key: (1) 176 └── fd: (1)-->(3,4), (3,4)~~>(1) 177 178 opt 179 SELECT s, d, x FROM a WHERE s IS NOT NULL 180 ---- 181 scan a@secondary 182 ├── columns: s:3(string!null) d:4(decimal!null) x:1(int!null) 183 ├── constraint: /-3/4: [ - /NULL) 184 ├── stats: [rows=3000, distinct(3)=2, null(3)=0] 185 ├── key: (1) 186 └── fd: (1)-->(3,4), (3,4)-->(1) 187 188 opt 189 SELECT s, d, x FROM a WHERE (s >= 'bar' AND s <= 'foo') OR (s >= 'foobar') 190 ---- 191 scan a@secondary 192 ├── columns: s:3(string!null) d:4(decimal!null) x:1(int!null) 193 ├── constraint: /-3/4 194 │ ├── [ - /'foobar'] 195 │ └── [/'foo' - /'bar'] 196 ├── stats: [rows=1500, distinct(3)=1, null(3)=0] 197 ├── key: (1) 198 └── fd: (1)-->(3,4), (3,4)-->(1) 199 200 opt 201 SELECT * FROM a WHERE ((s >= 'bar' AND s <= 'foo') OR (s >= 'foobar')) AND d > 5.0 202 ---- 203 select 204 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) b:5(bool) 205 ├── stats: [rows=650, distinct(3)=1, null(3)=0, distinct(4)=650, null(4)=0, distinct(3,4)=650, null(3,4)=0] 206 ├── key: (1) 207 ├── fd: (1)-->(2-5), (3,4)-->(1,2,5) 208 ├── scan a 209 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) b:5(bool) 210 │ ├── stats: [rows=3000, distinct(1)=2000, null(1)=0, distinct(3)=2, null(3)=0, distinct(4)=2000, null(4)=0, distinct(3,4)=3000, null(3,4)=0] 211 │ ├── key: (1) 212 │ └── fd: (1)-->(2-5), (3,4)~~>(1,2,5) 213 └── filters 214 ├── ((s:3 >= 'bar') AND (s:3 <= 'foo')) OR (s:3 >= 'foobar') [type=bool, outer=(3), constraints=(/3: [/'bar' - /'foo'] [/'foobar' - ]; tight)] 215 └── d:4 > 5.0 [type=bool, outer=(4), constraints=(/4: (/5.0 - ]; tight)] 216 217 opt 218 SELECT * FROM a WHERE ((s >= 'bar' AND s <= 'foo') OR (s >= 'foobar')) AND d <= 5.0 AND s IS NOT NULL 219 ---- 220 select 221 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) b:5(bool) 222 ├── stats: [rows=650, distinct(3)=1, null(3)=0, distinct(4)=650, null(4)=0, distinct(3,4)=650, null(3,4)=0] 223 ├── key: (1) 224 ├── fd: (1)-->(2-5), (3,4)-->(1,2,5) 225 ├── scan a 226 │ ├── columns: x:1(int!null) y:2(int) s:3(string) d:4(decimal!null) b:5(bool) 227 │ ├── stats: [rows=3000, distinct(1)=2000, null(1)=0, distinct(3)=2, null(3)=0, distinct(4)=2000, null(4)=0, distinct(3,4)=3000, null(3,4)=0] 228 │ ├── key: (1) 229 │ └── fd: (1)-->(2-5), (3,4)~~>(1,2,5) 230 └── filters 231 ├── (((s:3 >= 'bar') AND (s:3 <= 'foo')) OR (s:3 >= 'foobar')) AND (s:3 IS NOT NULL) [type=bool, outer=(3), constraints=(/3: [/'bar' - /'foo'] [/'foobar' - ]; tight)] 232 └── d:4 <= 5.0 [type=bool, outer=(4), constraints=(/4: (/NULL - /5.0]; tight)] 233 234 # Bump up null counts. 235 236 exec-ddl 237 ALTER TABLE a INJECT STATISTICS '[ 238 { 239 "columns": ["x"], 240 "created_at": "2018-01-01 1:00:00.00000+00:00", 241 "row_count": 2000, 242 "distinct_count": 2000 243 }, 244 { 245 "columns": ["x","y"], 246 "created_at": "2018-01-01 1:00:00.00000+00:00", 247 "row_count": 2000, 248 "distinct_count": 2000, 249 "null_count": 300 250 }, 251 { 252 "columns": ["y"], 253 "created_at": "2018-01-01 1:30:00.00000+00:00", 254 "row_count": 2000, 255 "distinct_count": 401, 256 "null_count": 800 257 }, 258 { 259 "columns": ["y"], 260 "created_at": "2018-01-01 2:00:00.00000+00:00", 261 "row_count": 3000, 262 "distinct_count": 501, 263 "null_count": 1000 264 }, 265 { 266 "columns": ["s"], 267 "created_at": "2018-01-01 2:00:00.00000+00:00", 268 "row_count": 3000, 269 "distinct_count": 3, 270 "null_count": 1000 271 }, 272 { 273 "columns": ["b"], 274 "created_at": "2018-01-01 2:00:00.00000+00:00", 275 "row_count": 3000, 276 "distinct_count": 3, 277 "null_count": 1500 278 } 279 ]' 280 ---- 281 282 # Test calculation of multi-column stats. 283 opt colstat=2 colstat=3 colstat=5 colstat=(2,3,5) colstat=(2,3) colstat=(3,5) 284 SELECT y,s,b FROM a 285 ---- 286 scan a 287 ├── columns: y:2(int) s:3(string) b:5(bool) 288 └── stats: [rows=3000, distinct(2)=501, null(2)=1000, distinct(3)=3, null(3)=1000, distinct(5)=3, null(5)=1500, distinct(2,3)=1503, null(2,3)=333.333333, distinct(3,5)=9, null(3,5)=500, distinct(2,3,5)=3000, null(2,3,5)=166.666667] 289 290 opt colstat=1 colstat=3 colstat=5 colstat=(1,3,5) colstat=(1,3) colstat=(3,5) 291 SELECT x,y,s FROM a 292 ---- 293 scan a 294 ├── columns: x:1(int!null) y:2(int) s:3(string) 295 ├── stats: [rows=3000, distinct(1)=2000, null(1)=0, distinct(3)=3, null(3)=1000, distinct(5)=3, null(5)=1500, distinct(1,3)=3000, null(1,3)=0, distinct(3,5)=9, null(3,5)=500, distinct(1,3,5)=3000, null(1,3,5)=0] 296 ├── key: (1) 297 └── fd: (1)-->(2,3) 298 299 opt 300 SELECT y, s FROM a GROUP BY y, s 301 ---- 302 distinct-on 303 ├── columns: y:2(int) s:3(string) 304 ├── grouping columns: y:2(int) s:3(string) 305 ├── stats: [rows=1503, distinct(2,3)=1503, null(2,3)=1] 306 ├── key: (2,3) 307 └── scan a 308 ├── columns: y:2(int) s:3(string) 309 └── stats: [rows=3000, distinct(2,3)=1503, null(2,3)=333.333333] 310 311 opt 312 SELECT s, d, x FROM a WHERE ((s <= 'aaa') OR (s >= 'bar' AND s <= 'foo')) AND s IS NOT NULL 313 ---- 314 scan a@secondary 315 ├── columns: s:3(string!null) d:4(decimal!null) x:1(int!null) 316 ├── constraint: /-3/4 317 │ ├── [/'foo' - /'bar'] 318 │ └── [/'aaa' - /NULL) 319 ├── stats: [rows=1000, distinct(3)=1, null(3)=0] 320 ├── key: (1) 321 └── fd: (1)-->(3,4), (3,4)-->(1) 322 323 opt 324 SELECT s, d, x FROM a WHERE (s <= 'aaa') OR (s >= 'bar' AND s <= 'foo') OR s IS NULL 325 ---- 326 scan a@secondary 327 ├── columns: s:3(string) d:4(decimal!null) x:1(int!null) 328 ├── constraint: /-3/4 329 │ ├── [/'foo' - /'bar'] 330 │ └── [/'aaa' - /NULL] 331 ├── stats: [rows=1000, distinct(3)=1, null(3)=1000] 332 ├── key: (1) 333 └── fd: (1)-->(3,4), (3,4)~~>(1) 334 335 opt 336 SELECT s, d, x FROM a WHERE s IS NOT NULL 337 ---- 338 scan a@secondary 339 ├── columns: s:3(string!null) d:4(decimal!null) x:1(int!null) 340 ├── constraint: /-3/4: [ - /NULL) 341 ├── stats: [rows=2000, distinct(3)=3, null(3)=0] 342 ├── key: (1) 343 └── fd: (1)-->(3,4), (3,4)-->(1) 344 345 opt 346 SELECT s, d, x FROM a WHERE ((s >= 'bar' AND s <= 'foo') OR (s >= 'foobar')) AND s IS NOT NULL 347 ---- 348 scan a@secondary 349 ├── columns: s:3(string!null) d:4(decimal!null) x:1(int!null) 350 ├── constraint: /-3/4 351 │ ├── [ - /'foobar'] 352 │ └── [/'foo' - /'bar'] 353 ├── stats: [rows=1000, distinct(3)=1, null(3)=0] 354 ├── key: (1) 355 └── fd: (1)-->(3,4), (3,4)-->(1) 356 357 opt 358 SELECT * FROM a WHERE ((s >= 'bar' AND s <= 'foo') OR (s >= 'foobar')) AND d <= 5.0 AND s IS NOT NULL 359 ---- 360 index-join a 361 ├── columns: x:1(int!null) y:2(int) s:3(string!null) d:4(decimal!null) b:5(bool) 362 ├── stats: [rows=333.333333, distinct(3)=1, null(3)=0, distinct(4)=100, null(4)=0, distinct(3,4)=100, null(3,4)=0] 363 ├── key: (1) 364 ├── fd: (1)-->(2-5), (3,4)-->(1,2,5) 365 └── select 366 ├── columns: x:1(int!null) s:3(string!null) d:4(decimal!null) 367 ├── stats: [rows=333.333333, distinct(4)=98.265847, null(4)=0] 368 ├── key: (1) 369 ├── fd: (1)-->(3,4), (3,4)-->(1) 370 ├── scan a@secondary 371 │ ├── columns: x:1(int!null) s:3(string!null) d:4(decimal!null) 372 │ ├── constraint: /-3/4 373 │ │ ├── [ - /'foobar'/5.0] 374 │ │ └── [/'foo' - /'bar'/5.0] 375 │ ├── stats: [rows=1000, distinct(1)=911.337892, null(1)=0, distinct(3)=1, null(3)=0, distinct(4)=294.797541, null(4)=0] 376 │ ├── key: (1) 377 │ └── fd: (1)-->(3,4), (3,4)-->(1) 378 └── filters 379 └── d:4 <= 5.0 [type=bool, outer=(4), constraints=(/4: (/NULL - /5.0]; tight)] 380 381 exec-ddl 382 CREATE TABLE abcde ( 383 a INT PRIMARY KEY, 384 b INT, 385 c STRING, 386 d INT, 387 e INT, 388 INDEX bad(b, d), 389 INDEX good(b, c, d) 390 ) 391 ---- 392 393 # Regression test for #31929. Ensure that the good index is chosen. 394 opt 395 SELECT * FROM abcde WHERE b = 1 AND c LIKE '+1-1000%' 396 ---- 397 index-join abcde 398 ├── columns: a:1(int!null) b:2(int!null) c:3(string!null) d:4(int) e:5(int) 399 ├── stats: [rows=9.11111111, distinct(2)=1, null(2)=0, distinct(3)=9.11111111, null(3)=0, distinct(2,3)=9.11111111, null(2,3)=0] 400 ├── key: (1) 401 ├── fd: ()-->(2), (1)-->(3-5) 402 └── scan abcde@good 403 ├── columns: a:1(int!null) b:2(int!null) c:3(string!null) d:4(int) 404 ├── constraint: /2/3/4/1: [/1/'+1-1000' - /1/'+1-1001') 405 ├── stats: [rows=9.11111111, distinct(2)=1, null(2)=0, distinct(3)=9.11111111, null(3)=0, distinct(2,3)=9.11111111, null(2,3)=0] 406 ├── key: (1) 407 └── fd: ()-->(2), (1)-->(3,4) 408 409 exec-ddl 410 CREATE SEQUENCE seq 411 ---- 412 413 opt 414 SELECT * FROM seq 415 ---- 416 sequence-select seq 417 ├── columns: last_value:1(int!null) log_cnt:2(int!null) is_called:3(bool!null) 418 ├── cardinality: [1 - 1] 419 ├── stats: [rows=1] 420 ├── key: () 421 └── fd: ()-->(1-3) 422 423 exec-ddl 424 CREATE TABLE empty (x INT) 425 ---- 426 427 exec-ddl 428 ALTER TABLE empty INJECT STATISTICS '[ 429 { 430 "columns": ["x"], 431 "created_at": "2018-01-01 1:00:00.00000+00:00", 432 "row_count": 0, 433 "distinct_count": 0 434 } 435 ]' 436 ---- 437 438 # We should always estimate at least 1 row even if the stats have 0 rows. 439 opt 440 SELECT * FROM empty 441 ---- 442 scan empty 443 ├── columns: x:1(int) 444 └── stats: [rows=1] 445 446 # Regression test: previously, overflow when computing estimated distinct count 447 # here resulted in a row count of zero being estimated. 448 opt 449 SELECT x FROM a WHERE x >= -9223372036854775808 AND x <= 0 ORDER BY x LIMIT 10 450 ---- 451 scan a 452 ├── columns: x:1(int!null) 453 ├── constraint: /1: [/-9223372036854775808 - /0] 454 ├── limit: 10 455 ├── stats: [rows=10] 456 ├── key: (1) 457 └── ordering: +1 458 459 # Regression test for #37953. 460 exec-ddl 461 CREATE TABLE t37953 ( 462 a UUID NOT NULL, 463 b FLOAT8 NOT NULL, 464 c TIME NOT NULL, 465 d UUID NOT NULL, 466 e VARCHAR, 467 f "char" NULL, 468 g INT4 NOT NULL, 469 h VARCHAR NULL, 470 i REGPROC NULL, 471 j FLOAT8 NOT NULL 472 ) 473 ---- 474 475 norm 476 WITH 477 subq (col0, col1) 478 AS ( 479 SELECT 480 tab1.g AS col0, 481 CASE 482 WHEN ilike_escape( 483 regexp_replace( 484 tab0.h, 485 tab1.e, 486 tab0.f, 487 tab0.e::STRING 488 ), 489 tab1.f, 490 '' 491 ) 492 THEN true 493 ELSE false 494 END 495 AS col1 496 FROM 497 t37953 AS tab0, t37953 AS tab1 498 WHERE 499 tab0.j IN (tab1.j,) 500 ) 501 SELECT 502 1 503 FROM 504 subq 505 WHERE 506 subq.col1; 507 ---- 508 project 509 ├── columns: "?column?":26(int!null) 510 ├── immutable 511 ├── stats: [rows=1] 512 ├── fd: ()-->(26) 513 ├── select 514 │ ├── columns: col1:25(bool!null) 515 │ ├── immutable 516 │ ├── stats: [rows=1, distinct(25)=1, null(25)=0] 517 │ ├── fd: ()-->(25) 518 │ ├── project 519 │ │ ├── columns: col1:25(bool) 520 │ │ ├── immutable 521 │ │ ├── stats: [rows=333333.333, distinct(25)=333333.333, null(25)=0] 522 │ │ ├── inner-join (cross) 523 │ │ │ ├── columns: tab0.e:5(varchar) tab0.f:6("char") tab0.h:8(varchar) tab0.j:10(float!null) tab1.e:16(varchar) tab1.f:17("char") tab1.j:21(float!null) 524 │ │ │ ├── stats: [rows=333333.333, distinct(5,6,8,16,17)=333333.333, null(5,6,8,16,17)=3.33333333e-05] 525 │ │ │ ├── scan tab0 526 │ │ │ │ ├── columns: tab0.e:5(varchar) tab0.f:6("char") tab0.h:8(varchar) tab0.j:10(float!null) 527 │ │ │ │ └── stats: [rows=1000, distinct(5,6,8)=1000, null(5,6,8)=0.001] 528 │ │ │ ├── scan tab1 529 │ │ │ │ ├── columns: tab1.e:16(varchar) tab1.f:17("char") tab1.j:21(float!null) 530 │ │ │ │ └── stats: [rows=1000, distinct(16,17)=1000, null(16,17)=0.1] 531 │ │ │ └── filters 532 │ │ │ └── tab0.j:10 IN (tab1.j:21,) [type=bool, outer=(10,21)] 533 │ │ └── projections 534 │ │ └── CASE WHEN ilike_escape(regexp_replace(tab0.h:8, tab1.e:16, tab0.f:6, tab0.e:5::STRING), tab1.f:17, '') THEN true ELSE false END [as=col1:25, type=bool, outer=(5,6,8,16,17), immutable] 535 │ └── filters 536 │ └── col1:25 [type=bool, outer=(25), constraints=(/25: [/true - /true]; tight), fd=()-->(25)] 537 └── projections 538 └── 1 [as="?column?":26, type=int] 539 540 # --------------------- 541 # Tests with Histograms 542 # --------------------- 543 544 exec-ddl 545 CREATE TABLE hist ( 546 a INT, 547 b DATE, 548 c DECIMAL, 549 d FLOAT, 550 e TIMESTAMP, 551 f TIMESTAMPTZ, 552 g STRING, 553 INDEX idx_a (a), 554 INDEX idx_b (b), 555 INDEX idx_c (c), 556 INDEX idx_d (d), 557 INDEX idx_e (e), 558 INDEX idx_f (f), 559 INDEX idx_g (g) 560 ) 561 ---- 562 563 exec-ddl 564 ALTER TABLE hist INJECT STATISTICS '[ 565 { 566 "columns": ["a"], 567 "created_at": "2018-01-01 1:00:00.00000+00:00", 568 "row_count": 1000, 569 "distinct_count": 40, 570 "histo_col_type": "int", 571 "histo_buckets": [ 572 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "0"}, 573 {"num_eq": 10, "num_range": 90, "distinct_range": 9, "upper_bound": "10"}, 574 {"num_eq": 20, "num_range": 180, "distinct_range": 9, "upper_bound": "20"}, 575 {"num_eq": 30, "num_range": 270, "distinct_range": 9, "upper_bound": "30"}, 576 {"num_eq": 40, "num_range": 360, "distinct_range": 9, "upper_bound": "40"} 577 ] 578 }, 579 { 580 "columns": ["b"], 581 "created_at": "2018-01-01 1:00:00.00000+00:00", 582 "row_count": 1000, 583 "distinct_count": 120, 584 "histo_col_type": "date", 585 "histo_buckets": [ 586 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "2018-06-30"}, 587 {"num_eq": 10, "num_range": 90, "distinct_range": 29, "upper_bound": "2018-07-31"}, 588 {"num_eq": 20, "num_range": 180, "distinct_range": 29, "upper_bound": "2018-08-31"}, 589 {"num_eq": 30, "num_range": 270, "distinct_range": 29, "upper_bound": "2018-09-30"}, 590 {"num_eq": 40, "num_range": 360, "distinct_range": 29, "upper_bound": "2018-10-31"} 591 ] 592 }, 593 { 594 "columns": ["c"], 595 "created_at": "2018-01-01 1:00:00.00000+00:00", 596 "row_count": 1000, 597 "distinct_count": 45, 598 "histo_col_type": "decimal", 599 "histo_buckets": [ 600 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "0"}, 601 {"num_eq": 10, "num_range": 90, "distinct_range": 9, "upper_bound": "10"}, 602 {"num_eq": 20, "num_range": 180, "distinct_range": 10, "upper_bound": "20"}, 603 {"num_eq": 30, "num_range": 270, "distinct_range": 11, "upper_bound": "30"}, 604 {"num_eq": 40, "num_range": 360, "distinct_range": 11, "upper_bound": "40"} 605 ] 606 }, 607 { 608 "columns": ["d"], 609 "created_at": "2018-01-01 1:00:00.00000+00:00", 610 "row_count": 1000, 611 "distinct_count": 45, 612 "histo_col_type": "float", 613 "histo_buckets": [ 614 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "0"}, 615 {"num_eq": 10, "num_range": 90, "distinct_range": 9, "upper_bound": "10"}, 616 {"num_eq": 20, "num_range": 180, "distinct_range": 10, "upper_bound": "20"}, 617 {"num_eq": 30, "num_range": 270, "distinct_range": 11, "upper_bound": "30"}, 618 {"num_eq": 40, "num_range": 360, "distinct_range": 11, "upper_bound": "40"} 619 ] 620 }, 621 { 622 "columns": ["e"], 623 "created_at": "2018-01-01 1:00:00.00000+00:00", 624 "row_count": 1000, 625 "distinct_count": 200, 626 "histo_col_type": "timestamp", 627 "histo_buckets": [ 628 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "2018-06-30"}, 629 {"num_eq": 10, "num_range": 90, "distinct_range": 49, "upper_bound": "2018-07-31"}, 630 {"num_eq": 20, "num_range": 180, "distinct_range": 49, "upper_bound": "2018-08-31"}, 631 {"num_eq": 30, "num_range": 270, "distinct_range": 49, "upper_bound": "2018-09-30"}, 632 {"num_eq": 40, "num_range": 360, "distinct_range": 49, "upper_bound": "2018-10-31"} 633 ] 634 }, 635 { 636 "columns": ["f"], 637 "created_at": "2018-01-01 1:00:00.00000+00:00", 638 "row_count": 1000, 639 "distinct_count": 200, 640 "histo_col_type": "timestamptz", 641 "histo_buckets": [ 642 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "2018-06-30"}, 643 {"num_eq": 10, "num_range": 90, "distinct_range": 49, "upper_bound": "2018-07-31"}, 644 {"num_eq": 20, "num_range": 180, "distinct_range": 49, "upper_bound": "2018-08-31"}, 645 {"num_eq": 30, "num_range": 270, "distinct_range": 49, "upper_bound": "2018-09-30"}, 646 {"num_eq": 40, "num_range": 360, "distinct_range": 49, "upper_bound": "2018-10-31"} 647 ] 648 }, 649 { 650 "columns": ["g"], 651 "created_at": "2018-01-01 1:00:00.00000+00:00", 652 "row_count": 1000, 653 "distinct_count": 40, 654 "histo_col_type": "string", 655 "histo_buckets": [ 656 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "apple"}, 657 {"num_eq": 10, "num_range": 90, "distinct_range": 9, "upper_bound": "banana"}, 658 {"num_eq": 20, "num_range": 180, "distinct_range": 9, "upper_bound": "cherry"}, 659 {"num_eq": 30, "num_range": 270, "distinct_range": 9, "upper_bound": "mango"}, 660 {"num_eq": 40, "num_range": 360, "distinct_range": 9, "upper_bound": "pineapple"} 661 ] 662 } 663 ]' 664 ---- 665 666 # An index join is worthwhile for a < 10. 667 opt 668 SELECT * FROM hist WHERE a < 10 669 ---- 670 index-join hist 671 ├── columns: a:1(int!null) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 672 ├── stats: [rows=90, distinct(1)=9, null(1)=0] 673 │ histogram(1)= 0 0 80 10 674 │ <--- 0 ---- 9 675 └── scan hist@idx_a 676 ├── columns: a:1(int!null) rowid:8(int!null) 677 ├── constraint: /1/8: (/NULL - /9] 678 ├── stats: [rows=90, distinct(1)=9, null(1)=0] 679 │ histogram(1)= 0 0 80 10 680 │ <--- 0 ---- 9 681 ├── key: (8) 682 └── fd: (8)-->(1) 683 684 # An index join is not worthwhile for a > 30. 685 opt 686 SELECT * FROM hist WHERE a > 30 687 ---- 688 select 689 ├── columns: a:1(int!null) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 690 ├── stats: [rows=400, distinct(1)=10, null(1)=0] 691 │ histogram(1)= 0 0 360 40 692 │ <--- 30 ----- 40 693 ├── scan hist 694 │ ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 695 │ └── stats: [rows=1000, distinct(1)=40, null(1)=0] 696 │ histogram(1)= 0 0 90 10 180 20 270 30 360 40 697 │ <--- 0 ---- 10 ----- 20 ----- 30 ----- 40 698 └── filters 699 └── a:1 > 30 [type=bool, outer=(1), constraints=(/1: [/31 - ]; tight)] 700 701 opt 702 SELECT * FROM hist WHERE b > '2018-07-31'::DATE AND b < '2018-08-05'::DATE 703 ---- 704 index-join hist 705 ├── columns: a:1(int) b:2(date!null) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 706 ├── stats: [rows=24, distinct(2)=3.9, null(2)=0] 707 │ histogram(2)= 0 0 18 6 708 │ <--- '2018-07-31' ---- '2018-08-04' 709 └── scan hist@idx_b 710 ├── columns: b:2(date!null) rowid:8(int!null) 711 ├── constraint: /2/8: [/'2018-08-01' - /'2018-08-04'] 712 ├── stats: [rows=24, distinct(2)=3.9, null(2)=0] 713 │ histogram(2)= 0 0 18 6 714 │ <--- '2018-07-31' ---- '2018-08-04' 715 ├── key: (8) 716 └── fd: (8)-->(2) 717 718 opt 719 SELECT * FROM hist WHERE c = 20 OR (c < 10) 720 ---- 721 index-join hist 722 ├── columns: a:1(int) b:2(date) c:3(decimal!null) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 723 ├── stats: [rows=110, distinct(3)=10, null(3)=0] 724 │ histogram(3)= 0 0 90 0 0 20 725 │ <--- 0 ---- 10 --- 20 726 └── scan hist@idx_c 727 ├── columns: c:3(decimal!null) rowid:8(int!null) 728 ├── constraint: /3/8 729 │ ├── (/NULL - /10) 730 │ └── [/20 - /20] 731 ├── stats: [rows=110, distinct(3)=10, null(3)=0] 732 │ histogram(3)= 0 0 90 0 0 20 733 │ <--- 0 ---- 10 --- 20 734 ├── key: (8) 735 └── fd: (8)-->(3) 736 737 opt 738 SELECT * FROM hist WHERE c = 20 OR (c <= 10) 739 ---- 740 index-join hist 741 ├── columns: a:1(int) b:2(date) c:3(decimal!null) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 742 ├── stats: [rows=120, distinct(3)=11, null(3)=0] 743 │ histogram(3)= 0 0 90 10 0 20 744 │ <--- 0 ---- 10 --- 20 745 └── scan hist@idx_c 746 ├── columns: c:3(decimal!null) rowid:8(int!null) 747 ├── constraint: /3/8 748 │ ├── (/NULL - /10] 749 │ └── [/20 - /20] 750 ├── stats: [rows=120, distinct(3)=11, null(3)=0] 751 │ histogram(3)= 0 0 90 10 0 20 752 │ <--- 0 ---- 10 --- 20 753 ├── key: (8) 754 └── fd: (8)-->(3) 755 756 opt 757 SELECT * FROM hist WHERE (d >= 5 AND d < 15) OR d >= 40 758 ---- 759 index-join hist 760 ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float!null) e:5(timestamp) f:6(timestamptz) g:7(string) 761 ├── stats: [rows=185, distinct(4)=11.5, null(4)=0] 762 │ histogram(4)= 0 0 45 10 90 0 0 40 763 │ <--- 4.999999999999999 ---- 10.0 ---- 14.999999999999998 --- 40.0 764 └── scan hist@idx_d 765 ├── columns: d:4(float!null) rowid:8(int!null) 766 ├── constraint: /4/8 767 │ ├── [/5.0 - /14.999999999999998] 768 │ └── [/40.0 - ] 769 ├── stats: [rows=185, distinct(4)=11.5, null(4)=0] 770 │ histogram(4)= 0 0 45 10 90 0 0 40 771 │ <--- 4.999999999999999 ---- 10.0 ---- 14.999999999999998 --- 40.0 772 ├── key: (8) 773 └── fd: (8)-->(4) 774 775 opt 776 SELECT * FROM hist WHERE e < '2018-07-31 23:00:00'::TIMESTAMP 777 ---- 778 index-join hist 779 ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float) e:5(timestamp!null) f:6(timestamptz) g:7(string) 780 ├── stats: [rows=105.564516, distinct(5)=51.5147849, null(5)=0] 781 │ histogram(5)= 0 0 90 10 5.5645 6.7204e-14 782 │ <--- '2018-06-30 00:00:00+00:00' ---- '2018-07-31 00:00:00+00:00' -------- '2018-07-31 22:59:59.999999+00:00' 783 └── scan hist@idx_e 784 ├── columns: e:5(timestamp!null) rowid:8(int!null) 785 ├── constraint: /5/8: (/NULL - /'2018-07-31 22:59:59.999999+00:00'] 786 ├── stats: [rows=105.564516, distinct(5)=51.5147849, null(5)=0] 787 │ histogram(5)= 0 0 90 10 5.5645 6.7204e-14 788 │ <--- '2018-06-30 00:00:00+00:00' ---- '2018-07-31 00:00:00+00:00' -------- '2018-07-31 22:59:59.999999+00:00' 789 ├── key: (8) 790 └── fd: (8)-->(5) 791 792 opt 793 SELECT * FROM hist WHERE f = '2019-10-30 23:00:00'::TIMESTAMPTZ 794 ---- 795 index-join hist 796 ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz!null) g:7(string) 797 ├── stats: [rows=1e-07, distinct(6)=1e-07, null(6)=0] 798 │ histogram(6)= 799 ├── fd: ()-->(6) 800 └── scan hist@idx_f 801 ├── columns: f:6(timestamptz!null) rowid:8(int!null) 802 ├── constraint: /6/8: [/'2019-10-30 23:00:00+00:00' - /'2019-10-30 23:00:00+00:00'] 803 ├── stats: [rows=1e-07, distinct(6)=1e-07, null(6)=0] 804 │ histogram(6)= 805 ├── key: (8) 806 └── fd: ()-->(6) 807 808 opt 809 SELECT * FROM hist WHERE g = 'mango' OR g = 'foo' 810 ---- 811 index-join hist 812 ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string!null) 813 ├── stats: [rows=60, distinct(7)=2, null(7)=0] 814 │ histogram(7)= 0 30 0 30 815 │ <--- 'foo' --- 'mango' 816 └── scan hist@idx_g 817 ├── columns: g:7(string!null) rowid:8(int!null) 818 ├── constraint: /7/8 819 │ ├── [/'foo' - /'foo'] 820 │ └── [/'mango' - /'mango'] 821 ├── stats: [rows=60, distinct(7)=2, null(7)=0] 822 │ histogram(7)= 0 30 0 30 823 │ <--- 'foo' --- 'mango' 824 ├── key: (8) 825 └── fd: (8)-->(7) 826 827 # Select the correct index depending on which predicate is more selective. 828 opt 829 SELECT * FROM hist WHERE (a = 10 OR a = 20) AND (b = '2018-08-31'::DATE OR b = '2018-09-30'::DATE) 830 ---- 831 select 832 ├── columns: a:1(int!null) b:2(date!null) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 833 ├── stats: [rows=6.63, distinct(1)=2, null(1)=0, distinct(2)=2, null(2)=0, distinct(1,2)=4, null(1,2)=0] 834 │ histogram(1)= 0 2.21 0 4.42 835 │ <--- 10 --- 20 836 │ histogram(2)= 0 2.652 0 3.978 837 │ <--- '2018-08-31' --- '2018-09-30' 838 ├── index-join hist 839 │ ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 840 │ ├── stats: [rows=30] 841 │ └── scan hist@idx_a 842 │ ├── columns: a:1(int!null) rowid:8(int!null) 843 │ ├── constraint: /1/8 844 │ │ ├── [/10 - /10] 845 │ │ └── [/20 - /20] 846 │ ├── stats: [rows=30, distinct(1)=2, null(1)=0] 847 │ │ histogram(1)= 0 10 0 20 848 │ │ <--- 10 --- 20 849 │ ├── key: (8) 850 │ └── fd: (8)-->(1) 851 └── filters 852 └── (b:2 = '2018-08-31') OR (b:2 = '2018-09-30') [type=bool, outer=(2), constraints=(/2: [/'2018-08-31' - /'2018-08-31'] [/'2018-09-30' - /'2018-09-30']; tight)] 853 854 opt 855 SELECT * FROM hist WHERE (a = 30 OR a = 40) AND (b = '2018-06-30'::DATE OR b = '2018-07-31'::DATE) 856 ---- 857 select 858 ├── columns: a:1(int!null) b:2(date!null) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 859 ├── stats: [rows=3.094, distinct(1)=2, null(1)=0, distinct(2)=1, null(2)=0, distinct(1,2)=2, null(1,2)=0] 860 │ histogram(1)= 0 1.326 0 1.768 861 │ <--- 30 ---- 40 - 862 │ histogram(2)= 0 3.094 863 │ <--- '2018-07-31' 864 ├── index-join hist 865 │ ├── columns: a:1(int) b:2(date) c:3(decimal) d:4(float) e:5(timestamp) f:6(timestamptz) g:7(string) 866 │ ├── stats: [rows=10] 867 │ └── scan hist@idx_b 868 │ ├── columns: b:2(date!null) rowid:8(int!null) 869 │ ├── constraint: /2/8 870 │ │ ├── [/'2018-06-30' - /'2018-06-30'] 871 │ │ └── [/'2018-07-31' - /'2018-07-31'] 872 │ ├── stats: [rows=10, distinct(2)=1, null(2)=0] 873 │ │ histogram(2)= 0 10 874 │ │ <--- '2018-07-31' 875 │ ├── key: (8) 876 │ └── fd: (8)-->(2) 877 └── filters 878 └── (a:1 = 30) OR (a:1 = 40) [type=bool, outer=(1), constraints=(/1: [/30 - /30] [/40 - /40]; tight)] 879 880 # Regression test for #47390. Histograms must be used with index constraints 881 # to choose the correct index. 882 exec-ddl 883 CREATE TABLE xyz ( 884 x INT, 885 y INT, 886 z INT, 887 other INT, 888 PRIMARY KEY(x, y), 889 UNIQUE INDEX xyz_x_z_key (x, z), 890 INDEX xyz_x_other_z (x, other DESC) 891 ) 892 ---- 893 894 exec-ddl 895 ALTER TABLE xyz INJECT STATISTICS '[ 896 { 897 "columns": ["x"], 898 "distinct_count": 5, 899 "null_count": 0, 900 "row_count": 100, 901 "created_at": "2020-01-01 0:00:00.00000+00:00" 902 }, 903 { 904 "columns": ["z"], 905 "distinct_count": 100, 906 "null_count": 0, 907 "row_count": 100, 908 "created_at": "2020-01-01 0:00:00.00000+00:00", 909 "histo_col_type": "int", 910 "histo_buckets": [ 911 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "0"}, 912 {"num_eq": 0, "num_range": 100, "distinct_range": 100, "upper_bound": "1000"} 913 ] 914 }, 915 { 916 "columns": ["other"], 917 "distinct_count": 30, 918 "null_count": 0, 919 "row_count": 100, 920 "created_at": "2020-01-01 0:00:00.00000+00:00", 921 "histo_col_type": "int", 922 "histo_buckets": [ 923 {"num_eq": 0, "num_range": 0, "distinct_range": 0, "upper_bound": "0"}, 924 {"num_eq": 10, "num_range": 10, "distinct_range": 10, "upper_bound": "10"}, 925 {"num_eq": 20, "num_range": 20, "distinct_range": 20, "upper_bound": "20"}, 926 {"num_eq": 20, "num_range": 20, "distinct_range": 20, "upper_bound": "30"} 927 ] 928 } 929 ]' 930 ---- 931 932 opt 933 SELECT * FROM xyz WHERE x=1 AND z>990 934 ---- 935 index-join xyz 936 ├── columns: x:1(int!null) y:2(int!null) z:3(int!null) other:4(int) 937 ├── stats: [rows=0.828828829, distinct(1)=0.828828829, null(1)=0, distinct(3)=0.828828829, null(3)=0, distinct(1,3)=0.828828829, null(1,3)=0] 938 │ histogram(3)= 0 0 0.82883 0 939 │ <--- 990 --------- 1000 940 ├── key: (2) 941 ├── fd: ()-->(1), (2)-->(3,4), (3)-->(2,4) 942 └── scan xyz@xyz_x_z_key 943 ├── columns: x:1(int!null) y:2(int!null) z:3(int!null) 944 ├── constraint: /1/3: [/1/991 - /1] 945 ├── stats: [rows=0.828828829, distinct(1)=0.828828829, null(1)=0, distinct(3)=0.828828829, null(3)=0, distinct(1,3)=0.828828829, null(1,3)=0] 946 │ histogram(3)= 0 0 0.82883 0 947 │ <--- 990 --------- 1000 948 ├── key: (2) 949 └── fd: ()-->(1), (2)-->(3), (3)-->(2) 950 951 opt 952 SELECT * FROM xyz WHERE x=1 AND z<990 AND (other=11 OR other=13) 953 ---- 954 select 955 ├── columns: x:1(int!null) y:2(int!null) z:3(int!null) other:4(int!null) 956 ├── stats: [rows=1.8395996, distinct(1)=1, null(1)=0, distinct(3)=1.8395996, null(3)=0, distinct(4)=1.8395996, null(4)=0, distinct(1,3,4)=1.8395996, null(1,3,4)=0] 957 │ histogram(3)= 0 0 1.8377 0.0018601 958 │ <--- 0 ---------- 989 -- 959 │ histogram(4)= 0 0.9198 0 0.9198 960 │ <---- 11 ----- 13 - 961 ├── key: (2) 962 ├── fd: ()-->(1), (2)-->(3,4), (3)-->(2,4) 963 ├── index-join xyz 964 │ ├── columns: x:1(int!null) y:2(int!null) z:3(int) other:4(int) 965 │ ├── stats: [rows=0.58] 966 │ ├── key: (2) 967 │ ├── fd: ()-->(1), (2)-->(4), (2)-->(3,4), (1,3)~~>(2,4) 968 │ └── scan xyz@xyz_x_other_z 969 │ ├── columns: x:1(int!null) y:2(int!null) other:4(int!null) 970 │ ├── constraint: /1/-4/2 971 │ │ ├── [/1/13 - /1/13] 972 │ │ └── [/1/11 - /1/11] 973 │ ├── stats: [rows=0.58, distinct(1)=0.58, null(1)=0, distinct(4)=0.58, null(4)=0, distinct(1,4)=0.58, null(1,4)=0] 974 │ │ histogram(4)= 0 0.29 0 0.29 975 │ │ <--- 11 --- 13 976 │ ├── key: (2) 977 │ └── fd: ()-->(1), (2)-->(4) 978 └── filters 979 └── z:3 < 990 [type=bool, outer=(3), constraints=(/3: (/NULL - /989]; tight)] 980 981 # Regression test for #47742 and #47879. Make sure the first bucket always has 982 # NumRange=0, even after filtering. 983 exec-ddl 984 CREATE TABLE t47742 (a INT, b BOOL, INDEX b_idx (b DESC)); 985 ---- 986 987 exec-ddl 988 ALTER TABLE t47742 INJECT STATISTICS '[ 989 { 990 "name":"__auto__", 991 "created_at":"2000-01-01 00:00:00+00:00", 992 "columns":["b"], 993 "row_count":200000, 994 "distinct_count":56128, 995 "null_count":27606, 996 "histo_col_type":"BOOL", 997 "histo_buckets":[{ 998 "num_eq":7975541041996628837, 999 "num_range":0, 1000 "distinct_range":0, 1001 "upper_bound":"false" 1002 }, 1003 { 1004 "num_eq":124065620125775458, 1005 "num_range":100000000000, 1006 "distinct_range":100000000000, 1007 "upper_bound":"true" 1008 }] 1009 } 1010 ]' 1011 ---- 1012 1013 opt 1014 SELECT a, b::string FROM t47742 WHERE b = true 1015 ---- 1016 project 1017 ├── columns: a:1(int) b:4(string!null) 1018 ├── stats: [rows=2640.64496] 1019 ├── fd: ()-->(4) 1020 ├── index-join t47742 1021 │ ├── columns: a:1(int) t47742.b:2(bool!null) 1022 │ ├── stats: [rows=2640.64496, distinct(2)=2.00246926, null(2)=0] 1023 │ │ histogram(2)= 0 0 0.0021284 2640.6 1024 │ │ <--- false ----------- true 1025 │ ├── fd: ()-->(2) 1026 │ └── scan t47742@b_idx 1027 │ ├── columns: t47742.b:2(bool!null) rowid:3(int!null) 1028 │ ├── constraint: /-2/3: [/true - /true] 1029 │ ├── stats: [rows=2640.64496, distinct(2)=2.00246926, null(2)=0] 1030 │ │ histogram(2)= 0 0 0.0021284 2640.6 1031 │ │ <--- false ----------- true 1032 │ ├── key: (3) 1033 │ └── fd: ()-->(2) 1034 └── projections 1035 └── t47742.b:2::STRING [as=b:4, type=string, outer=(2)] 1036 1037 # Multi-column stats tests. 1038 exec-ddl 1039 CREATE TABLE multi_col ( 1040 a UUID, 1041 b BOOL, 1042 c INT, 1043 d STRING, 1044 e INT, 1045 f FLOAT, 1046 INDEX abcde_idx (a, b, c DESC, d, e), 1047 INDEX ce_idx (c, e), 1048 INDEX bad_idx (b, a DESC, d), 1049 INDEX def_idx (d, e, f), 1050 INDEX bef_idx (b, e, f) 1051 ) 1052 ---- 1053 1054 opt 1055 SELECT * FROM multi_col 1056 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1057 AND b = true 1058 AND c = 5 1059 AND d = 'foo' 1060 AND e > 10 AND e <= 20 1061 AND f > 0 1062 ---- 1063 select 1064 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) f:6(float!null) 1065 ├── stats: [rows=0.810857003, distinct(1)=0.810857003, null(1)=0, distinct(2)=0.810857003, null(2)=0, distinct(3)=0.810857003, null(3)=0, distinct(4)=0.810857003, null(4)=0, distinct(5)=0.810857003, null(5)=0, distinct(6)=0.810857003, null(6)=0, distinct(1-4)=0.810857003, null(1-4)=0, distinct(1-6)=0.810857003, null(1-6)=0] 1066 ├── fd: ()-->(1-4) 1067 ├── index-join multi_col 1068 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1069 │ ├── stats: [rows=0.810860303] 1070 │ ├── fd: ()-->(1-4) 1071 │ └── scan multi_col@abcde_idx 1072 │ ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) rowid:7(int!null) 1073 │ ├── constraint: /1/2/-3/4/5/7: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/true/5/'foo'/11 - /'37685f26-4b07-40ba-9bbf-42916ed9bc61'/true/5/'foo'/20] 1074 │ ├── stats: [rows=0.810860303, distinct(1)=0.810860303, null(1)=0, distinct(2)=0.810860303, null(2)=0, distinct(3)=0.810860303, null(3)=0, distinct(4)=0.810860303, null(4)=0, distinct(5)=0.810860303, null(5)=0, distinct(1-4)=0.810860303, null(1-4)=0, distinct(1-5)=0.810860303, null(1-5)=0] 1075 │ ├── key: (7) 1076 │ └── fd: ()-->(1-4), (7)-->(5) 1077 └── filters 1078 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1079 1080 # Make sure stats estimates are as expected when forcing the other indexes. 1081 opt 1082 SELECT * FROM multi_col@ce_idx 1083 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1084 AND b = true 1085 AND c = 5 1086 AND d = 'foo' 1087 AND e > 10 AND e <= 20 1088 AND f > 0 1089 ---- 1090 select 1091 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) f:6(float!null) 1092 ├── stats: [rows=0.810857003, distinct(1)=0.810857003, null(1)=0, distinct(2)=0.810857003, null(2)=0, distinct(3)=0.810857003, null(3)=0, distinct(4)=0.810857003, null(4)=0, distinct(5)=0.810857003, null(5)=0, distinct(6)=0.810857003, null(6)=0, distinct(1-4)=0.810857003, null(1-4)=0, distinct(1-6)=0.810857003, null(1-6)=0] 1093 ├── fd: ()-->(1-4) 1094 ├── index-join multi_col 1095 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1096 │ ├── stats: [rows=9.1] 1097 │ ├── fd: ()-->(3) 1098 │ └── scan multi_col@ce_idx 1099 │ ├── columns: c:3(int!null) e:5(int!null) rowid:7(int!null) 1100 │ ├── constraint: /3/5/7: [/5/11 - /5/20] 1101 │ ├── flags: force-index=ce_idx 1102 │ ├── stats: [rows=9.1, distinct(3)=1, null(3)=0, distinct(5)=9.1, null(5)=0, distinct(3,5)=9.1, null(3,5)=0] 1103 │ ├── key: (7) 1104 │ └── fd: ()-->(3), (7)-->(5) 1105 └── filters 1106 ├── a:1 = '37685f26-4b07-40ba-9bbf-42916ed9bc61' [type=bool, outer=(1), constraints=(/1: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61' - /'37685f26-4b07-40ba-9bbf-42916ed9bc61']; tight), fd=()-->(1)] 1107 ├── b:2 = true [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)] 1108 ├── d:4 = 'foo' [type=bool, outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 1109 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1110 1111 opt 1112 SELECT * FROM multi_col@bad_idx 1113 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1114 AND b = true 1115 AND c = 5 1116 AND d = 'foo' 1117 AND e > 10 AND e <= 20 1118 AND f > 0 1119 ---- 1120 select 1121 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) f:6(float!null) 1122 ├── stats: [rows=0.810857003, distinct(1)=0.810857003, null(1)=0, distinct(2)=0.810857003, null(2)=0, distinct(3)=0.810857003, null(3)=0, distinct(4)=0.810857003, null(4)=0, distinct(5)=0.810857003, null(5)=0, distinct(6)=0.810857003, null(6)=0, distinct(1-4)=0.810857003, null(1-4)=0, distinct(1-6)=0.810857003, null(1-6)=0] 1123 ├── fd: ()-->(1-4) 1124 ├── index-join multi_col 1125 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1126 │ ├── stats: [rows=0.90585] 1127 │ ├── fd: ()-->(1,2,4) 1128 │ └── scan multi_col@bad_idx 1129 │ ├── columns: a:1(uuid!null) b:2(bool!null) d:4(string!null) rowid:7(int!null) 1130 │ ├── constraint: /2/-1/4/7: [/true/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/'foo' - /true/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/'foo'] 1131 │ ├── flags: force-index=bad_idx 1132 │ ├── stats: [rows=0.90585, distinct(1)=0.90585, null(1)=0, distinct(2)=0.90585, null(2)=0, distinct(4)=0.90585, null(4)=0, distinct(1,2,4)=0.90585, null(1,2,4)=0] 1133 │ ├── key: (7) 1134 │ └── fd: ()-->(1,2,4) 1135 └── filters 1136 ├── (e:5 > 10) AND (e:5 <= 20) [type=bool, outer=(5), constraints=(/5: [/11 - /20]; tight)] 1137 ├── c:3 = 5 [type=bool, outer=(3), constraints=(/3: [/5 - /5]; tight), fd=()-->(3)] 1138 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1139 1140 opt 1141 SELECT * FROM multi_col@def_idx 1142 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1143 AND b = true 1144 AND c = 5 1145 AND d = 'foo' 1146 AND e > 10 AND e <= 20 1147 AND f > 0 1148 ---- 1149 select 1150 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) f:6(float!null) 1151 ├── stats: [rows=0.810857003, distinct(1)=0.810857003, null(1)=0, distinct(2)=0.810857003, null(2)=0, distinct(3)=0.810857003, null(3)=0, distinct(4)=0.810857003, null(4)=0, distinct(5)=0.810857003, null(5)=0, distinct(6)=0.810857003, null(6)=0, distinct(1-4)=0.810857003, null(1-4)=0, distinct(1-6)=0.810857003, null(1-6)=0] 1152 ├── fd: ()-->(1-4) 1153 ├── index-join multi_col 1154 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1155 │ ├── stats: [rows=3.39117229] 1156 │ ├── fd: ()-->(4) 1157 │ └── select 1158 │ ├── columns: d:4(string!null) e:5(int!null) f:6(float!null) rowid:7(int!null) 1159 │ ├── stats: [rows=3.39117229, distinct(6)=2.91208514, null(6)=0] 1160 │ ├── key: (7) 1161 │ ├── fd: ()-->(4), (7)-->(5,6) 1162 │ ├── scan multi_col@def_idx 1163 │ │ ├── columns: d:4(string!null) e:5(int!null) f:6(float) rowid:7(int!null) 1164 │ │ ├── constraint: /4/5/6/7: [/'foo'/11/5e-324 - /'foo'/20] 1165 │ │ ├── flags: force-index=def_idx 1166 │ │ ├── stats: [rows=9.1, distinct(4)=1, null(4)=0, distinct(5)=9.1, null(5)=0, distinct(6)=8.73625541, null(6)=0.091, distinct(7)=9.1, null(7)=0, distinct(4,5)=9.1, null(4,5)=0] 1167 │ │ ├── key: (7) 1168 │ │ └── fd: ()-->(4), (7)-->(5,6) 1169 │ └── filters 1170 │ └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1171 └── filters 1172 ├── a:1 = '37685f26-4b07-40ba-9bbf-42916ed9bc61' [type=bool, outer=(1), constraints=(/1: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61' - /'37685f26-4b07-40ba-9bbf-42916ed9bc61']; tight), fd=()-->(1)] 1173 ├── b:2 = true [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)] 1174 └── c:3 = 5 [type=bool, outer=(3), constraints=(/3: [/5 - /5]; tight), fd=()-->(3)] 1175 1176 # A different combination of predicates. 1177 opt 1178 SELECT * FROM multi_col 1179 WHERE b = true 1180 AND c = 5 1181 AND e IN (1, 3, 5, 7, 9) 1182 AND f > 0 1183 ---- 1184 select 1185 ├── columns: a:1(uuid) b:2(bool!null) c:3(int!null) d:4(string) e:5(int!null) f:6(float!null) 1186 ├── stats: [rows=3.1625092, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=3.1625092, null(5)=0, distinct(6)=3.1625092, null(6)=0, distinct(2,3)=1, null(2,3)=0, distinct(2,3,5,6)=3.1625092, null(2,3,5,6)=0] 1187 ├── fd: ()-->(2,3) 1188 ├── index-join multi_col 1189 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1190 │ ├── stats: [rows=4.55405405] 1191 │ ├── fd: ()-->(3) 1192 │ └── scan multi_col@ce_idx 1193 │ ├── columns: c:3(int!null) e:5(int!null) rowid:7(int!null) 1194 │ ├── constraint: /3/5/7 1195 │ │ ├── [/5/1 - /5/1] 1196 │ │ ├── [/5/3 - /5/3] 1197 │ │ ├── [/5/5 - /5/5] 1198 │ │ ├── [/5/7 - /5/7] 1199 │ │ └── [/5/9 - /5/9] 1200 │ ├── stats: [rows=4.55405405, distinct(3)=1, null(3)=0, distinct(5)=4.55405405, null(5)=0, distinct(3,5)=4.55405405, null(3,5)=0] 1201 │ ├── key: (7) 1202 │ └── fd: ()-->(3), (7)-->(5) 1203 └── filters 1204 ├── b:2 = true [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)] 1205 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1206 1207 # Force the alternate index. 1208 opt 1209 SELECT * FROM multi_col@bef_idx 1210 WHERE b = true 1211 AND c = 5 1212 AND e IN (1, 3, 5, 7, 9) 1213 AND f > 0 1214 ---- 1215 select 1216 ├── columns: a:1(uuid) b:2(bool!null) c:3(int!null) d:4(string) e:5(int!null) f:6(float!null) 1217 ├── stats: [rows=3.1625092, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=3.1625092, null(5)=0, distinct(6)=3.1625092, null(6)=0, distinct(2,3)=1, null(2,3)=0, distinct(2,3,5,6)=3.1625092, null(2,3,5,6)=0] 1218 ├── fd: ()-->(2,3) 1219 ├── index-join multi_col 1220 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1221 │ ├── stats: [rows=45.825] 1222 │ ├── fd: ()-->(2) 1223 │ └── scan multi_col@bef_idx 1224 │ ├── columns: b:2(bool!null) e:5(int!null) f:6(float!null) rowid:7(int!null) 1225 │ ├── constraint: /2/5/6/7 1226 │ │ ├── [/true/1/5e-324 - /true/1] 1227 │ │ ├── [/true/3/5e-324 - /true/3] 1228 │ │ ├── [/true/5/5e-324 - /true/5] 1229 │ │ ├── [/true/7/5e-324 - /true/7] 1230 │ │ └── [/true/9/5e-324 - /true/9] 1231 │ ├── flags: force-index=bef_idx 1232 │ ├── stats: [rows=45.825, distinct(2)=1, null(2)=0, distinct(5)=5, null(5)=0, distinct(6)=33.3333333, null(6)=0, distinct(2,5,6)=45.825, null(2,5,6)=0] 1233 │ ├── key: (7) 1234 │ └── fd: ()-->(2), (7)-->(5,6) 1235 └── filters 1236 └── c:3 = 5 [type=bool, outer=(3), constraints=(/3: [/5 - /5]; tight), fd=()-->(3)] 1237 1238 # Now inject some stats ane see how the estimates change. 1239 exec-ddl 1240 ALTER TABLE multi_col INJECT STATISTICS '[ 1241 { 1242 "columns": [ 1243 "a" 1244 ], 1245 "created_at": "2020-05-14 22:50:19.864085+00:00", 1246 "distinct_count": 10000, 1247 "histo_col_type": "", 1248 "name": "__auto__", 1249 "null_count": 0, 1250 "row_count": 10000 1251 }, 1252 { 1253 "columns": [ 1254 "a", 1255 "b" 1256 ], 1257 "created_at": "2020-05-14 22:50:19.864085+00:00", 1258 "distinct_count": 10000, 1259 "histo_col_type": "", 1260 "name": "__auto__", 1261 "null_count": 0, 1262 "row_count": 10000 1263 }, 1264 { 1265 "columns": [ 1266 "a", 1267 "b", 1268 "c" 1269 ], 1270 "created_at": "2020-05-14 22:50:19.864085+00:00", 1271 "distinct_count": 10000, 1272 "histo_col_type": "", 1273 "name": "__auto__", 1274 "null_count": 0, 1275 "row_count": 10000 1276 }, 1277 { 1278 "columns": [ 1279 "a", 1280 "b", 1281 "c", 1282 "d" 1283 ], 1284 "created_at": "2020-05-14 22:50:19.864085+00:00", 1285 "distinct_count": 10000, 1286 "histo_col_type": "", 1287 "name": "__auto__", 1288 "null_count": 0, 1289 "row_count": 10000 1290 }, 1291 { 1292 "columns": [ 1293 "a", 1294 "b", 1295 "c", 1296 "d", 1297 "e" 1298 ], 1299 "created_at": "2020-05-14 22:50:19.864085+00:00", 1300 "distinct_count": 10000, 1301 "histo_col_type": "", 1302 "name": "__auto__", 1303 "null_count": 0, 1304 "row_count": 10000 1305 }, 1306 { 1307 "columns": [ 1308 "c" 1309 ], 1310 "created_at": "2020-05-14 22:50:19.864085+00:00", 1311 "distinct_count": 100, 1312 "histo_col_type": "", 1313 "name": "__auto__", 1314 "null_count": 1000, 1315 "row_count": 10000 1316 }, 1317 { 1318 "columns": [ 1319 "c", 1320 "e" 1321 ], 1322 "created_at": "2020-05-14 22:50:19.864085+00:00", 1323 "distinct_count": 1000, 1324 "histo_col_type": "", 1325 "name": "__auto__", 1326 "null_count": 100, 1327 "row_count": 10000 1328 }, 1329 { 1330 "columns": [ 1331 "b" 1332 ], 1333 "created_at": "2020-05-14 22:50:19.864085+00:00", 1334 "distinct_count": 3, 1335 "histo_col_type": "", 1336 "name": "__auto__", 1337 "null_count": 5000, 1338 "row_count": 10000 1339 }, 1340 { 1341 "columns": [ 1342 "b", 1343 "a", 1344 "d" 1345 ], 1346 "created_at": "2020-05-14 22:50:19.864085+00:00", 1347 "distinct_count": 10000, 1348 "histo_col_type": "", 1349 "name": "__auto__", 1350 "null_count": 0, 1351 "row_count": 10000 1352 }, 1353 { 1354 "columns": [ 1355 "b", 1356 "e" 1357 ], 1358 "created_at": "2020-05-14 22:50:19.864085+00:00", 1359 "distinct_count": 200, 1360 "histo_col_type": "", 1361 "name": "__auto__", 1362 "null_count": 1000, 1363 "row_count": 10000 1364 }, 1365 { 1366 "columns": [ 1367 "b", 1368 "e", 1369 "f" 1370 ], 1371 "created_at": "2020-05-14 22:50:19.864085+00:00", 1372 "distinct_count": 9000, 1373 "histo_col_type": "", 1374 "name": "__auto__", 1375 "null_count": 50, 1376 "row_count": 10000 1377 }, 1378 { 1379 "columns": [ 1380 "d" 1381 ], 1382 "created_at": "2020-05-14 22:50:19.864085+00:00", 1383 "distinct_count": 10, 1384 "histo_col_type": "", 1385 "name": "__auto__", 1386 "null_count": 0, 1387 "row_count": 10000 1388 }, 1389 { 1390 "columns": [ 1391 "d", 1392 "e" 1393 ], 1394 "created_at": "2020-05-14 22:50:19.864085+00:00", 1395 "distinct_count": 200, 1396 "histo_col_type": "", 1397 "name": "__auto__", 1398 "null_count": 0, 1399 "row_count": 10000 1400 }, 1401 { 1402 "columns": [ 1403 "d", 1404 "e", 1405 "f" 1406 ], 1407 "created_at": "2020-05-14 22:50:19.864085+00:00", 1408 "distinct_count": 10000, 1409 "histo_col_type": "", 1410 "name": "__auto__", 1411 "null_count": 0, 1412 "row_count": 10000 1413 }, 1414 { 1415 "columns": [ 1416 "e" 1417 ], 1418 "created_at": "2020-05-14 22:50:19.864085+00:00", 1419 "distinct_count": 100, 1420 "histo_col_type": "", 1421 "name": "__auto__", 1422 "null_count": 1000, 1423 "row_count": 10000 1424 }, 1425 { 1426 "columns": [ 1427 "f" 1428 ], 1429 "created_at": "2020-05-14 22:50:19.864085+00:00", 1430 "distinct_count": 5000, 1431 "histo_col_type": "", 1432 "name": "__auto__", 1433 "null_count": 100, 1434 "row_count": 10000 1435 } 1436 ]' 1437 ---- 1438 1439 opt 1440 SELECT * FROM multi_col 1441 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1442 AND b = true 1443 AND c = 5 1444 AND d = 'foo' 1445 AND e > 10 AND e <= 20 1446 AND f > 0 1447 ---- 1448 select 1449 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) f:6(float!null) 1450 ├── stats: [rows=0.810021137, distinct(1)=0.810021137, null(1)=0, distinct(2)=0.810021137, null(2)=0, distinct(3)=0.810021137, null(3)=0, distinct(4)=0.810021137, null(4)=0, distinct(5)=0.810021137, null(5)=0, distinct(6)=0.810021137, null(6)=0, distinct(1-4)=0.810021137, null(1-4)=0, distinct(1-6)=0.810021137, null(1-6)=0] 1451 ├── fd: ()-->(1-4) 1452 ├── index-join multi_col 1453 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1454 │ ├── stats: [rows=0.810022521] 1455 │ ├── fd: ()-->(1-4) 1456 │ └── scan multi_col@abcde_idx 1457 │ ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) rowid:7(int!null) 1458 │ ├── constraint: /1/2/-3/4/5/7: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/true/5/'foo'/11 - /'37685f26-4b07-40ba-9bbf-42916ed9bc61'/true/5/'foo'/20] 1459 │ ├── stats: [rows=0.810022521, distinct(1)=0.810022521, null(1)=0, distinct(2)=0.810022521, null(2)=0, distinct(3)=0.810022521, null(3)=0, distinct(4)=0.810022521, null(4)=0, distinct(5)=0.810022521, null(5)=0, distinct(1-4)=0.810022521, null(1-4)=0, distinct(1-5)=0.810022521, null(1-5)=0] 1460 │ ├── key: (7) 1461 │ └── fd: ()-->(1-4), (7)-->(5) 1462 └── filters 1463 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1464 1465 # A different combination of predicates. 1466 opt 1467 SELECT * FROM multi_col 1468 WHERE b = true 1469 AND c = 5 1470 AND e IN (1, 3, 5, 7, 9) 1471 AND f > 0 1472 ---- 1473 select 1474 ├── columns: a:1(uuid) b:2(bool!null) c:3(int!null) d:4(string) e:5(int!null) f:6(float!null) 1475 ├── stats: [rows=27.8153382, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=5, null(5)=0, distinct(6)=27.8153382, null(6)=0, distinct(2,3)=1, null(2,3)=0, distinct(2,3,5,6)=27.8153382, null(2,3,5,6)=0] 1476 ├── fd: ()-->(2,3) 1477 ├── index-join multi_col 1478 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1479 │ ├── stats: [rows=45.0078177] 1480 │ ├── fd: ()-->(3) 1481 │ └── scan multi_col@ce_idx 1482 │ ├── columns: c:3(int!null) e:5(int!null) rowid:7(int!null) 1483 │ ├── constraint: /3/5/7 1484 │ │ ├── [/5/1 - /5/1] 1485 │ │ ├── [/5/3 - /5/3] 1486 │ │ ├── [/5/5 - /5/5] 1487 │ │ ├── [/5/7 - /5/7] 1488 │ │ └── [/5/9 - /5/9] 1489 │ ├── stats: [rows=45.0078177, distinct(3)=1, null(3)=0, distinct(5)=5, null(5)=0, distinct(3,5)=5, null(3,5)=0] 1490 │ ├── key: (7) 1491 │ └── fd: ()-->(3), (7)-->(5) 1492 └── filters 1493 ├── b:2 = true [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)] 1494 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1495 1496 # Force the alternate index. 1497 opt 1498 SELECT * FROM multi_col@bef_idx 1499 WHERE b = true 1500 AND c = 5 1501 AND e IN (1, 3, 5, 7, 9) 1502 AND f > 0 1503 ---- 1504 select 1505 ├── columns: a:1(uuid) b:2(bool!null) c:3(int!null) d:4(string) e:5(int!null) f:6(float!null) 1506 ├── stats: [rows=27.8153382, distinct(2)=1, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=5, null(5)=0, distinct(6)=27.8153382, null(6)=0, distinct(2,3)=1, null(2,3)=0, distinct(2,3,5,6)=27.8153382, null(2,3,5,6)=0] 1507 ├── fd: ()-->(2,3) 1508 ├── index-join multi_col 1509 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1510 │ ├── stats: [rows=412.841659] 1511 │ ├── fd: ()-->(2) 1512 │ └── scan multi_col@bef_idx 1513 │ ├── columns: b:2(bool!null) e:5(int!null) f:6(float!null) rowid:7(int!null) 1514 │ ├── constraint: /2/5/6/7 1515 │ │ ├── [/true/1/5e-324 - /true/1] 1516 │ │ ├── [/true/3/5e-324 - /true/3] 1517 │ │ ├── [/true/5/5e-324 - /true/5] 1518 │ │ ├── [/true/7/5e-324 - /true/7] 1519 │ │ └── [/true/9/5e-324 - /true/9] 1520 │ ├── flags: force-index=bef_idx 1521 │ ├── stats: [rows=412.841659, distinct(2)=1, null(2)=0, distinct(5)=5, null(5)=0, distinct(6)=412.841659, null(6)=0, distinct(2,5,6)=412.841659, null(2,5,6)=0] 1522 │ ├── key: (7) 1523 │ └── fd: ()-->(2), (7)-->(5,6) 1524 └── filters 1525 └── c:3 = 5 [type=bool, outer=(3), constraints=(/3: [/5 - /5]; tight), fd=()-->(3)] 1526 1527 # Include histograms. 1528 exec-ddl 1529 ALTER TABLE multi_col INJECT STATISTICS '[ 1530 { 1531 "columns": [ 1532 "a" 1533 ], 1534 "created_at": "2020-05-14 22:50:19.864085+00:00", 1535 "distinct_count": 10000, 1536 "histo_col_type": "", 1537 "name": "__auto__", 1538 "null_count": 0, 1539 "row_count": 10000 1540 }, 1541 { 1542 "columns": [ 1543 "a", 1544 "b" 1545 ], 1546 "created_at": "2020-05-14 22:50:19.864085+00:00", 1547 "distinct_count": 10000, 1548 "histo_col_type": "", 1549 "name": "__auto__", 1550 "null_count": 0, 1551 "row_count": 10000 1552 }, 1553 { 1554 "columns": [ 1555 "a", 1556 "b", 1557 "c" 1558 ], 1559 "created_at": "2020-05-14 22:50:19.864085+00:00", 1560 "distinct_count": 10000, 1561 "histo_col_type": "", 1562 "name": "__auto__", 1563 "null_count": 0, 1564 "row_count": 10000 1565 }, 1566 { 1567 "columns": [ 1568 "a", 1569 "b", 1570 "c", 1571 "d" 1572 ], 1573 "created_at": "2020-05-14 22:50:19.864085+00:00", 1574 "distinct_count": 10000, 1575 "histo_col_type": "", 1576 "name": "__auto__", 1577 "null_count": 0, 1578 "row_count": 10000 1579 }, 1580 { 1581 "columns": [ 1582 "a", 1583 "b", 1584 "c", 1585 "d", 1586 "e" 1587 ], 1588 "created_at": "2020-05-14 22:50:19.864085+00:00", 1589 "distinct_count": 10000, 1590 "histo_col_type": "", 1591 "name": "__auto__", 1592 "null_count": 0, 1593 "row_count": 10000 1594 }, 1595 { 1596 "columns": [ 1597 "c" 1598 ], 1599 "created_at": "2020-05-14 22:50:19.864085+00:00", 1600 "distinct_count": 100, 1601 "histo_col_type": "", 1602 "name": "__auto__", 1603 "null_count": 1000, 1604 "row_count": 10000 1605 }, 1606 { 1607 "columns": [ 1608 "c", 1609 "e" 1610 ], 1611 "created_at": "2020-05-14 22:50:19.864085+00:00", 1612 "distinct_count": 1000, 1613 "histo_col_type": "", 1614 "name": "__auto__", 1615 "null_count": 100, 1616 "row_count": 10000 1617 }, 1618 { 1619 "columns": [ 1620 "b" 1621 ], 1622 "created_at": "2020-05-14 22:50:19.864085+00:00", 1623 "distinct_count": 3, 1624 "histo_col_type": "BOOL", 1625 "histo_buckets":[{ 1626 "num_eq":1, 1627 "num_range":0, 1628 "distinct_range":0, 1629 "upper_bound":"false" 1630 }, 1631 { 1632 "num_eq":4999, 1633 "num_range":0, 1634 "distinct_range":0, 1635 "upper_bound":"true" 1636 }], 1637 "name": "__auto__", 1638 "null_count": 5000, 1639 "row_count": 10000 1640 }, 1641 { 1642 "columns": [ 1643 "b", 1644 "a", 1645 "d" 1646 ], 1647 "created_at": "2020-05-14 22:50:19.864085+00:00", 1648 "distinct_count": 10000, 1649 "histo_col_type": "", 1650 "name": "__auto__", 1651 "null_count": 0, 1652 "row_count": 10000 1653 }, 1654 { 1655 "columns": [ 1656 "b", 1657 "e" 1658 ], 1659 "created_at": "2020-05-14 22:50:19.864085+00:00", 1660 "distinct_count": 200, 1661 "histo_col_type": "", 1662 "name": "__auto__", 1663 "null_count": 1000, 1664 "row_count": 10000 1665 }, 1666 { 1667 "columns": [ 1668 "b", 1669 "e", 1670 "f" 1671 ], 1672 "created_at": "2020-05-14 22:50:19.864085+00:00", 1673 "distinct_count": 9000, 1674 "histo_col_type": "", 1675 "name": "__auto__", 1676 "null_count": 50, 1677 "row_count": 10000 1678 }, 1679 { 1680 "columns": [ 1681 "d" 1682 ], 1683 "created_at": "2020-05-14 22:50:19.864085+00:00", 1684 "distinct_count": 10, 1685 "histo_col_type": "STRING", 1686 "histo_buckets":[{ 1687 "num_eq":1, 1688 "num_range":0, 1689 "distinct_range":0, 1690 "upper_bound":"bar" 1691 }, 1692 { 1693 "num_eq":1, 1694 "num_range":2, 1695 "distinct_range":2, 1696 "upper_bound":"baz" 1697 }, 1698 { 1699 "num_eq":1, 1700 "num_range":1, 1701 "distinct_range":1, 1702 "upper_bound":"boo" 1703 }, 1704 { 1705 "num_eq":9990, 1706 "num_range":2, 1707 "distinct_range":1, 1708 "upper_bound":"foo" 1709 }, 1710 { 1711 "num_eq":1, 1712 "num_range":1, 1713 "distinct_range":1, 1714 "upper_bound":"foobar" 1715 }], 1716 "name": "__auto__", 1717 "null_count": 0, 1718 "row_count": 10000 1719 }, 1720 { 1721 "columns": [ 1722 "d", 1723 "e" 1724 ], 1725 "created_at": "2020-05-14 22:50:19.864085+00:00", 1726 "distinct_count": 200, 1727 "histo_col_type": "", 1728 "name": "__auto__", 1729 "null_count": 0, 1730 "row_count": 10000 1731 }, 1732 { 1733 "columns": [ 1734 "d", 1735 "e", 1736 "f" 1737 ], 1738 "created_at": "2020-05-14 22:50:19.864085+00:00", 1739 "distinct_count": 10000, 1740 "histo_col_type": "", 1741 "name": "__auto__", 1742 "null_count": 0, 1743 "row_count": 10000 1744 }, 1745 { 1746 "columns": [ 1747 "e" 1748 ], 1749 "created_at": "2020-05-14 22:50:19.864085+00:00", 1750 "distinct_count": 100, 1751 "histo_col_type": "", 1752 "name": "__auto__", 1753 "null_count": 1000, 1754 "row_count": 10000 1755 }, 1756 { 1757 "columns": [ 1758 "f" 1759 ], 1760 "created_at": "2020-05-14 22:50:19.864085+00:00", 1761 "distinct_count": 5000, 1762 "histo_col_type": "", 1763 "name": "__auto__", 1764 "null_count": 100, 1765 "row_count": 10000 1766 } 1767 ]' 1768 ---- 1769 1770 opt 1771 SELECT * FROM multi_col 1772 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1773 AND b = true 1774 AND c = 5 1775 AND d = 'foo' 1776 AND e > 10 AND e <= 20 1777 AND f > 0 1778 ---- 1779 select 1780 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) f:6(float!null) 1781 ├── stats: [rows=8.09111244, distinct(1)=1, null(1)=0, distinct(2)=2, null(2)=0, distinct(3)=1, null(3)=0, distinct(4)=1, null(4)=0, distinct(5)=8.09111244, null(5)=0, distinct(6)=8.09111244, null(6)=0, distinct(1,3,4)=1, null(1,3,4)=0, distinct(1-6)=8.09111244, null(1-6)=0] 1782 │ histogram(2)= 0 8.0911 1783 │ <--- true 1784 │ histogram(4)= 0 8.0911 1785 │ <--- 'foo' 1786 ├── fd: ()-->(1-4) 1787 ├── index-join multi_col 1788 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1789 │ ├── stats: [rows=8.09114009] 1790 │ ├── fd: ()-->(1-4) 1791 │ └── scan multi_col@abcde_idx 1792 │ ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int!null) d:4(string!null) e:5(int!null) rowid:7(int!null) 1793 │ ├── constraint: /1/2/-3/4/5/7: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/true/5/'foo'/11 - /'37685f26-4b07-40ba-9bbf-42916ed9bc61'/true/5/'foo'/20] 1794 │ ├── stats: [rows=8.09114009, distinct(1)=1, null(1)=0, distinct(2)=2, null(2)=0, distinct(3)=1, null(3)=0, distinct(4)=1, null(4)=0, distinct(5)=8.09114009, null(5)=0, distinct(1,3,4)=1, null(1,3,4)=0, distinct(1-5)=8.09114009, null(1-5)=0] 1795 │ │ histogram(2)= 0 8.0911 1796 │ │ <--- true 1797 │ │ histogram(4)= 0 8.0911 1798 │ │ <--- 'foo' 1799 │ ├── key: (7) 1800 │ └── fd: ()-->(1-4), (7)-->(5) 1801 └── filters 1802 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1803 1804 # A different combination of predicates. 1805 opt 1806 SELECT * FROM multi_col 1807 WHERE b = true 1808 AND c = 5 1809 AND e IN (1, 3, 5, 7, 9) 1810 AND f > 0 1811 ---- 1812 select 1813 ├── columns: a:1(uuid) b:2(bool!null) c:3(int!null) d:4(string) e:5(int!null) f:6(float!null) 1814 ├── stats: [rows=81.87, distinct(2)=2, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=5, null(5)=0, distinct(6)=81.87, null(6)=0, distinct(2,3,5,6)=81.87, null(2,3,5,6)=0] 1815 │ histogram(2)= 0 81.87 1816 │ <--- true 1817 ├── fd: ()-->(2,3) 1818 ├── index-join multi_col 1819 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1820 │ ├── stats: [rows=45.0078177] 1821 │ ├── fd: ()-->(3) 1822 │ └── scan multi_col@ce_idx 1823 │ ├── columns: c:3(int!null) e:5(int!null) rowid:7(int!null) 1824 │ ├── constraint: /3/5/7 1825 │ │ ├── [/5/1 - /5/1] 1826 │ │ ├── [/5/3 - /5/3] 1827 │ │ ├── [/5/5 - /5/5] 1828 │ │ ├── [/5/7 - /5/7] 1829 │ │ └── [/5/9 - /5/9] 1830 │ ├── stats: [rows=45.0078177, distinct(3)=1, null(3)=0, distinct(5)=5, null(5)=0, distinct(3,5)=5, null(3,5)=0] 1831 │ ├── key: (7) 1832 │ └── fd: ()-->(3), (7)-->(5) 1833 └── filters 1834 ├── b:2 = true [type=bool, outer=(2), constraints=(/2: [/true - /true]; tight), fd=()-->(2)] 1835 └── f:6 > 0.0 [type=bool, outer=(6), constraints=(/6: [/5e-324 - ]; tight)] 1836 1837 # Force the alternate index. 1838 opt 1839 SELECT * FROM multi_col@bef_idx 1840 WHERE b = true 1841 AND c = 5 1842 AND e IN (1, 3, 5, 7, 9) 1843 AND f > 0 1844 ---- 1845 select 1846 ├── columns: a:1(uuid) b:2(bool!null) c:3(int!null) d:4(string) e:5(int!null) f:6(float!null) 1847 ├── stats: [rows=81.87, distinct(2)=2, null(2)=0, distinct(3)=1, null(3)=0, distinct(5)=5, null(5)=0, distinct(6)=81.87, null(6)=0, distinct(2,3,5,6)=81.87, null(2,3,5,6)=0] 1848 │ histogram(2)= 0 81.87 1849 │ <--- true 1850 ├── fd: ()-->(2,3) 1851 ├── index-join multi_col 1852 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1853 │ ├── stats: [rows=416.509091] 1854 │ ├── fd: ()-->(2) 1855 │ └── scan multi_col@bef_idx 1856 │ ├── columns: b:2(bool!null) e:5(int!null) f:6(float!null) rowid:7(int!null) 1857 │ ├── constraint: /2/5/6/7 1858 │ │ ├── [/true/1/5e-324 - /true/1] 1859 │ │ ├── [/true/3/5e-324 - /true/3] 1860 │ │ ├── [/true/5/5e-324 - /true/5] 1861 │ │ ├── [/true/7/5e-324 - /true/7] 1862 │ │ └── [/true/9/5e-324 - /true/9] 1863 │ ├── flags: force-index=bef_idx 1864 │ ├── stats: [rows=416.509091, distinct(2)=2, null(2)=0, distinct(5)=5, null(5)=0, distinct(6)=416.509091, null(6)=0, distinct(2,5,6)=416.509091, null(2,5,6)=0] 1865 │ │ histogram(2)= 0 416.51 1866 │ │ <--- true 1867 │ ├── key: (7) 1868 │ └── fd: ()-->(2), (7)-->(5,6) 1869 └── filters 1870 └── c:3 = 5 [type=bool, outer=(3), constraints=(/3: [/5 - /5]; tight), fd=()-->(3)] 1871 1872 # A different combination of predicates, with four different combinations of 1873 # values for b and d. 1874 1875 opt 1876 SELECT * FROM multi_col 1877 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1878 AND b = true 1879 AND d = 'foo' 1880 AND e = 5 1881 AND f = 0 1882 ---- 1883 select 1884 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int) d:4(string!null) e:5(int!null) f:6(float!null) 1885 ├── stats: [rows=8.09028187, distinct(1)=1, null(1)=0, distinct(2)=2, null(2)=0, distinct(4)=1, null(4)=0, distinct(5)=1, null(5)=0, distinct(6)=1, null(6)=0, distinct(1,4-6)=1, null(1,4-6)=0, distinct(1,2,4-6)=2, null(1,2,4-6)=0] 1886 │ histogram(2)= 0 8.0903 1887 │ <--- true 1888 │ histogram(4)= 0 8.0903 1889 │ <--- 'foo' 1890 ├── fd: ()-->(1,2,4-6) 1891 ├── index-join multi_col 1892 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1893 │ ├── stats: [rows=0.811629073] 1894 │ ├── fd: ()-->(2,5,6) 1895 │ └── scan multi_col@bef_idx 1896 │ ├── columns: b:2(bool!null) e:5(int!null) f:6(float!null) rowid:7(int!null) 1897 │ ├── constraint: /2/5/6/7: [/true/5/0.0 - /true/5/0.0] 1898 │ ├── stats: [rows=0.811629073, distinct(2)=0.811629073, null(2)=0, distinct(5)=0.811629073, null(5)=0, distinct(6)=0.811629073, null(6)=0, distinct(5,6)=0.811629073, null(5,6)=0, distinct(2,5,6)=0.811629073, null(2,5,6)=0] 1899 │ │ histogram(2)= 0 0.81163 1900 │ │ <--- true - 1901 │ ├── key: (7) 1902 │ └── fd: ()-->(2,5,6) 1903 └── filters 1904 ├── a:1 = '37685f26-4b07-40ba-9bbf-42916ed9bc61' [type=bool, outer=(1), constraints=(/1: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61' - /'37685f26-4b07-40ba-9bbf-42916ed9bc61']; tight), fd=()-->(1)] 1905 └── d:4 = 'foo' [type=bool, outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)] 1906 1907 opt 1908 SELECT * FROM multi_col 1909 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1910 AND b = true 1911 AND d = 'bar' 1912 AND e = 5 1913 AND f = 0 1914 ---- 1915 select 1916 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int) d:4(string!null) e:5(int!null) f:6(float!null) 1917 ├── stats: [rows=0.000809838025, distinct(1)=0.000809838025, null(1)=0, distinct(2)=0.000809838025, null(2)=0, distinct(4)=0.000809838025, null(4)=0, distinct(5)=0.000809838025, null(5)=0, distinct(6)=0.000809838025, null(6)=0, distinct(1,4-6)=0.000809838025, null(1,4-6)=0, distinct(1,2,4-6)=0.000809838025, null(1,2,4-6)=0] 1918 │ histogram(2)= 0 0.00080984 1919 │ <----- true -- 1920 │ histogram(4)= 0 0.00080984 1921 │ <---- 'bar' -- 1922 ├── fd: ()-->(1,2,4-6) 1923 ├── index-join multi_col 1924 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1925 │ ├── stats: [rows=0.0008238352] 1926 │ ├── fd: ()-->(1,2,4) 1927 │ └── scan multi_col@bad_idx 1928 │ ├── columns: a:1(uuid!null) b:2(bool!null) d:4(string!null) rowid:7(int!null) 1929 │ ├── constraint: /2/-1/4/7: [/true/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/'bar' - /true/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/'bar'] 1930 │ ├── stats: [rows=0.0008238352, distinct(1)=0.0008238352, null(1)=0, distinct(2)=0.0008238352, null(2)=0, distinct(4)=0.0008238352, null(4)=0, distinct(1,4)=0.0008238352, null(1,4)=0, distinct(1,2,4)=0.0008238352, null(1,2,4)=0] 1931 │ │ histogram(2)= 0 0.00082384 1932 │ │ <----- true -- 1933 │ │ histogram(4)= 0 0.00082384 1934 │ │ <---- 'bar' -- 1935 │ ├── key: (7) 1936 │ └── fd: ()-->(1,2,4) 1937 └── filters 1938 ├── e:5 = 5 [type=bool, outer=(5), constraints=(/5: [/5 - /5]; tight), fd=()-->(5)] 1939 └── f:6 = 0.0 [type=bool, outer=(6), constraints=(/6: [/0.0 - /0.0]; tight), fd=()-->(6)] 1940 1941 opt 1942 SELECT * FROM multi_col 1943 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1944 AND b = false 1945 AND d = 'bar' 1946 AND e = 5 1947 AND f = 0 1948 ---- 1949 select 1950 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int) d:4(string!null) e:5(int!null) f:6(float!null) 1951 ├── stats: [rows=1.62000005e-07, distinct(1)=1.62000005e-07, null(1)=0, distinct(2)=1.62000005e-07, null(2)=0, distinct(4)=1.62000005e-07, null(4)=0, distinct(5)=1.62000005e-07, null(5)=0, distinct(6)=1.62000005e-07, null(6)=0, distinct(1,4-6)=1.62000005e-07, null(1,4-6)=0, distinct(1,2,4-6)=1.62000005e-07, null(1,2,4-6)=0] 1952 │ histogram(2)= 0 1.62e-07 1953 │ <--- false - 1954 │ histogram(4)= 0 1.62e-07 1955 │ <--- 'bar' - 1956 ├── fd: ()-->(1,2,4-6) 1957 ├── index-join multi_col 1958 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1959 │ ├── stats: [rows=1.648e-07] 1960 │ ├── fd: ()-->(1,2,4) 1961 │ └── scan multi_col@bad_idx 1962 │ ├── columns: a:1(uuid!null) b:2(bool!null) d:4(string!null) rowid:7(int!null) 1963 │ ├── constraint: /2/-1/4/7: [/false/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/'bar' - /false/'37685f26-4b07-40ba-9bbf-42916ed9bc61'/'bar'] 1964 │ ├── stats: [rows=1.648e-07, distinct(1)=1.648e-07, null(1)=0, distinct(2)=1.648e-07, null(2)=0, distinct(4)=1.648e-07, null(4)=0, distinct(1,4)=1.648e-07, null(1,4)=0, distinct(1,2,4)=1.648e-07, null(1,2,4)=0] 1965 │ │ histogram(2)= 0 1.648e-07 1966 │ │ <---- false - 1967 │ │ histogram(4)= 0 1.648e-07 1968 │ │ <---- 'bar' - 1969 │ ├── key: (7) 1970 │ └── fd: ()-->(1,2,4) 1971 └── filters 1972 ├── e:5 = 5 [type=bool, outer=(5), constraints=(/5: [/5 - /5]; tight), fd=()-->(5)] 1973 └── f:6 = 0.0 [type=bool, outer=(6), constraints=(/6: [/0.0 - /0.0]; tight), fd=()-->(6)] 1974 1975 opt 1976 SELECT * FROM multi_col 1977 WHERE a = '37685f26-4b07-40ba-9bbf-42916ed9bc61' 1978 AND b = false 1979 AND d = 'foo' 1980 AND e = 5 1981 AND f = 0 1982 ---- 1983 select 1984 ├── columns: a:1(uuid!null) b:2(bool!null) c:3(int) d:4(string!null) e:5(int!null) f:6(float!null) 1985 ├── stats: [rows=0.00161838005, distinct(1)=0.00161838005, null(1)=0, distinct(2)=0.00161838005, null(2)=0, distinct(4)=0.00161838005, null(4)=0, distinct(5)=0.00161838005, null(5)=0, distinct(6)=0.00161838005, null(6)=0, distinct(1,4-6)=0.00161838005, null(1,4-6)=0, distinct(1,2,4-6)=0.00161838005, null(1,2,4-6)=0] 1986 │ histogram(2)= 0 0.0016184 1987 │ <---- false - 1988 │ histogram(4)= 0 0.0016184 1989 │ <---- 'foo' - 1990 ├── fd: ()-->(1,2,4-6) 1991 ├── index-join multi_col 1992 │ ├── columns: a:1(uuid) b:2(bool) c:3(int) d:4(string) e:5(int) f:6(float) 1993 │ ├── stats: [rows=0.000162358286] 1994 │ ├── fd: ()-->(2,5,6) 1995 │ └── scan multi_col@bef_idx 1996 │ ├── columns: b:2(bool!null) e:5(int!null) f:6(float!null) rowid:7(int!null) 1997 │ ├── constraint: /2/5/6/7: [/false/5/0.0 - /false/5/0.0] 1998 │ ├── stats: [rows=0.000162358286, distinct(2)=0.000162358286, null(2)=0, distinct(5)=0.000162358286, null(5)=0, distinct(6)=0.000162358286, null(6)=0, distinct(5,6)=0.000162358286, null(5,6)=0, distinct(2,5,6)=0.000162358286, null(2,5,6)=0] 1999 │ │ histogram(2)= 0 0.00016236 2000 │ │ <---- false -- 2001 │ ├── key: (7) 2002 │ └── fd: ()-->(2,5,6) 2003 └── filters 2004 ├── a:1 = '37685f26-4b07-40ba-9bbf-42916ed9bc61' [type=bool, outer=(1), constraints=(/1: [/'37685f26-4b07-40ba-9bbf-42916ed9bc61' - /'37685f26-4b07-40ba-9bbf-42916ed9bc61']; tight), fd=()-->(1)] 2005 └── d:4 = 'foo' [type=bool, outer=(4), constraints=(/4: [/'foo' - /'foo']; tight), fd=()-->(4)]