Optimize query for finding last uptrend

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?

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.


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

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