opt: support locality optimized search for scans with more than 1 row

[jseldess]

Jesse Seldess (jseldess) commented:

PR: cockroachdb/cockroach#69395

From release notes:

Locality-optimized search is now supported for scans that are guaranteed to return 100,000 keys or less. This optimization allows the execution engine to avoid visiting remote regions if all requested keys are found in the local region, thus reducing the latency of the query. [#69395][#69395]

Jira Issue: DOC-1313

[exalate-issue-sync]

Vy Ton (vy-ton) commented:
Stephanie Bodoff We no longer have a dedicated multi-region virtual team, so we are working on aligning multi-region pieces with the correct product area. This would go to SQL Queries, therefore I’m replacing Richard Loveland with you.

Richard Loveland noted this might be documented already

[exalate-issue-sync]

Stephanie Bodoff (stbof) commented:
@rytaft I'm a little confused by this improvement. Here it appears as a positive. However, this limitation spins it as negative: cockroachdb/cockroach#64862 (comment)

What am I missing?

[rytaft]

Hi @stbof -- it's a bit confusing since there are two parts to the issue and I only fixed one of them. I added support for locality optimized search for scans with more than one row, when the number of rows can be determined because a unique column (or set of unique columns) in the scan is limited to a specified number of values. For example, SELECT * FROM tab WHERE pk IN (1, 3, 5), where tab is a REGIONAL BY ROW table, and pk is the primary key.

What is still outstanding is support for the case when the number of rows can be determined based on a limit. For example, we do not yet support locality optimized search for SELECT * FROM tab LIMIT 3 where tab is a REGIONAL BY ROW table.

Does this help clarify?