sql: enable lookup joins to lookup spans, not just individual keys #51576
Description
Imagine you have tables like this:
CREATE TABLE metrics (
id SERIAL PRIMARY KEY,
name STRING,
INDEX name_index (name)
);
CREATE TABLE metric_values (
metric_id INT8,
time TIMESTAMPTZ,
value INT8,
PRIMARY KEY (metric_id, time)
);
Now say I want to look up a 10 minute interval of metric values for a particular metric:
SELECT *
FROM metric_values
INNER JOIN metrics
ON metric_id=id
WHERE
time BETWEEN '2020-01-01 00:00:00+00:00' AND '2020-01-01 00:10:00+00:00' AND
name='cpu.percent'
;
This is the resulting plan:
inner-join (lookup metric_values)
├── scan metrics@name_index
│ └── constraint: /6/5: [/'cpu.percent' - /'cpu.percent']
└── filters
└── (time >= '2020-01-01 00:00:00+00:00') AND (time <= '2020-01-01 00:10:00+00:00')
Notice that the time filter, which is actually the most important thing about this query, is not part of the lookup join criteria. That's because lookup joins only allow equality conditions for lookup. But what if lookup joins allowed (multi-column) spans? It'd make dynamic lookup cases like this vastly more efficient. In this example, what we really want is for the lookup join to operate on spans like this:
/id/time
[/1/'2020-01-01 00:00:00+00:00' - /1/'2020-01-01 00:10:00+00:00']
[/5/'2020-01-01 00:00:00+00:00' - /5/'2020-01-01 00:10:00+00:00']
[/6/'2020-01-01 00:00:00+00:00' - /6/'2020-01-01 00:10:00+00:00']
...
rather than simply as it does today:
/id
[/1 - /1]
[/5 - /5]
[/6 - /6]
Jira issue: CRDB-4031
Epic: CRDB-14091
gz#11736
Metadata
Metadata
Assignees
Labels
Type
Projects
Status