github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/xform/testdata/coster/zone (about) 1 exec-ddl 2 CREATE TABLE abc ( 3 a INT PRIMARY KEY, 4 b INT, 5 c STRING, 6 UNIQUE INDEX bc1 (b, c), 7 UNIQUE INDEX bc2 (b, c) 8 ) 9 ---- 10 11 exec-ddl 12 CREATE TABLE xy ( 13 x INT PRIMARY KEY, 14 y INT, 15 INDEX y1 (y), 16 INDEX y2 (y) 17 ) 18 ---- 19 20 # -------------------------------------------------- 21 # Single constraints. 22 # -------------------------------------------------- 23 24 exec-ddl 25 ALTER TABLE abc CONFIGURE ZONE USING constraints='[+region=central]' 26 ---- 27 28 exec-ddl 29 ALTER INDEX abc@bc1 CONFIGURE ZONE USING constraints='[+region=east]' 30 ---- 31 32 exec-ddl 33 ALTER INDEX abc@bc2 CONFIGURE ZONE USING constraints='[+region=west]' 34 ---- 35 36 # With locality in central, use primary index. 37 opt format=show-all locality=(region=central) 38 SELECT * FROM abc 39 ---- 40 scan t.public.abc 41 ├── columns: a:1(int!null) b:2(int) c:3(string) 42 ├── stats: [rows=1000] 43 ├── cost: 1060.02 44 ├── key: (1) 45 ├── fd: (1)-->(2,3), (2,3)~~>(1) 46 ├── prune: (1-3) 47 └── interesting orderings: (+1) (+2,+3,+1) 48 49 # With locality in central, still use bc1 index when the filter is selective. 50 opt format=show-all locality=(region=central) 51 SELECT * FROM abc WHERE b=10 52 ---- 53 scan t.public.abc@bc1 54 ├── columns: a:1(int!null) b:2(int!null) c:3(string) 55 ├── constraint: /2/3: [/10 - /10] 56 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 57 ├── cost: 11.21 58 ├── key: (1) 59 ├── fd: ()-->(2), (1)-->(3), (2,3)~~>(1) 60 ├── prune: (1,3) 61 └── interesting orderings: (+1) (+2,+3,+1) 62 63 # With locality in east, use bc1 index. 64 opt format=show-all locality=(region=east) 65 SELECT b, c FROM abc WHERE b=10 66 ---- 67 scan t.public.abc@bc1 68 ├── columns: b:2(int!null) c:3(string) 69 ├── constraint: /2/3: [/10 - /10] 70 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 71 ├── cost: 10.51 72 ├── lax-key: (3) 73 ├── fd: ()-->(2) 74 ├── prune: (3) 75 └── interesting orderings: (+2,+3) 76 77 # With locality in west, use bc2 index. 78 opt format=show-all locality=(region=west) 79 SELECT b, c FROM abc WHERE b=10 80 ---- 81 scan t.public.abc@bc2 82 ├── columns: b:2(int!null) c:3(string) 83 ├── constraint: /2/3: [/10 - /10] 84 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 85 ├── cost: 10.51 86 ├── lax-key: (3) 87 ├── fd: ()-->(2) 88 ├── prune: (3) 89 └── interesting orderings: (+2,+3) 90 91 # No locality, so use bc1, since it's first. 92 opt format=show-all 93 SELECT b, c FROM abc WHERE b=10 94 ---- 95 scan t.public.abc@bc1 96 ├── columns: b:2(int!null) c:3(string) 97 ├── constraint: /2/3: [/10 - /10] 98 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 99 ├── cost: 10.51 100 ├── lax-key: (3) 101 ├── fd: ()-->(2) 102 ├── prune: (3) 103 └── interesting orderings: (+2,+3) 104 105 # Locality doesn't match any constraints, so use bc1, since it's first. 106 opt format=show-all locality=(region=central) 107 SELECT b, c FROM abc WHERE b=10 108 ---- 109 scan t.public.abc@bc1 110 ├── columns: b:2(int!null) c:3(string) 111 ├── constraint: /2/3: [/10 - /10] 112 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 113 ├── cost: 11.01 114 ├── lax-key: (3) 115 ├── fd: ()-->(2) 116 ├── prune: (3) 117 └── interesting orderings: (+2,+3) 118 119 # -------------------------------------------------- 120 # Multiple constraints. 121 # -------------------------------------------------- 122 123 exec-ddl 124 ALTER TABLE abc CONFIGURE ZONE USING constraints='[+region=us,+dc=central,+rack=1]' 125 ---- 126 127 exec-ddl 128 ALTER INDEX abc@bc1 CONFIGURE ZONE USING constraints='[+region=us,+dc=east,+rack=1]' 129 ---- 130 131 # Do not specify region constraint. 132 exec-ddl 133 ALTER INDEX abc@bc2 CONFIGURE ZONE USING constraints='[+dc=west]' 134 ---- 135 136 # With locality in us + central, use primary index. 137 opt format=show-all locality=(region=us,dc=central) 138 SELECT * FROM abc 139 ---- 140 scan t.public.abc 141 ├── columns: a:1(int!null) b:2(int) c:3(string) 142 ├── stats: [rows=1000] 143 ├── cost: 1060.02 144 ├── key: (1) 145 ├── fd: (1)-->(2,3), (2,3)~~>(1) 146 ├── prune: (1-3) 147 └── interesting orderings: (+1) (+2,+3,+1) 148 149 # With locality in us + central, still use bc1 index if filter is selective. 150 opt format=show-all locality=(region=us,dc=central) 151 SELECT b, c FROM abc WHERE b=10 152 ---- 153 scan t.public.abc@bc1 154 ├── columns: b:2(int!null) c:3(string) 155 ├── constraint: /2/3: [/10 - /10] 156 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 157 ├── cost: 10.76 158 ├── lax-key: (3) 159 ├── fd: ()-->(2) 160 ├── prune: (3) 161 └── interesting orderings: (+2,+3) 162 163 # With locality in us + east, use bc1 index. 164 opt format=show-all locality=(region=us,dc=east) 165 SELECT b, c FROM abc WHERE b=10 166 ---- 167 scan t.public.abc@bc1 168 ├── columns: b:2(int!null) c:3(string) 169 ├── constraint: /2/3: [/10 - /10] 170 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 171 ├── cost: 10.51 172 ├── lax-key: (3) 173 ├── fd: ()-->(2) 174 ├── prune: (3) 175 └── interesting orderings: (+2,+3) 176 177 # With locality in us + west, use bc2 index, even though region does not match 178 # any constraint on the index. 179 opt format=show-all locality=(region=us,dc=west) 180 SELECT b, c FROM abc WHERE b=10 181 ---- 182 scan t.public.abc@bc2 183 ├── columns: b:2(int!null) c:3(string) 184 ├── constraint: /2/3: [/10 - /10] 185 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 186 ├── cost: 10.51 187 ├── lax-key: (3) 188 ├── fd: ()-->(2) 189 ├── prune: (3) 190 └── interesting orderings: (+2,+3) 191 192 # -------------------------------------------------- 193 # Multiple replica constraints. 194 # -------------------------------------------------- 195 196 exec-ddl 197 ALTER INDEX abc@bc1 CONFIGURE ZONE USING constraints='{"+region=us,+dc=east":2, "+region=us,+dc=west":1}' 198 ---- 199 200 exec-ddl 201 ALTER INDEX abc@bc2 CONFIGURE ZONE USING constraints='[+dc=east]' 202 ---- 203 204 # With locality in us, use bc1 index, since only one tier matches in case of 205 # both indexes. 206 opt format=show-all locality=(region=us) 207 SELECT b, c FROM abc WHERE b=10 208 ---- 209 scan t.public.abc@bc1 210 ├── columns: b:2(int!null) c:3(string) 211 ├── constraint: /2/3: [/10 - /10] 212 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 213 ├── cost: 10.51 214 ├── lax-key: (3) 215 ├── fd: ()-->(2) 216 ├── prune: (3) 217 └── interesting orderings: (+2,+3) 218 219 # With locality in us + east, use bc2 index (use lowest match count when 220 # replicas have different numbers of matches). 221 opt format=show-all locality=(region=us,dc=east) 222 SELECT b, c FROM abc WHERE b=10 223 ---- 224 scan t.public.abc@bc2 225 ├── columns: b:2(int!null) c:3(string) 226 ├── constraint: /2/3: [/10 - /10] 227 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 228 ├── cost: 10.51 229 ├── lax-key: (3) 230 ├── fd: ()-->(2) 231 ├── prune: (3) 232 └── interesting orderings: (+2,+3) 233 234 # -------------------------------------------------- 235 # Complex constraints. 236 # -------------------------------------------------- 237 238 exec-ddl 239 ALTER INDEX abc@bc1 CONFIGURE ZONE USING constraints='[+region=us,-region=eu,+region=ap]' 240 ---- 241 242 exec-ddl 243 ALTER INDEX abc@bc2 CONFIGURE ZONE USING constraints='[+region=eu,+region=us,+dc=east]' 244 ---- 245 246 # With locality in us, use bc1, since it's first in order. 247 opt format=show-all locality=(region=us) 248 SELECT b, c FROM abc WHERE b=10 249 ---- 250 scan t.public.abc@bc1 251 ├── columns: b:2(int!null) c:3(string) 252 ├── constraint: /2/3: [/10 - /10] 253 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 254 ├── cost: 10.51 255 ├── lax-key: (3) 256 ├── fd: ()-->(2) 257 ├── prune: (3) 258 └── interesting orderings: (+2,+3) 259 260 # With locality in eu, use bc2, since it's prohibited with bc1. 261 opt format=show-all locality=(region=eu) 262 SELECT b, c FROM abc WHERE b=10 263 ---- 264 scan t.public.abc@bc2 265 ├── columns: b:2(int!null) c:3(string) 266 ├── constraint: /2/3: [/10 - /10] 267 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 268 ├── cost: 10.51 269 ├── lax-key: (3) 270 ├── fd: ()-->(2) 271 ├── prune: (3) 272 └── interesting orderings: (+2,+3) 273 274 # With locality in us + east, use bc2, since it matches both tiers, even though 275 # "us" match is after "eu" in list. 276 opt format=show-all locality=(region=us,dc=east) 277 SELECT b, c FROM abc WHERE b=10 278 ---- 279 scan t.public.abc@bc2 280 ├── columns: b:2(int!null) c:3(string) 281 ├── constraint: /2/3: [/10 - /10] 282 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 283 ├── cost: 10.51 284 ├── lax-key: (3) 285 ├── fd: ()-->(2) 286 ├── prune: (3) 287 └── interesting orderings: (+2,+3) 288 289 # With locality in ap + east, use bc1, since ap is not in list of regions for 290 # bc2, even though dc=east matches. 291 opt format=show-all locality=(region=ap,dc=east) 292 SELECT b, c FROM abc WHERE b=10 293 ---- 294 scan t.public.abc@bc1 295 ├── columns: b:2(int!null) c:3(string) 296 ├── constraint: /2/3: [/10 - /10] 297 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 298 ├── cost: 10.76 299 ├── lax-key: (3) 300 ├── fd: ()-->(2) 301 ├── prune: (3) 302 └── interesting orderings: (+2,+3) 303 304 exec-ddl 305 ALTER INDEX abc@bc1 CONFIGURE ZONE USING constraints='[-region=eu,+dc=east]' 306 ---- 307 308 exec-ddl 309 ALTER INDEX abc@bc2 CONFIGURE ZONE USING constraints='[+dc=east]' 310 ---- 311 312 # With locality in us + east, use bc1, since it's first in order. 313 opt format=show-all locality=(region=us,dc=east) 314 SELECT b, c FROM abc WHERE b=10 315 ---- 316 scan t.public.abc@bc1 317 ├── columns: b:2(int!null) c:3(string) 318 ├── constraint: /2/3: [/10 - /10] 319 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 320 ├── cost: 10.51 321 ├── lax-key: (3) 322 ├── fd: ()-->(2) 323 ├── prune: (3) 324 └── interesting orderings: (+2,+3) 325 326 # With locality in eu + east, use bc2, since eu is prohibited for bc1. 327 opt format=show-all locality=(region=eu,dc=east) 328 SELECT b, c FROM abc WHERE b=10 329 ---- 330 scan t.public.abc@bc2 331 ├── columns: b:2(int!null) c:3(string) 332 ├── constraint: /2/3: [/10 - /10] 333 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 334 ├── cost: 10.51 335 ├── lax-key: (3) 336 ├── fd: ()-->(2) 337 ├── prune: (3) 338 └── interesting orderings: (+2,+3) 339 340 # -------------------------------------------------- 341 # Lookup join. 342 # -------------------------------------------------- 343 344 exec-ddl 345 ALTER INDEX abc@bc1 CONFIGURE ZONE USING constraints='[+region=us,+dc=east]' 346 ---- 347 348 exec-ddl 349 ALTER INDEX abc@bc2 CONFIGURE ZONE USING constraints='[+region=us,+dc=west]' 350 ---- 351 352 exec-ddl 353 ALTER INDEX xy@y1 CONFIGURE ZONE USING constraints='[+region=us,+dc=east]' 354 ---- 355 356 exec-ddl 357 ALTER INDEX xy@y2 CONFIGURE ZONE USING constraints='[+region=us,+dc=west]' 358 ---- 359 360 # Ensure that both indexes involved in the lookup join are selected from the 361 # "west" data center. 362 opt format=show-all locality=(region=us,dc=west) 363 SELECT * FROM abc INNER LOOKUP JOIN xy ON b=y WHERE b=1 364 ---- 365 inner-join (lookup xy@y2) 366 ├── columns: a:1(int!null) b:2(int!null) c:3(string) x:4(int!null) y:5(int!null) 367 ├── flags: force lookup join (into right side) 368 ├── key columns: [2] = [5] 369 ├── stats: [rows=100, distinct(2)=1, null(2)=0, distinct(5)=1, null(5)=0] 370 ├── cost: 412.59 371 ├── key: (1,4) 372 ├── fd: ()-->(2,5), (1)-->(3), (2,3)~~>(1), (2)==(5), (5)==(2) 373 ├── prune: (1,3,4) 374 ├── interesting orderings: (+1) (+2,+3,+1) 375 ├── scan t.public.abc@bc2 376 │ ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(string) 377 │ ├── constraint: /2/3: [/1 - /1] 378 │ ├── stats: [rows=10, distinct(1)=10, null(1)=0, distinct(2)=1, null(2)=0] 379 │ ├── cost: 10.61 380 │ ├── key: (1) 381 │ ├── fd: ()-->(2), (1)-->(3), (2,3)~~>(1) 382 │ ├── prune: (1,3) 383 │ └── interesting orderings: (+1) (+2,+3,+1) 384 └── filters 385 └── eq [type=bool, outer=(5), constraints=(/5: [/1 - /1]; tight), fd=()-->(5)] 386 ├── variable: t.public.xy.y:5 [type=int] 387 └── const: 1 [type=int] 388 389 # Switch the data center for the target lookup join index. 390 391 exec-ddl 392 ALTER INDEX xy@y1 CONFIGURE ZONE USING constraints='[+region=us,+dc=west]' 393 ---- 394 395 exec-ddl 396 ALTER INDEX xy@y2 CONFIGURE ZONE USING constraints='[+region=us,+dc=east]' 397 ---- 398 399 # Should use other index now. 400 opt format=show-all locality=(region=us,dc=west) 401 SELECT * FROM abc INNER LOOKUP JOIN xy ON b=y WHERE b=1 402 ---- 403 inner-join (lookup xy@y1) 404 ├── columns: a:1(int!null) b:2(int!null) c:3(string) x:4(int!null) y:5(int!null) 405 ├── flags: force lookup join (into right side) 406 ├── key columns: [2] = [5] 407 ├── stats: [rows=100, distinct(2)=1, null(2)=0, distinct(5)=1, null(5)=0] 408 ├── cost: 412.59 409 ├── key: (1,4) 410 ├── fd: ()-->(2,5), (1)-->(3), (2,3)~~>(1), (2)==(5), (5)==(2) 411 ├── prune: (1,3,4) 412 ├── interesting orderings: (+1) (+2,+3,+1) 413 ├── scan t.public.abc@bc2 414 │ ├── columns: t.public.abc.a:1(int!null) t.public.abc.b:2(int!null) t.public.abc.c:3(string) 415 │ ├── constraint: /2/3: [/1 - /1] 416 │ ├── stats: [rows=10, distinct(1)=10, null(1)=0, distinct(2)=1, null(2)=0] 417 │ ├── cost: 10.61 418 │ ├── key: (1) 419 │ ├── fd: ()-->(2), (1)-->(3), (2,3)~~>(1) 420 │ ├── prune: (1,3) 421 │ └── interesting orderings: (+1) (+2,+3,+1) 422 └── filters 423 └── eq [type=bool, outer=(5), constraints=(/5: [/1 - /1]; tight), fd=()-->(5)] 424 ├── variable: t.public.xy.y:5 [type=int] 425 └── const: 1 [type=int] 426 427 # -------------------------------------------------- 428 # Lease preferences - single constraint. 429 # -------------------------------------------------- 430 431 exec-ddl 432 ALTER TABLE abc CONFIGURE ZONE USING lease_preferences='[[+region=central]]' 433 ---- 434 435 exec-ddl 436 ALTER INDEX abc@bc1 CONFIGURE ZONE USING lease_preferences='[[+region=east]]' 437 ---- 438 439 exec-ddl 440 ALTER INDEX abc@bc2 CONFIGURE ZONE USING lease_preferences='[[+region=west]]' 441 ---- 442 443 # With locality in us + central, use primary index. 444 opt format=show-all locality=(region=central) 445 SELECT * FROM abc 446 ---- 447 scan t.public.abc 448 ├── columns: a:1(int!null) b:2(int) c:3(string) 449 ├── stats: [rows=1000] 450 ├── cost: 1100.02 451 ├── key: (1) 452 ├── fd: (1)-->(2,3), (2,3)~~>(1) 453 ├── prune: (1-3) 454 └── interesting orderings: (+1) (+2,+3,+1) 455 456 # With locality in us + central, still use bc1 index if filter is selective. 457 opt format=show-all locality=(region=central) 458 SELECT b, c FROM abc WHERE b=10 459 ---- 460 scan t.public.abc@bc1 461 ├── columns: b:2(int!null) c:3(string) 462 ├── constraint: /2/3: [/10 - /10] 463 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 464 ├── cost: 11.01 465 ├── lax-key: (3) 466 ├── fd: ()-->(2) 467 ├── prune: (3) 468 └── interesting orderings: (+2,+3) 469 470 # With locality in east, use bc1 index. 471 opt format=show-all locality=(region=east) 472 SELECT b, c FROM abc WHERE b=10 473 ---- 474 scan t.public.abc@bc1 475 ├── columns: b:2(int!null) c:3(string) 476 ├── constraint: /2/3: [/10 - /10] 477 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 478 ├── cost: 10.8433333 479 ├── lax-key: (3) 480 ├── fd: ()-->(2) 481 ├── prune: (3) 482 └── interesting orderings: (+2,+3) 483 484 # With locality in west, use bc2 index. 485 opt format=show-all locality=(region=west) 486 SELECT b, c FROM abc WHERE b=10 487 ---- 488 scan t.public.abc@bc2 489 ├── columns: b:2(int!null) c:3(string) 490 ├── constraint: /2/3: [/10 - /10] 491 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 492 ├── cost: 10.8433333 493 ├── lax-key: (3) 494 ├── fd: ()-->(2) 495 ├── prune: (3) 496 └── interesting orderings: (+2,+3) 497 498 # -------------------------------------------------- 499 # Lease preferences - multiple constraints. 500 # -------------------------------------------------- 501 502 exec-ddl 503 ALTER TABLE abc CONFIGURE ZONE USING lease_preferences='[[+region=us,+dc=central,+rack=1]]' 504 ---- 505 506 exec-ddl 507 ALTER INDEX abc@bc1 CONFIGURE ZONE USING lease_preferences='[[+region=us,+dc=east,+rack=1]]' 508 ---- 509 510 exec-ddl 511 ALTER INDEX abc@bc2 CONFIGURE ZONE USING lease_preferences='[[+region=us,+dc=west,+rack=1]]' 512 ---- 513 514 # With locality in us + central, use primary index. 515 opt format=show-all locality=(region=us,dc=central) 516 SELECT * FROM abc 517 ---- 518 scan t.public.abc 519 ├── columns: a:1(int!null) b:2(int) c:3(string) 520 ├── stats: [rows=1000] 521 ├── cost: 1100.02 522 ├── key: (1) 523 ├── fd: (1)-->(2,3), (2,3)~~>(1) 524 ├── prune: (1-3) 525 └── interesting orderings: (+1) (+2,+3,+1) 526 527 # With locality in us + central, still use bc1 index if filter is selective. 528 opt format=show-all locality=(region=us,dc=central) 529 SELECT b, c FROM abc WHERE b=10 530 ---- 531 scan t.public.abc@bc1 532 ├── columns: b:2(int!null) c:3(string) 533 ├── constraint: /2/3: [/10 - /10] 534 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 535 ├── cost: 10.9266667 536 ├── lax-key: (3) 537 ├── fd: ()-->(2) 538 ├── prune: (3) 539 └── interesting orderings: (+2,+3) 540 541 # With locality in us + east, use bc1 index. 542 opt format=show-all locality=(region=us,dc=east) 543 SELECT b, c FROM abc WHERE b=10 544 ---- 545 scan t.public.abc@bc1 546 ├── columns: b:2(int!null) c:3(string) 547 ├── constraint: /2/3: [/10 - /10] 548 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 549 ├── cost: 10.8433333 550 ├── lax-key: (3) 551 ├── fd: ()-->(2) 552 ├── prune: (3) 553 └── interesting orderings: (+2,+3) 554 555 # With locality in us + west, use bc2 index. 556 opt format=show-all locality=(region=us,dc=west) 557 SELECT b, c FROM abc WHERE b=10 558 ---- 559 scan t.public.abc@bc2 560 ├── columns: b:2(int!null) c:3(string) 561 ├── constraint: /2/3: [/10 - /10] 562 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 563 ├── cost: 10.8433333 564 ├── lax-key: (3) 565 ├── fd: ()-->(2) 566 ├── prune: (3) 567 └── interesting orderings: (+2,+3) 568 569 # -------------------------------------------------- 570 # Zone constraint + leaseholder preference. 571 # -------------------------------------------------- 572 573 exec-ddl 574 ALTER TABLE abc CONFIGURE ZONE 575 USING constraints='[+region=us]', lease_preferences='[[+region=us,+dc=central]]' 576 ---- 577 578 exec-ddl 579 ALTER INDEX abc@bc1 CONFIGURE ZONE 580 USING constraints='[+region=us]', lease_preferences='[[+region=us,+dc=east]]' 581 ---- 582 583 exec-ddl 584 ALTER INDEX abc@bc2 CONFIGURE ZONE 585 USING constraints='[+region=us]', lease_preferences='[[+region=us,+dc=west]]' 586 ---- 587 588 # With locality in us + central, use primary index. 589 opt format=show-all locality=(region=us,dc=central) 590 SELECT * FROM abc 591 ---- 592 scan t.public.abc 593 ├── columns: a:1(int!null) b:2(int) c:3(string) 594 ├── stats: [rows=1000] 595 ├── cost: 1080.02 596 ├── key: (1) 597 ├── fd: (1)-->(2,3), (2,3)~~>(1) 598 ├── prune: (1-3) 599 └── interesting orderings: (+1) (+2,+3,+1) 600 601 # With locality in us + central, still use bc1 index if filter is selective. 602 opt format=show-all locality=(region=us,dc=central) 603 SELECT b, c FROM abc WHERE b=10 604 ---- 605 scan t.public.abc@bc1 606 ├── columns: b:2(int!null) c:3(string) 607 ├── constraint: /2/3: [/10 - /10] 608 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 609 ├── cost: 10.76 610 ├── lax-key: (3) 611 ├── fd: ()-->(2) 612 ├── prune: (3) 613 └── interesting orderings: (+2,+3) 614 615 # With locality in us + east, use bc1 index. 616 opt format=show-all locality=(region=us,dc=east) 617 SELECT b, c FROM abc WHERE b=10 618 ---- 619 scan t.public.abc@bc1 620 ├── columns: b:2(int!null) c:3(string) 621 ├── constraint: /2/3: [/10 - /10] 622 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 623 ├── cost: 10.6766667 624 ├── lax-key: (3) 625 ├── fd: ()-->(2) 626 ├── prune: (3) 627 └── interesting orderings: (+2,+3) 628 629 # With locality in us + west, use bc2 index. 630 opt format=show-all locality=(region=us,dc=west) 631 SELECT b, c FROM abc WHERE b=10 632 ---- 633 scan t.public.abc@bc2 634 ├── columns: b:2(int!null) c:3(string) 635 ├── constraint: /2/3: [/10 - /10] 636 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 637 ├── cost: 10.6766667 638 ├── lax-key: (3) 639 ├── fd: ()-->(2) 640 ├── prune: (3) 641 └── interesting orderings: (+2,+3) 642 643 exec-ddl 644 ALTER TABLE abc CONFIGURE ZONE USING constraints='[+region=us]' 645 ---- 646 647 exec-ddl 648 ALTER INDEX abc@bc1 CONFIGURE ZONE 649 USING constraints='[+region=us]', lease_preferences='[[+region=us,+dc=east]]' 650 ---- 651 652 exec-ddl 653 ALTER INDEX abc@bc2 CONFIGURE ZONE 654 USING constraints='[+region=us,+dc=east]' 655 ---- 656 657 # With locality in the east, prefer the index with the constraints over the 658 # index with just the lease preferences. 659 opt format=show-all locality=(region=us,dc=east) 660 SELECT b, c FROM abc WHERE b=10 661 ---- 662 scan t.public.abc@bc2 663 ├── columns: b:2(int!null) c:3(string) 664 ├── constraint: /2/3: [/10 - /10] 665 ├── stats: [rows=10, distinct(2)=1, null(2)=0] 666 ├── cost: 10.51 667 ├── lax-key: (3) 668 ├── fd: ()-->(2) 669 ├── prune: (3) 670 └── interesting orderings: (+2,+3)