Repository navigation
Scan query: time-ordering #6088
Description
Currently, the Select query is "better" than Scan in one case: it supports time ordering, so you can do queries like "latest 1000 records". But sadly, Select queries are known for excessive memory use (see #5006) so we would like to replace them with Scan whenever possible.
We could support time-ordering for Scan using an approach like the following:
- Analyze the segment timeline and start from the first (or last, if descending) chunk.
- If the limit is "low" (below some reasonable number like 100000, let's say) then maintain a priority queue of size = limit, and find the earliest (or latest) rows in the current chunk by scanning each segment in turn. If the current chunk can fill up the priority queue then we are done. If not then move on to the next chunk.
- If the limit is "high" then that approach won't work: it will use too much memory. Instead, we can do an N-way merge of the individual segments for the current chunk and send those results back to the client.
- If there are too many segments for an N-way merge sort (100s of segments in the time chunk) then that approach won't work: it will open up too many column selectors at once (each one has overhead: it needs decompression/decoding buffers). Instead, we can do a multi-level merge on disk. This is kind of lame (it will really slow down the query) but it's still better than what Select would do, which is crash the machine.
IMO, implementing only (2) will let people move more workloads to Scan (mostly stuff like "find the most recent X rows"), and so it would be a good start to just do that by itself.
Tagging this as "SQL" too, since if we can improve Scan to handle more cases, then we should also switch over the Druid SQL planner to use it instead of Select in those cases.
Activity
himanshug commentedon Aug 7, 2018
Implementing (3) makes sense to me as well with one additional change that if limit was L then it would work like below.
try finding latest L (or less) in first "hour", say some x < L found (write these x rows to response out)
then try finding latest (L-x) in second "hour"...
... keep going till you either find L rows or have been through all segments.
the "hour" could be "minute", "day" etc ... it is either provided by the user in the query or automatically determined (e.g. use segment granularity). By breaking it down, I'm basically hoping there wouldn't 100s of segments in one "hour" window leading to the problem mentioned in (4).
KenjiTakahashi commentedon Aug 7, 2018
FYI: I'd just like to see this kept as option in the query, so ones that do no need sorting avoid the performance hit.
I'd also point out that, besides the memory hit, select also tends to be very slow. In some of our cases, it was literally 100's of times vs. scan.
RestfulBlue commentedon Aug 18, 2018
Current implementation of select query is almost unusable. It cant be limited and can kill entire cluster. i hope this change will be implemented in near future. after that, i think current select query can be removed at all =)
gianm commentedon Aug 20, 2018
Agreed, we should keep the default as unsorted.
I'd definitely love to be able to remove the select query. I believe its design is not salvageable.
justinborromeo commentedon Jan 29, 2019
@gianm How would this look from an interface point of view? Would we be adding a new JSON field or is there an existing field where a user could specify to time-order results?
gianm commentedon Jan 29, 2019
My suggestion would be a new parameter "timeOrder" that is an enum which can be either "ascending", "descending", or "none", with "none" being the default if not provided.
justinborromeo commentedon Jan 29, 2019
Would it make sense to have both
timeOrderand thedescendingparameter (from BaseQuery)?gianm commentedon Jan 29, 2019
Hmm, I don't think
descendingmakes sense for the Scan query. It's a boolean since it makes an assumption that normally queries have an ascending order but that it can be reversed if you want. But that's not really how Scan works, it would have three modes: ascending, descending, and unordered.IMO,
descendingshould not have been added to BaseQuery in the first place. Not all query types use it. In the GroupBy and Search queries, it's always false. GroupBy does have an ordering concept, but it's more powerful than a simple ascending vs. descending, so it works through thelimitSpecinstead.justinborromeo commentedon Jan 30, 2019
The way Scan is written now, the "scan query doesn't retain all rows in memory before rows can be returned to client" (http://druid.io/docs/latest/querying/scan-query.html) and the design of the code reflects this. Afaik, the proposed implementation for the time-ordering feature would require the broker to store all the returned rows either in memory or on disk before it could start returning rows in a sorted order. It seems that this would violate the main principle behind Scan...is this time-ordering feature something that's appropriate for Scan or am I misunderstanding something?
gianm commentedon Jan 30, 2019
IMO it’s appropriate and would just need to involve changes to the docs to reflect new behaviors. Scan in that case would be able to start returning rows immediately in unordered mode, and would buffer them for sorting in ordered mode.
justinborromeo commentedon Jan 31, 2019
The current output of the scan query is a collection of ScanResultValue objects (String segmentId, List columns, Object events) where each ScanResultValue contains the rows from a specific segment. Time-ordering scan results will cause events to not be nicely grouped by segment...for that reason, I don't think it makes sense to use that format.
The 3 options I've thought of:
Having a separate output format for time-ordered scans -> An example of such a format would be a collection of events where segmentId is a field of each event. This isn't great because Scan would have two output formats
Returning a ScanResultValue for each event -> This is pretty bad because there would be a ridiculous amount of extra overhead from the list of columns.
Changing the output format for all Scan queries -> I think this is a no-go because it would be a breaking change for existing clients
Are there any better alternatives?
gianm commentedon Jan 31, 2019
Ah, that's a good point. I can't think of a better kind of alternative. I do think (1) would make the most sense, with as small a change as possible, like setting
segmentIdto null when time-ordering is enabled. (But still batching things based on the batch size.)justinborromeo commentedon Feb 1, 2019
Should time-ordering be supported for legacy mode scan queries? Just wondering since the timestamp format is different.
gianm commentedon Feb 1, 2019
I think it's fine to not add the feature to legacy mode.
justinborromeo commentedon Feb 1, 2019
If someone submits a Druid SQL query to order by timestamp with a limit > threshold, is the ideal behaviour to fail the query completely or silently turn off time ordering.
gianm commentedon Feb 1, 2019
In that case what do you mean by
threshold? The max threshold possible for time ordering (which I guess should be a config)? Possibly return an error that queries with time ordering above a certain limit aren't currently supported.bputt commentedon Feb 26, 2019
@gianm @justinborromeo
Are there any plans on supporting order by a non timestamp field? Specifically interested in this feature for the select query type.
vogievetsky commentedon Sep 25, 2019
@gianm is this done or are we waiting for some phase 2?
gianm commentedon May 31, 2023
It is done.