Better handling of intervals in queries

Is your feature request related to a problem?

We found that posing queries having a where statement looking something like:
... WHERE (timestamp BETWEEN a AND b) OR (timestamp BETWEEN c AND d) ...
resulted in row scans over the whole dataset which lead to suboptimal performance.

Currently what we did is we did a query of a single timestamp range and essentially pre-processing so we grabbed the min( start_timestamps ) and the max(end_timestamps) then after receiving the dataset we split it up on our own. Even though we processed and transferred more data than necessary it was faster than the full record scan.

Describe the solution you'd like.

Since queries like:
WHERE (timestamp BETWEEN a AND b) AND (timestamp BETWEEN c AND d) ...
i.e. bolean AND operator instead of OR resulted in a query plan using interval forward scan and by the looks of it the intervals could be an array of multiple intervals.

My direct idea would be to aggregate the given intervals from the query and do a pre-processing step to determine the k number of sorted and non-overlaping intervals based of the query, then use this list of non-overlapping queries as input to the interval forward scan.

Describe alternatives you've considered.

As an alternative you could in the query engine do something similar to what we resorted to, generate the interval from min(start_timestamps) to max(end_timestamps) and use interval scan on that interval combined with filter on timestamp.

Full Name:

Daniel Wedlund

Affiliation:

Mounte AB

Additional context

Had a quick chat on slack regarding this.

Start by reproducing the reported SQL queries with OR- and AND-connected timestamp intervals, then trace the query engine's interval forward scan and interval handling. The work is complete when multiple sorted, non-overlapping intervals can be used for the scan without a full dataset scan, with the query results matching the timestamp predicates.