Query needs optimization but I don't know how

1

Hi all, I would love help optimizing this query. Right now, it times out even trying to pull 7 days worth of data. It works at 2days. I’m just trying to get max SOC and min SOC in a pivot format at 1min time increments. Any help is much appreciated!

ts = from(bucket: "rt-data")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "site")
  |> filter(fn: (r) => r["point_name"] == "SOC")

  ts1 =  ts  |> aggregateWindow(every: 1m, fn: max)
  ts2 =  ts  |> aggregateWindow(every: 1m, fn: min) 

join(tables: {s1: ts1, s2: ts2 }, on: ["_time"], method: "inner")
  |> map(fn: (r) => ({ r with maxsoc: r._value_s1 })) 
  |> map(fn: (r) => ({ r with minsoc: r._value_s2 }))  

|> drop(columns: ["root_s1", "root_s2", "_value_s1", "_value_s2", "_start_s1", "_start_s2", "_stop_s1", "_stop_s2", "_field_s1", "_field_s2", "_measurement_s1", "_measurement_s2", "point_name_s1", "point_name_s2", "register_s1", "register_s2", "site_controller_id_s1", "site_controller_id_s2", "site_name_s1", "site_name_s2", "source_device_id_s1", "source_device_id_s2", "site_controller_s1", "site_controller_s2", "source_device_type_s1", "source_device_type_s2"])
2

Hello @Marion_Akagi,
why do you need to get it in a pivot format?

You could try dropping the columns before the join. That might help a little.
I’m not sure I understand why you need to join the tables too. You can pivot without a join.
You can also join after the pivot.

3

I don’t think I need it in pivot format since I’m going to plot it on a time series graph. I didn’t know how to create two fields (min and max) from one field (soc). Thanks for any help you can provide. @Anaisdg

4

@Anaisdg I was reading over some of your other posts and tried this from one of them. Would this work?

common = from(bucket: "rt-data")

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)

|> filter(fn: (r) => r["_measurement"] == "site")

|> filter(fn: (r) => r["point_name"] == "SOC")

minsoc = common

|> filter(fn: (r) => r["point_name"] == "SOC")

|> aggregateWindow(every: 1d, fn: min, createEmpty: false)

|> yield(name: "min")

maxsoc = common

|> filter(fn: (r) => r["point_name"] == "SOC")

|> aggregateWindow(every: 1d, fn: max, createEmpty: false)

|> yield(name: "max")

union(tables: [minsoc, maxsoc])