Postgres lag() 函式對查詢有巨大影響

正如我之前的問題所報告的那樣（為了完整性，這里報告了），我已經解決了我的問題，使用視窗函式LAG來獲取上一行和下一行之間的時間，將它們相加，然後提取我的太陽能電池板的生產時間分鐘數系統。

該表的架構如下：

+-----------------------------------------------------------------------------------------------------+
|                                     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 |
+-----------------------------------------------------------------------------------------------------+

基本上，通過以下查詢，我可以指定一個月和一年，然後計算kW生產的總秒數，然後，我將計算 cte 聚合表的總小時和天數，這是完整的查詢：

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

我對這個查詢有很多問題：我需要對我的所有設備（大約 15 個）重複這個查詢（在 grafana 上），這需要很多時間：為了完整性，產生非常高的 CPU 和記憶體使用率，here這是EXPLAIN ANALYZE VERBOSE的執行計劃

有人能給我一些替代解決方案嗎？

回答提出的問題

您可以大大簡化查詢：

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<>在這裡擺弄

我調整了您的過濾器smart_device_id = 8以(data->>'smart_device_id')::int = 8匹配您的範例數據，其中smart_device_id實際上是一個嵌套的 JSON 欄位。（不過，它確實應該是一個專門的專欄；見下文。）

由於無論如何您都有效地總結了所有時間差，因此只取最大值和最小值之間的差應該會便宜得多。

此外，只需計算間隔，提取紀元（秒數）並除以 3600 即可獲得小時數。看：

• 查詢中的 PostgreSQL 分區不起作用

節省 CTE 和不必要的多次lag()呼叫和後續計算的成本。

但就像 jjanes 已經說過的那樣，大部分時間似乎都花在了讀取數據上。但是，切換到min()andmax()應該可以顯著減少這一點，因為我們現在只需要讀取 2 行，並且可以從索引中讀取這些行。

如果可能，將其拆分(data->>'potenza_kw')::float為專用列。使過濾器更便宜。如果它實際上替換了 JSON 欄位，則使表格更小 - 就像使用專用列而不是 JSON 文件（使一切更快）一樣，整個表格會變得更小。您的列似乎是靜態的，因此確實不需要 JSON。

由於您經常執行此查詢，因此請使用jjanes 建議的多列索引。如果一半（或更多）行沒有通過過濾器potenza_kw > 1（如您的範例數據所示），它應該有助於使其成為部分索引：

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

假設有一個專用列potenza_kw。其他用途（更昂貴）：

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

如果您的表足夠真空，則應該允許更快的僅索引掃描。您可能想要更積極地吸塵您的桌子。可以按表autovacuum設置。看：

• 在有時很慢的大表上調試查詢
• Tiny table 會導致性能極度下降，由強制 VACUUM 修復。為什麼？

計算錯誤

以上回答了所提出的問題。但我懷疑你的整個計算一開始就不正確。

根據你之前的問題，你…

需要獲得我的太陽能電池板生產系統的確切工作時間，我假設太陽能電池板僅在 kW 值大於零時“工作”。

但是您實際上排除了計算時間範圍potenza_kw <= 1 之前的行（0 或 1 作為門檻值，這是一個旁白）。這樣，您會得到誤導性範圍，其中可能包括低於門檻值的時間段potenza_kw（“間隙”）。

我們實際上並不知道兩行之間發生了什麼。但是假設一條穩定的曲線，我們至少可以得到一個不錯的近似值——用一種完全不同的方法……

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<>在這裡擺弄

我在第二把小提琴中擴展了測試數據以展示您原始查詢的錯誤。

分步說明

第一個子查詢從前一行sub1檢索。potenza_kw

第二個子查詢sub2標識值高於門檻值的新組開始的行WHERE kw >= 1 AND last_kw < 1（grp

第三個子查詢通過在從 max: 中刪除沒有工作的尾隨行之後減去max - minsub3 -來計算每個組的實際工作持續時間。FILTER (WHERE kw >= 1)

外部SELECT總和工作時間會像您展示的那樣截斷。

請注意，隨著島嶼突然開始和結束，該近似值略顯悲觀。您可能會插入界限以獲得更好的估計。但每分鐘一排的平均值。每個島的誤差約為 1 分鐘。您可以在每個島嶼上增加 1 分鐘…

您可以在下面的連結答案中找到更深入的解釋。

不過，為了獲得絕對的最佳性能，請考慮在這種情況下使用程序解決方案，因為這可以通過單次遍歷表來解決。看：

• 分組或視窗
• 選擇最長的連續序列

簡單的替代方案

如果您可靠地每分鐘有 1 行，則有一種不同的、更簡單的方法：

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<>在這裡擺弄

它的作用應該很明顯。每次測量超過門檻值需要一整分鐘，因此並不像上面那樣悲觀。

為簡單起見返回分鐘 - 以您喜歡的任何方式格式化。

引用自：https://dba.stackexchange.com/questions/276637