Query with lots of fields takes orders of magnitude higher in flux than influxql

I’ve been using this query on InfluxV1 and InfluxV2(on compatibility endpoint) and it takes a second or two.

SELECT 
mean("signal") * mean("fft_0001"),
mean("signal") * mean("fft_0002"),
mean("signal") * mean("fft_0003"),
mean("signal") * mean("fft_0004"),
....
mean("signal") * mean("fft_0510"),
mean("signal") * mean("fft_0511"),
mean("signal") * mean("fft_0512") FROM "data" WHERE "device" = 'XY' AND $timeFilter GROUP 
BY time($__interval) fill(null)

But a similar query in FluxQL takes order of magnitude higher and simply times out if I increase the time range. Is there potential to improve this query and bring it at par with the influxql one ?

import "strings"
filtered = from(bucket: v.defaultBucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "data" and and r["device"] == "XY")

signal = filtered
  |> filter(fn: (r) => r._field == "signal")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

fft = filtered
  |> filter(fn: (r) => strings.hasPrefix(v: r._field, prefix: "fft_"))
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

join(tables:{signal:signal,fft:fft}, on:["_time"])
  |> map(fn: (r) =>({ _value: float(v:r._value_signal) * float(v:r._value_fft),  _time: r._time }))
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

I’ve tried it on a beefier machine and still the same.

Tried naming all fields instead of using prefix: "fft_" and that takes even longer.

Hello @tintin,
I’m not sure. I’m sorry you’re having a bad experience. I’m sharing your question with the Flux team directly and I hope someone can provide some insight. Thank you.