apache / apache/druid

SQL query uses TopN when grouping by time and other dimension

Open
#11,264 0 comments 0 reactions 0 assignees View on GitHub
Area - SQL Feature/Change Description
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.