opt: Optimizer uses index in wrong locality #36642
Description
REPRO:
- Start 3 nodes.
./cockroach start --insecure --locality=cloud=gce,region=us-east1,zone=us-east1-b --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080
./cockroach start --insecure --locality=cloud=gce,region=us-west1,zone=us-west1-b --store=node2 --listen-addr=localhost:26258 --http-addr=localhost:8081 --join=localhost:26257
./cockroach start --insecure --locality=cloud=gce,region=europe-west2,zone=europe-west2-b --store=node3 --listen-addr=localhost:26259 --http-addr=localhost:8082 --join=localhost:26257
- Connect to node tidy up some correctness issues reported by go vet #1 via the SQL CLI and issue the following commands:
CREATE TABLE t (
id INT PRIMARY KEY,
UNIQUE INDEX idx (id ASC)
);
ALTER INDEX t@idx CONFIGURE ZONE USING lease_preferences = '[[+cloud=gce,+region=us-east1,+zone=us-east1-b]]'
EXPLAIN (OPT) SELECT id FROM t WHERE id = 10
EXPECTED: The query should use the t@idx index, since its lease preferences make it preferable to the primary index.
ACTUAL: The query uses the primary index.
Metadata
Metadata
Assignees
Labels
No labels