You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "pjain1 (via GitHub)" <gi...@apache.org> on 2024/07/12 05:48:30 UTC
[I] Planner optimization for comparison query (druid)
pjain1 opened a new issue, #16728:
URL: https://github.com/apache/druid/issues/16728
For a comparison query that compares previous time range to current time range on some metric like below
```
SELECT
(COALESCE(base."page", comparison."page")) AS "page",
(ANY_VALUE(base."added")) AS "added",
(ANY_VALUE(comparison."added")) AS "added_prev",
(ANY_VALUE(base."added" - comparison."added")) AS "added_delta"
FROM
(SELECT "page", sum(added) AS "added" FROM "wikipedia" WHERE "__time" >= '2016-06-27T00:00:00.000Z' AND "__time" < '2016-06-27T01:00:00.000Z' GROUP BY 1 ORDER BY "added" DESC LIMIT 10) base
LEFT OUTER JOIN
(SELECT "page", sum(added) AS "added" FROM "wikipedia" WHERE "__time" >= '2016-06-27T01:00:00.000Z' AND "__time" < '2016-06-27T02:00:00.000Z' GROUP BY 1) comparison
ON
(base."page" IS NOT DISTINCT FROM comparison."page")
GROUP BY 1
ORDER BY "added" DESC
LIMIT 10
```
Druid calculates the base(left) and comparison(right) inner queries and joins them on `page` dimension. However if the dimension is high cardinality, the comparison query might fail with `ResourceLimitExceededException` as it might exceed `maxSubqueryBytes` limit. Limit cannot be pushed to the comparison query as it might have different topNs than the base one. An inner query selecting topn values from base time range also cannot be used in the where clause of comparison query as if there is a `null` value then `<NULL> IN NULL` comparison is false so `null` value in comparison query will be ignored.
Druid can however compute the base query first and push the join values into the comparison query to limit the comparison query results. Can planner do this optimization or any other ideas ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "kgyrtkirk (via GitHub)" <gi...@apache.org>.
kgyrtkirk commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2224951243
interesting; I think these things could be supported in general with some kind of semijoin
however for the above query I think there is an alterante way to write the query:
instead of:
```
(SELECT "page", sum(added) AS "added" FROM "wikipedia" WHERE "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T01:00:00.000Z' GROUP BY 1 ORDER BY "added" DESC LIMIT 33) base
LEFT OUTER JOIN
(SELECT "page", sum(added) AS "added" FROM "wikipedia" WHERE "__time" >= '2015-09-12T01:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z' GROUP BY 1) comparison
ON
(base."page" IS NOT DISTINCT FROM comparison."page")
```
a single query could be given to collect both aggregates
```
SELECT "page",
sum(added) filter (where "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T01:00:00.000Z') as base_added,
sum(added) filter (where "__time" >= '2015-09-12T01:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z') AS comparison_added
FROM "wikipedia" WHERE "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z' GROUP BY 1 ORDER BY "base_added" DESC LIMIT 33
```
I believe this rewrite could not be done in all cases; but might worth a try; could you check if this would help? or if yes how much?
not sure if the input query could be recognized and rewritten to this one automatically...it doesn't seem impossible; but right now I feel like that would be a bit fragile
<details>
<summary>queries used/etc
</summary>
modified base query
```sql
SELECT
(COALESCE(base."page", comparison."page")) AS "page",
(ANY_VALUE(base."added")) AS "added",
(ANY_VALUE(comparison."added")) AS "added_prev",
(ANY_VALUE(base."added" - comparison."added")) AS "added_delta"
FROM
(SELECT "page", sum(added) AS "added" FROM "wikipedia" WHERE "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T01:00:00.000Z' GROUP BY 1 ORDER BY "added" DESC LIMIT 33) base
LEFT OUTER JOIN
(SELECT "page", sum(added) AS "added" FROM "wikipedia" WHERE "__time" >= '2015-09-12T01:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z' GROUP BY 1) comparison
ON
(base."page" IS NOT DISTINCT FROM comparison."page")
GROUP BY 1
ORDER BY "added" DESC
LIMIT 33
```
plan is to join a topN and a gby result
rewritten query
```
SELECT
page,
(ANY_VALUE(base_added)) AS "added",
(ANY_VALUE(comparison_added)) AS "added_prev",
(ANY_VALUE(base_added - comparison_added)) AS "added_delta"
FROM
(SELECT "page",
sum(added) filter (where "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T01:00:00.000Z') as base_added,
sum(added) filter (where "__time" >= '2015-09-12T01:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z') AS comparison_added
FROM "wikipedia" WHERE "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z' GROUP BY 1 ORDER BY "base_added" DESC LIMIT 33) base
GROUP BY 1
ORDER BY "added" DESC
LIMIT 33
```
plan is to do a gby on a topn
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "kgyrtkirk (via GitHub)" <gi...@apache.org>.
kgyrtkirk commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2228976930
interesting; for me even a similar query to yours is planned as a topn
```sql
SELECT "page",
sum(added) filter (where "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T01:00:00.000Z') as base_added,
sum(added) filter (where "__time" >= '2015-09-12T01:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z') AS comparison_added,
sum(added) filter (where "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T01:00:00.000Z') -
sum(added) filter (where "__time" >= '2015-09-12T01:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z') AS gx
FROM "wikipedia" WHERE "__time" >= '2015-09-12T00:00:00.000Z' AND "__time" < '2015-09-12T20:00:00.000Z' GROUP BY 1 ORDER BY "gx" DESC LIMIT 33
```
<details>
<summary>plan</summary>
```json
[
{
"query": {
"queryType": "topN",
"dataSource": {
"type": "table",
"name": "wikipedia"
},
"dimension": {
"type": "default",
"dimension": "page",
"outputName": "d0",
"outputType": "STRING"
},
"metric": {
"type": "numeric",
"metric": "p0"
},
"threshold": 33,
"intervals": {
"type": "intervals",
"intervals": [
"2015-09-12T00:00:00.000Z/2015-09-12T20:00:00.000Z"
]
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "filtered",
"aggregator": {
"type": "longSum",
"name": "a0",
"fieldName": "added"
},
"filter": {
"type": "range",
"column": "__time",
"matchValueType": "LONG",
"lower": 1442016000000,
"upper": 1442019600000,
"upperOpen": true
},
"name": "a0"
},
{
"type": "filtered",
"aggregator": {
"type": "longSum",
"name": "a1",
"fieldName": "added"
},
"filter": {
"type": "range",
"column": "__time",
"matchValueType": "LONG",
"lower": 1442019600000,
"upper": 1442088000000,
"upperOpen": true
},
"name": "a1"
}
],
"postAggregations": [
{
"type": "expression",
"name": "p0",
"expression": "(\"a0\" - \"a1\")",
"outputType": "LONG"
}
],
"context": {
"enableUnnest": true,
"implyFeature": "explain",
"implyUser": "asd",
"implyView": "sql",
"queryId": "PIVOT-f3daed67-f916-4027-a002-e0bff6572ea8-1",
"sqlOuterLimit": 5000,
"sqlQueryId": "c15c8d19-095c-40dd-aef5-92f30240b634",
"timeout": 300000
}
},
"signature": [
{
"name": "d0",
"type": "STRING"
},
{
"name": "a0",
"type": "LONG"
},
{
"name": "a1",
"type": "LONG"
},
{
"name": "p0",
"type": "LONG"
}
],
"columnMappings": [
{
"queryColumn": "d0",
"outputColumn": "page"
},
{
"queryColumn": "a0",
"outputColumn": "base_added"
},
{
"queryColumn": "a1",
"outputColumn": "comparison_added"
},
{
"queryColumn": "p0",
"outputColumn": "gx"
}
]
}
]
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "egor-ryashin (via GitHub)" <gi...@apache.org>.
egor-ryashin commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2228938273
Somehow FILTER is 2x slower on my datasource. I tried these two:
```
select "camp",
sum(cnt+1) filter (where __time >= '2024-06-01' and __time < '2024-06-07') p,
sum(cnt) filter (where __time >= '2024-06-07' and __time < '2024-06-14') n,
sum(cnt) filter (where __time >= '2024-06-07' and __time < '2024-06-14') - sum(cnt) filter (where __time >= '2024-06-01' and __time < '2024-06-07') delta
from
atd_pba where __time >= '2024-06-01' and __time < '2024-06-14'
group by 1
order by delta
limit 10
```
```
select p."ucomp", any_value(p.c), any_value(n.c), any_value(n.c- p.c ) delta from (
select "ucomp" , sum(cnt) c from atd_pba where __time >= '2024-06-01' and __time < '2024-06-07' group by ucomp
) p
left join
(
select "ucomp" , sum(cnt) c from atd_pba where __time >= '2024-06-07' and __time < '2024-06-14' group by ucomp
) n
on p.ucomp is not distinct from n.ucomp
group by 1
order by delta
limit 10
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "pjain1 (via GitHub)" <gi...@apache.org>.
pjain1 commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2225819998
Thanks @kgyrtkirk for the inputs, it works really well is cases where metric definition is simple. In case where metric expression is a little involved, filter needs to be added to each agg function for example `sum(price) - sum(cost)` would need to become `sum(price) FILTER (…) - sum(cost) FILTER (…)` and so on. Also tried a semi-join approch but that does not work well in Druid, planner essentially does an inner join with whole underlying table.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2819762341
This issue has been marked as stale due to 280 days of inactivity.
It will be closed in 4 weeks if no further activity occurs. If this issue is still
relevant, please simply write any comment. Even if closed, you can still revive the
issue at any time or discuss it on the dev@druid.apache.org list.
Thank you for your contributions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #16728: Planner optimization for comparison query
URL: https://github.com/apache/druid/issues/16728
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "kgyrtkirk (via GitHub)" <gi...@apache.org>.
kgyrtkirk commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2228985465
yes; it supposed to plan as a `TopN` query - which works a bit differently ;nd iirc it doesn't support vectorization - so in smaller cases it may be slower
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2892567778
This issue has been closed due to lack of activity. If you think that
is incorrect, or the issue requires additional review, you can revive the issue at
any time.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "egor-ryashin (via GitHub)" <gi...@apache.org>.
egor-ryashin commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2228974771
Actually, as cardinality grows FILTER begins to outperform JOIN.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [I] Planner optimization for comparison query (druid)
Posted by "egor-ryashin (via GitHub)" <gi...@apache.org>.
egor-ryashin commented on issue #16728:
URL: https://github.com/apache/druid/issues/16728#issuecomment-2228989755
I added cardinality by grouping additional dimensions - now it looks like this:
```
select "camp", "buyer" , "source" ,
sum(cnt) filter (where __time >= '2024-06-01' and __time < '2024-06-07') p,
sum(cnt) filter (where __time >= '2024-06-07' and __time < '2024-06-14') n,
sum(cnt) filter (where __time >= '2024-06-07' and __time < '2024-06-14') - sum(cnt) filter (where __time >= '2024-06-01' and __time < '2024-06-07') delta
from atd_pba
where __time >= '2024-06-01' and __time < '2024-06-14'
group by 1,2,3
order by delta
limit 10
```
```
select p."camp", p. "buyer" ,p. "source",any_value(p.c), any_value(n.c), any_value(n.c- p.c ) delta from (
select "camp", "buyer" , "source" , sum(cnt) c from atd_pba where __time >= '2024-06-01' and __time < '2024-06-07' group by 1,2,3
) p
left join
(
select "camp", "buyer" , "source" , sum(cnt) c from atd_pba where __time >= '2024-06-07' and __time < '2024-06-14' group by 1,2,3
) n
on p.camp is not distinct from n.camp and p.buyer is not distinct from n.buyer and p.source is not distinct from n.source
group by 1,2,3
order by delta
limit 10
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org