Contains query performance (Finding an alternative)

Hello everyone,

I need some help in optimising a query. I need to join the timeseries data with data from PostgreSQL through the sql package. I managed to make the query work, but i have some performance issues.

The SQL data is paginated, where i read for example 50 devices. I need to left join that data with the last values for these 50 devices. The thing how i’m pulling Influx data now is for example:

data = from(bucket: "my-bucket")
    |> range(start: -13h)
    |> filter(fn: (r) => r._measurement == "my-measurement" and (r._field == "temperature" or r._field == "battery"))
    |> last()
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
    |> group(columns: ["device_address"])
    |> sort(columns: ["_time"], desc: true)
    |> limit(n: 1)
    |> group(columns: ["result"])

The problem is, this query fetches last values for around 50 000 different devices, and the query gets really slow. Where ReadWindowAggregate takes 99% of the query.

Obviously i would like to read only for these devices i read through sql, but i’m not sure if thats possible with flux. I tried contains but i still experienced bad performance for some reason. Even worse than without it. The ,device_address, i would like to filter by is a tag.

i thought about using a regex, but building a regex that big will not make it that much better.

Any advice? The InfluxDB version i’m using locally is 2.5.1, and flux version is v0.188.1