Locality-optimized scans and joins with stale remote reads

**Is your feature request related to a problem? Please describe.**
Given a cluster configuration where the majority of the data a SQL connection reads is homed in the local region, a pattern for an app may be that it cares about serving up-to-date data for local rows, but for remote rows, stale data is OK, and what matters most is performance.

**Describe the solution you'd like**
Provide a new query modifier or session mode that causes the optimizer to produce locality-optimized scans and locality-optimized lookup joins on REGIONAL BY ROW tables where the local reads are consistent, and the remote reads use `AS OF SYSTEM TIME follower_read_timestamp()` if the request is not mutating any rows. LOS would not need a LIMIT or limit hint to be triggered. It could be a full scan. Some extra work may be needed if stale and consistent reads can't currently be combined in a transaction.

**Describe alternatives you've considered**
None

Jira issue: CRDB-24171