Aggregation over different measurements

Fluxlang
, ,
1

I have two measurements m1 and m2 each of them has a field f. I want to calculate the mean over both fields f of the two measuremensts m1 and m2.

Here my query:

from(bucket: "mybucket")
 |> range(start: 2020-01-27T23:00:00.000Z, stop: 2020-01-28T22:59:00.000Z)
 |> filter(fn: (r) => (r._measurement == "m1" and r._field == "f") or (r._measurement == "m2" and r._field == "f"))
 |> drop(columns: ["_start", "_stop", "_measurement", "_field"])
 |> aggregateWindow(every: 1m, fn: mean)

This query is ver very slow. How can I make the query better?

2

@thais A few suggestions:

  • One thing that will improve query performance speed is using larger window durations. You’re querying 24 hours of data and windowing it every minute. This means you’ll have approximately 1440 windows per unique group in your data set. Go with a longer window duration, like 5 or 10 minutes.
  • I don’t know if it will be much quicker, but you can improve how some of the filter logic works. See my example below.
  • I wouldn’t worry about dropping columns before you run the aggregate.
  • By default, all the returned points will be grouped by measurement so when you run an aggregate, you’ll get separate values per window per measurement. Remove the grouping to group all points into a single table, no matter which measurement they came from.
from(bucket: "mybucket")
  |> range(start: 2020-01-27T23:00:00.000Z, stop: 2020-01-28T22:59:00.000Z)
  |> filter(fn: (r) => r._measurement =~ /m[1-2]/ and r._field == "f")
  |> group()
  |> aggregateWindow(every: 10m, fn: mean)

Some other optimization tips here: Optimize Flux queries | InfluxDB OSS 2.0 Documentation

3

Thanks for your response!
Yeah a larger window duration helped a lot.
Grouping and droping didn’t help.
I will also increse my focus on downsampling the data, this also helps.
I hope that the Flux-Query performance will also increase soon…