Query for "last timestamp" takes very long

[MatinF]

I'm trying to run below query to fetch the last timestamp of a specific measurement (an IoT device) across the various fields (parameters). My query looks as below via the Python API:

influx_time = self.client.query_api().query(
                    f'from(bucket:"{self.influx_bucket}") |> range(start: 0, stop: now()) |> filter(fn: (r) => r["_measurement"] == "{device}") |> keep(columns: ["_time"]) |> sort(columns: ["_time"], desc: false) |> last(column: "_time")'
                )

My question is whether there is an issue/bug with this query or the InfluxDB Python API? It seems to take ~76 seconds for a very light process. Any inputs are welcome.

[bednar]

Hi @MatinF,

thanks for using our client.

My question is whether there is an issue/bug with this query or the InfluxDB Python API?

There isn't known issue for this type of query.

• How long takes this query in InfluxDB UI?
• Do you have installed ciso8601 for better performance of parsing data (https://github.com/influxdata/influxdb-client-python#installation)?
• How many records your query returns?

Regards

[MatinF]

@bednar Thanks for the quick reply! And appreciate the suggestion on testing it in the UI.

I did test it there, and you're right that the time it takes is the same as in the Python API, so it seems it's probably related to the query itself being perhaps inefficient. The reason I believe that is the case is that when I simply want to query all the field time series for the last 30 days, the query for that is completed within <1 second - and it essentially carries the information I need.

Basically my use case is as follows:

• I have a number of measurements, representing IoT devices
• Each device uploads a number of parameters (e.g. speed, temperature etc) as time series as below image
• To upload the data, we're executing a Python script periodically. As part of the script we need to be able to determine what data to process for each device. We achieve this by getting the "most recent" timestamp per device via the Flux query mentioned here, which gets the last timestamp across all fields for the specific measurement

While the query returns the right timestamp value, I'm thinking there must be a more efficient/scalable way to query the most recent timestamp across all fields for a measurement. If you have any suggestions for this, it would be greatly appreciated!

[bednar]

I am not expert to optimize the Flux query, but there is a few suggestions:

• range(start: 0, stop: now()) - this is a fullscan of database. I don't know how often your devices publish their metrics, but what do you think about change the start to something like -30d?
• |> sort(columns: ["_time"], desc: false) - this is default sorting, you can skip it

There is a doc about optimize Flux queries - https://docs.influxdata.com/influxdb/cloud/query-data/optimize-queries/ and also we have dedicate Flux channel in our Slack - https://influxcommunity.slack.com/archives/CH8RGRW4T

[bednar]

This issue has been closed because it has not had recent activity. Please reopen if this issue is still important to you and you have additionally information.

[MatinF]

It seems modifying the query to below significantly speeds up the query:

influx_time = self.client.query_api().query(
                    f'from(bucket:"{self.influx_bucket}") |> range(start: -100d) |> filter(fn: (r) => r["_measurement"] == "{device}") |> group() |> last()'
                )