SQL query uses TopN when grouping by time and other dimension
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
### Description
When querying the SQL druid endpoint with a query doing a group-by on two fields and on of them is time related, the TopN query-type could be used instead of the group-by one, with the time grouping being implemented as "granularity".
Example:
```
EXPLAIN PLAN FOR
SELECT FLOOR("__time" TO MONTH) AS "__timestamp",
"my_field" AS "my_field",
SUM(my_value) AS "sum_my_value"
FROM my_data_source
WHERE "__time" >= '...'
AND "__time" < '...'
GROUP BY "my_field", "FLOOR("__time" TO MONTH)
ORDER BY sum_my_value
LIMIT 500;
DruidQueryRel(query=[{"queryType":"groupBy","dataSource":{"type":"table","name":"my_data_source"}...
```
### Motivation
Group-by queries are a lot more expensive than TopN queries, this change would allow to get results a lot faster and cheaper.
Contributor guide
Research direction
Start with the SQL Druid endpoint's query-planning path and the EXPLAIN PLAN example in the issue, focusing on how group-by queries select their query type and represent time grouping. Done means eligible groupings use a TopN query with time represented as granularity, while preserving the requested grouped results and ordering.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- databases, performance
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100