Troubleshooting expensive query

1

Hi,

I’m querying an InfluxDB 2.7.7 instance. I’m storing grocery prices and I have written a query to get recent price changes. Unfortunately it’s quite slow and I would like to work out why.

SELECT "time","last","difference","name"::tag
FROM (
    SELECT difference(last("cents")),last("cents")
    FROM "autogen"."product" 
    WHERE $timeFilter 
    GROUP BY time($__interval),* 
    LIMIT 1)
WHERE difference != 0 AND last != 0 AND difference != last

The inner query returns the differences between the last value of the “cents” field and the second-to-last, as well as the current value of the “cents” field. The “GROUP BY” clause means all selected metrics need to have grouping functions, hence the “last()”.
The outer query filters this down to only non-zero differences, returning the time, last value, difference and the “name” tag for each series.

This is too expensive to run per-client, so I’m having to run it on a cronjob and store the result.

Can anyone suggest any optimisations to this query that might achieve the same thing without stressing the InfluxDB server so much?

Cheers,
tjhowse.

2

Can you run it for a narrower time range by adding something like this WHERE time > now() - 7d ?