Postgres lag() function has huge impact on query

Question

As reported to my previous question (that for completeness is reported here), I've solved my problem using the window function LAG to get the time between the previous and next row, sum them and then extract the minutes of production hours of my solar panel system.

The schema of the table is the following one:

+-----------------------------------------------------------------------------------------------------+
|                                     pk,insert_time,data,sensor                                      |
+-----------------------------------------------------------------------------------------------------+
| 3003711,2020-10-03 09:55:54.271738+00,"{""smart_device_id"": 12, ""potenza_kw"": 0, ""temp"": 20.8, ""lux"": 2.0}",12   |
| 3003692,2020-10-03 09:54:54.289131+00,"{""smart_device_id"": 12, ""potenza_kw"": 0, ""temp"": 20.6, ""lux"": 2.0}",12   |
| 3003681,2020-10-03 09:53:54.287502+00,"{""smart_device_id"": 12, ""potenza_kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003670,2020-10-03 09:52:54.284262+00,"{""smart_device_id"": 12, ""potenza_kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003659,2020-10-03 09:51:56.382746+00,"{""smart_device_id"": 12, ""potenza_kw"": 12, ""temp"": 20.5, ""lux"": 2.0}",12  |
| 3003648,2020-10-03 09:50:54.279558+00,"{""smart_device_id"": 12, ""potenza_kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
| 3003637,2020-10-03 09:49:56.377988+00,"{""smart_device_id"": 12, ""potenza_kw"": 9.0, ""temp"": 20.5, ""lux"": 2.0}",12 |
+-----------------------------------------------------------------------------------------------------+

Basically, with the following query, I'm able to specify a month and a year and then calculate the total seconds of kW production, after that, I'll calculate the total hours and days over the cte aggregated table, here is the complete query:

with cte_temp as (
    SELECT mt.insert_time,
              (DATE_PART('day', lag(mt.insert_time) OVER w - mt.insert_time) * 24 + --Calculates the delta between the two measurements in seconds
               DATE_PART('hour', lag(mt.insert_time) OVER w - mt.insert_time)* 60 +
               DATE_PART('minute', lag(mt.insert_time) OVER w - mt.insert_time) * 60 +
               DATE_PART('second', lag(mt.insert_time) OVER w - mt.insert_time)) as total_seconds
    FROM tv_smartdevicemeasurement_mqtt mt 
    WHERE smart_device_id = 8  -- Filter Section
        AND (mt.data->>'potenza_kw')::float > 1
        AND date_part('month', mt.insert_time) = 10
        AND date_part('year', mt.insert_time) = 2020
    WINDOW w AS (order by insert_time desc)
)

SELECT --Calculates total sum of hours and days over temp cte table
 TRUNC(sum("total_seconds")::numeric/3600, 2) AS "Hours" --Hours
from cte_temp

I have a lot of issues with this query: i need to repeat this query (on grafana) for all my devices (that are around 15) and it takes a lot of time: producing very high CPU and Memory usage, for completeness, here it's the execution plan with EXPLAIN ANALYZE VERBOSE

Is anyone able to give me some alternative solutions to that?

Answer 1

Answer to the question asked

You can simplify the query quite a bit:

SELECT trunc((EXTRACT(epoch FROM max(insert_time) - min(insert_time)) / 3600)::numeric, 2) AS hours  -- !
FROM   tv_smartdevicemeasurement_mqtt
WHERE  (data->>'smart_device_id')::int = 8  -- filter section
AND    (data->>'potenza_kw')::float > 1
AND    insert_time >= '2020-10-01'  -- assuming current time zone
AND    insert_time <  '2020-11-01'; -- like jjanes suggested

db<>fiddle here

I adjusted your filter smart_device_id = 8 to (data->>'smart_device_id')::int = 8 to match your sample data, where smart_device_id is actually a nested JSON field. (It really should be a dedicated column, though; see below.)

Since you effectively sum up all time differences anyway, it should be substantially cheaper to just take the difference between maximum and minimum.

Also, just compute the interval, extract the epoch (number of seconds) and divide by 3600 to get hours. See:

• PostgreSQL Division In Query Not Working

Saves the overhead of the CTE and needless multiple lag() calls and following computations.

But like jjanes already stated, the bulk of the time seems to be spent just reading data. However, the switch to min() and max() should allow to reduce that dramatically, as we only need to read 2 rows now, and those can be read from the index.

If possible, break out (data->>'potenza_kw')::float as dedicated column. Makes the filter cheaper. And makes the table smaller if that actually replaces the JSON field - like the whole table would be substantially smaller with dedicated columns instead of a JSON document (making everything faster). Your columns seem to be static, so there is really no need for JSON.

Since you run this query a lot, use a multicolumn index like jjanes suggested. And if half the rows (or more) don't pass the filter potenza_kw > 1 (like your sample data suggests) it should help some more to make it a partial index:

CREATE INDEX ON tv_smartdevicemeasurement_mqtt (smart_device_id, insert_time)
WHERE potenza_kw > 1;

That's assuming a dedicated column potenza_kw. Else use (more expensively):

...
WHERE (data->>'potenza_kw')::float > 1;

If your table is vacuumed enough either should allow faster index-only scans. You may want to vacuum your table more aggressively. Per-table autovacuum settings are possible. See:

• Debug query on big table that is sometimes slow
• Tiny table causes extreme performance degradation, fixed by forced VACUUM. Why?

Calculation incorrect

The above answers the question asked. But I suspect that your whole calculation is incorrect to begin with.

According to your previous question, you ...

need to get the exact amount of working hour of my solar panel production system, I'm assuming that the solar panel "works" only when the kW value is greater than zero.

But you actually exclude rows with potenza_kw <= 1 before computing time ranges (0 or 1 as threshold, that's an aside). This way you get misleading ranges that can include time periods with potenza_kw under the threshold ("gaps").

We do not actually know what happened between two rows. But assuming a steady curve, we can at least get a decent approximation - with a completely different approach ....

SELECT trunc((EXTRACT(epoch FROM sum(work))/ 3600)::numeric, 2) AS hours
FROM (
   SELECT max(insert_time) FILTER (WHERE kw >= 1)  -- last row above threshold per group
        - min(insert_time) AS work
   FROM  (
      SELECT *
           , count(*) FILTER(WHERE kw >= 1 AND last_kw < 1) OVER (ORDER BY insert_time) AS grp
      FROM  (
         SELECT insert_time, (data->>'potenza_kw')::float AS kw
              , lag((data->>'potenza_kw')::float) OVER (ORDER BY insert_time) AS last_kw
         FROM   tv_smartdevicemeasurement_mqtt
         WHERE  (data->>'smart_device_id')::int = 12  -- filter section
         AND    insert_time >= '2020-10-01'  -- assuming current time zone
         AND    insert_time <  '2020-11-01'
         ORDER  BY insert_time
         ) sub1
      ) sub2
   GROUP  BY grp
   ) sub3;

db<>fiddle here

I extended the test data in the second fiddle to demonstrate the error of your original query.

Step-by-step explanation

The first subquery sub1 retrieves potenza_kw from the previous row.

The second subquery sub2 identifies rows where a new group with values above threshold starts (WHERE kw >= 1 AND last_kw < 1) - and counts those start events to effectively form groups ("islands") - every new patch gets a grp number incremented by 1.

The third subquery sub3 computes the duration of actual work withing each group by subtracting max - min - after cutting off trailing rows without work from max: FILTER (WHERE kw >= 1).

The outer SELECT sums working periods truncates like you demonstrated.

Note that the approximation is slightly pessimistic as islands begin and end abruptly. You might interpolate bounds to get a better estimate. But with one row per minute the avg. error is around 1 minute per island. You could just add 1 minute per island ...

You can find more in-depth explanation in the linked answers below.

To get the absolute best performance, consider a procedural solution in this case, though, because that can make do with a single pass over the table. See:

• Grouping or Window
• Select longest continuous sequence

Simple alternative

If you reliably have 1 row per minute, there is a different, much simpler approach:

SELECT count(*) AS minutes
FROM   tv_smartdevicemeasurement_mqtt
WHERE  (data->>'smart_device_id')::int = 12  -- filter section
AND    insert_time >= '2020-10-01'
AND    insert_time <  '2020-11-01'
AND    (data->>'potenza_kw')::float >= 1;

db<>fiddle here

Should be pretty obvious what it does. It takes a whole minute per measurement above threshold, so it is not pessimistic like the above is.
Returning minutes for simplicity - format any way you like.

Answer 2

It looks like almost all the time is spent just reading the data. I don't think the window function really has anything to do with the poor performance. The timestamp selection would probably be better written as this:

insert_time>='2020-10-01' and insert_time < '2020-11-01'

especially if you had an index it could use, probably on (smart_device_id, insert_time).

Finally, clustering your table so that all the data which will be needed together is found together should help a lot. Maybe use partitioning on smart_device_id so that it stays clustered.