search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.03k stars 3.79k forks source link

sql: virtual table constrained scan #74806

Open Azhng opened 2 years ago

Azhng commented 2 years ago

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 commented 2 years ago

cc @cockroachdb/sql-queries

ajwerner commented 2 years ago

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 commented 2 years ago

Ah good point, changed the title.

github-actions[bot] commented 11 months ago

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!