Today, the optimizer will make some effort to create a query plan that avoids visiting remote regions if possible. However, there is no guarantee that any query will not visit a remote region. For some latency-sensitive applications, this may not be acceptable. In these cases, it would often be better to return an error rather than incur the high latency of a cross-region hop.

We should create a session setting that causes the optimizer and/or execution engine to error if it tries to visit a remote region. We should try to make the error message as friendly as possible to help the developer fix their query or data so that it does not visit a remote region. For example, if a query cannot use locality optimized search, we could include a hint suggesting that they add a limit to the query (so it can use LOS) or include crdb_region in the WHERE clause. If a query does use locality optimized search but the data is not found locally, we can provide a hint to that effect as well.

This will help provide guard rails for latency-sensitive apps that cannot tolerate cross-region latency.

Jira issue: CRDB-17283
Epic: CRDB-18645