sql: virtual table constrained scan

[Azhng]

Is your feature request related to a problem? Please describe.

Currently, there's no way perform an efficient constrained scan virtual table. That means, if a virtual table utilizes a regular SQL table under the hood, there's no way for the virtual table to utilize the indexes from the underlying SQL table, resulting in the bad bad query plans where SQL engine performs full table scan. This becomes more problematic if the sorting is involved which result in a small query fall back to disk spilling sort. E.g. #74680 #71245

Describe the solution you'd like
The virtual table generator / populate hook should have the predicate information in order to make smarter decisions.

Jira issue: CRDB-12275

Epic CRDB-14840

[Azhng]

cc @cockroachdb/sql-queries

[ajwerner]

Well, you can push down a predicate which fully specifies a row with the virtualIndex construct. The problem with it is that it doesn't work if you want to scan a constrained portion of the virtual table.

[Azhng]

Ah good point, changed the title.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!