📝 Introduction
YugabyteDB’s cost-based optimizer (CBO) is designed to pick the lowest-cost execution plan. When using duplicate covering geo-localized indexes, this usually means the CBO should choose the index located in the same region as the connected client.
However, in some cases, the CBO incorrectly prefers the global primary key (PK) index, even when a local covering index would be dramatically faster.
This happens even when geolocation costing is enabled.
This tip shows:
● A minimal reproducible example reproducing the issue
● Why the CBO makes the wrong decision
● A work-around that fixes index selection without requiring query hints or application changes
● A link to the public GitHub issue tracking this behavior
Everything below is fully reproducible using a simple 3-region test environment.
🙏 Note: I’d like to thank my colleague, Gaurav Kukreja, Staff Engineer at Yugabyte, for providing the workaround featured in this YugabyteDB Tip!
📦 Test Setup
Before we discuss the optimizer’s behavior, here are the table and index definitions used in this demonstration.
1️⃣ Regional Tablespaces
CREATE TABLESPACE us_east_1_ts WITH (
replica_placement='{
"num_replicas":3,
"placement_blocks":[
{"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1,"leader_preference":1},
{"cloud":"aws","region":"us-east-2","zone":"us-east-2a","min_num_replicas":1,"leader_preference":2},
{"cloud":"aws","region":"us-west-2","zone":"us-west-2a","min_num_replicas":1}
]
}'
);
CREATE TABLESPACE us_east_2_ts WITH (
replica_placement='{
"num_replicas":3,
"placement_blocks":[
{"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1,"leader_preference":2},
{"cloud":"aws","region":"us-east-2","zone":"us-east-2a","min_num_replicas":1,"leader_preference":1},
{"cloud":"aws","region":"us-west-2","zone":"us-west-2a","min_num_replicas":1}
]
}'
);
2️⃣ Table and Index Definitions
Here are the exact objects involved in the demonstration.
Base Table (default tablespace)
CREATE TABLE t1 (
c1 TEXT,
c2 TEXT,
c3 TEXT,
PRIMARY KEY (c1 HASH)
);
Two regional covering indexes
Each contains the same columns and includes (c2,c3); the only difference is the tablespace location.
CREATE INDEX t1_c2_lower_us_east_1_ts_v2
ON t1 (c1 HASH, lower(c2))
INCLUDE (c2, c3)
TABLESPACE us_east_1_ts;
CREATE INDEX t1_c2_lower_us_east_2_ts_v2
ON t1 (c1 HASH, lower(c2))
INCLUDE (c2, c3)
TABLESPACE us_east_2_ts;
3️⃣ Load Sample Data
INSERT INTO t1
SELECT md5(s::text), md5(s::text), md5(s::text)
FROM generate_series(1, 10000) s;
ANALYZE t1;
4️⃣ Enable CBO and Geolocation Costing
SET yb_enable_cbo = ON;
SET yb_enable_geolocation_costing = ON;
🧪 Reproducing the Issue
Assume the session is connected in us-east-1.
We run a query that should use the local covering index:
EXPLAIN
SELECT *
FROM t1
WHERE LOWER(c2) = md5('a')
AND c1 = md5('A');
❌ Unexpected Plan: PK Chosen
Index Scan using t1_pkey on t1
Index Cond: (c1 = '...')
Storage Filter: (lower(c2) = '...')
Even though the local covering index exists:
t1_c2_lower_us_east_1_ts_v2
… the optimizer ignores it and uses the default-tablespace PK, which may be remote.
Why this matters
- ● The PK requires a Read + Remote RPC, plus a storage-level filter.
- ● The covering index avoids that completely.
- ● In real workloads, the difference can be 10×–200× faster.
🤔 Why the Wrong Index Is Chosen
In this situation:
● The table and primary key are in the default tablespace (
tablespace_id = 0)● The cost model treats the default tablespace as local
● Therefore the PK index appears to have the same startup cost as the local regional index
● Without a locality penalty, the optimizer sometimes prefers the PK simply because of its slightly lower base cost
This is unintended behavior and is currently being worked on!
👉 Public GitHub issue:
https://github.com/yugabyte/yugabyte-db/issues/29123
✅ Work-Around (No Hints, No App Changes)
The best current workaround:
- Place the base table into an explicit “distributed” tablespace. This gives the CBO accurate locality metadata for both the table and its PK index.
Step 1: Create a distributed tablespace
CREATE TABLESPACE us_distributed_ts WITH (
replica_placement='{
"num_replicas":3,
"placement_blocks":[
{"cloud":"aws","region":"us-east-1","zone":"us-east-1a","min_num_replicas":1},
{"cloud":"aws","region":"us-east-2","zone":"us-east-2a","min_num_replicas":1},
{"cloud":"aws","region":"us-west-2","zone":"us-west-2a","min_num_replicas":1}
]
}'
);
Step 2: Recreate table in this tablespace
CREATE TABLE t1 (
c1 TEXT,
c2 TEXT,
c3 TEXT,
PRIMARY KEY (c1 HASH)
) TABLESPACE us_distributed_ts;
Step 3: Recreate the localized indexes; reload data; analyze
Step 4: Re-run the test query
EXPLAIN
SELECT *
FROM t1
WHERE LOWER(c2) = md5('a')
AND c1 = md5('A');
🎉 Correct plan (after workaround)
Index Only Scan using t1_c2_lower_us_east_1_ts_v2
This matches expectations and avoids cross-region lookups.
🧾 Summary
In certain multi-region deployments, YugabyteDB’s cost-based optimizer may incorrectly choose the global primary-key index instead of a local covering index, even when geolocation costing is enabled. This leads to unnecessary cross-region lookups and significantly slower performance.
The root cause is that tables (and their primary-key indexes) stored in the default tablespace are currently treated as “local” by the optimizer, regardless of the region. Because no locality penalty is applied, the PK can appear cheaper than the region-specific covering index, causing the suboptimal plan choice.
The most effective workaround is to move the table into a non-default distributed tablespace so that locality information is correctly applied to both the table and its indexes. This immediately causes the optimizer to choose the correct local covering index—no query hints, CTE rewrites, or application changes required.
A long-term fix is underway, and progress is being tracked publicly under GitHub issue #29123. I will update this tip when the workaround is no longer needed!
Have Fun!
