opt: tighten constraints on range scans of hash-sharded indexes to only necessary buckets

[michae2]

Consider the following range query on a table with a hash-sharded primary index:

CREATE TABLE a (a INT NOT NULL, PRIMARY KEY (a) USING HASH WITH (bucket_count = 16));
EXPLAIN (VERBOSE) SELECT * FROM a WHERE a >=1 AND a < 5;

The only possible buckets that could contain rows are:

• a=1: bucket 11
• a=2: bucket 6
• a=3: bucket 13
• a=4: bucket 2

And yet we build a plan that scans all 16 buckets, instead of just the 4 that cover this range. It would be nice to only scan the necessary buckets.

Jira issue: CRDB-50914

[michae2]

(This matters if one day we want to support very large numbers of buckets, to avoid the lumpiness problem of 16-bucket indexes.)

[yuzefovich]

[triage] if we convert this to IN expression, we might get this behavior for free