PostgreSQL 視窗函式在大表上非常慢

我有一個 PostgreSQL 表構造為

device_id | point     | dt_edit
----------+-----------+----------
100       | geometry  | timestamp
101       | geometry  | timestamp
100       | geometry  | timestamp
102       | geometry  | timestamp
102       | geometry  | timestamp
101       | geometry  | timestamp

我需要選擇dt_edit從 unique排序的最後 2 條記錄device_id。這個查詢工作很慢，有 10 億條記錄 - 500 秒：

SELECT dt.device_id,
   dt.point,
   dt.dt_edit
  FROM ( SELECT gps_data.device_id,
           gps_data.point,
           gps_data.dt_edit,
           rank() OVER (PARTITION BY gps_data.device_id
                        ORDER BY gps_data.dt_edit DESC) AS rank
          FROM gps_data) dt
 WHERE dt.rank <= 2

有任何想法嗎？

如果我們可以假設一個device表格包含所有感興趣的設備。例子：

CREATE TABLE device (device_id int, device text);
INSERT INTO device (device_id, device) VALUES
 (100, 'a')
, (101, 'b')
, (102, 'c');

查詢可以非常簡單：

SELECT d.device_id, g.point, g.dt_edit
FROM   device d
,      LATERAL (
  SELECT point, dt_edit
  FROM   gps_data
  WHERE  device_id = d.device_id
  ORDER  BY dt_edit DESC NULLS LAST -- more items?
  LIMIT  2
  ) g;

假設 table 中的基數device比 table小得多gps_data，即後者中每個設備有很多行。

如果dt_edit不是UNIQUE，請添加更多項目ORDER BY以消除排序順序的歧義。

要使其快速執行，您只需要一個多列索引；

CREATE INDEX gps_data_foo_idx
ON gps_data (device_id, dt_edit DESC NULLS LAST); -- more items? 

如果列NULLS LAST可以為 NULL，則修飾符很有用，但無論如何都不會造成傷害。無論哪種方式，查詢都必須與索引匹配。

• PostgreSQL 按日期時間 asc 排序，先為空？

如果您沒有設備表，我建議您創建一個（並保持最新）。但是即使沒有桌子，

也有辦法讓這個速度更快……device

細節：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄
• 如何有效地獲取“最近的對應行”？
• 在多列上選擇 DISTINCT

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