Optimize query for finding last uptrend

InfluxDB 2
1

I’m trying to capture the most recent point in time when a variable was in an up-trend (values were increasing). These represent the re-filling of a tank.
I’m only interested in capturing the dateTime that this happened. There can be a fair amount of time between re-fills for certain tanks, so the time range to consider is fairly broad (two weeks or so).

The best I currently have is the following query:

from(bucket: "Ingest_Test")
  |> range(start: -15d)
  |> aggregateWindow(every: 30m, fn: last)
  |> filter(fn: (r) =>
    r._field == "percentagefull"
  )
  |> difference(
    nonNegative: true,
    columns: ["_value"],
    keepFirst: false
    )
  |> filter(fn: (r) => 
    r._value >= 5
  )  
  |> group(columns: ["tank"] )
  |> sort(columns: ["_time"])
  |> last()
  |> group()
  |> map(fn: (r) => ({
  tank: r.tank,
  _value_date: r._time 
  }))

It does the job, but since we’re looking at a few dozen tanks, it’s also terrifyingly slow.
Is there a way that I can significantly improve the performance of this query?

2

You can try to do the filter on percentageFull before the aggregateWindow. In general for debugging queries use the flux profiler package profiler package | Flux 0.x Documentation I find it very useful.

3 Likes
3

Bygods… that makes a difference… :exploding_head:

I had somehow assumed that filter and aggregate window would automatically be combined into a single operation. Which is a stupid assumption, given the pipe-syntax. Too used to SQL, I suppose.

You sir, are a life-saver.

Now excuse me while I go re-write some other queries as well… :see_no_evil:

1 Like
4

Hello @christiaan,
This blog might be useful to you as well: