Postgres lag() function has huge impact on query 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,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 0, &quot;&quot;temp&quot;&quot;: 20.8, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,12   |
| 3003692,2020-10-03 09:54:54.289131+00,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 0, &quot;&quot;temp&quot;&quot;: 20.6, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,12   |
| 3003681,2020-10-03 09:53:54.287502+00,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 9.0, &quot;&quot;temp&quot;&quot;: 20.5, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,12 |
| 3003670,2020-10-03 09:52:54.284262+00,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 9.0, &quot;&quot;temp&quot;&quot;: 20.5, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,12 |
| 3003659,2020-10-03 09:51:56.382746+00,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 12, &quot;&quot;temp&quot;&quot;: 20.5, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,12  |
| 3003648,2020-10-03 09:50:54.279558+00,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 9.0, &quot;&quot;temp&quot;&quot;: 20.5, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,12 |
| 3003637,2020-10-03 09:49:56.377988+00,&quot;{&quot;&quot;smart_device_id&quot;&quot;: 12, &quot;&quot;potenza_kw&quot;&quot;: 9.0, &quot;&quot;temp&quot;&quot;: 20.5, &quot;&quot;lux&quot;&quot;: 2.0}&quot;,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-&gt;&gt;'potenza_kw')::float &gt; 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(&quot;total_seconds&quot;)::numeric/3600, 2) AS &quot;Hours&quot; --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?
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&gt;='2020-10-01' and insert_time &lt; '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.