如何在 PostgreSQL 中使 DISTINCT ON 更快？

我station_logs在 PostgreSQL 9.6 數據庫中有一個表：

   Column     |            Type             |    
---------------+-----------------------------+
id            | bigint                      | bigserial
station_id    | integer                     | not null
submitted_at  | timestamp without time zone | 
level_sensor  | double precision            | 
Indexes:
   "station_logs_pkey" PRIMARY KEY, btree (id)
   "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

我試圖獲得level_sensor基於submitted_at,的最後一個值station_id。大約有 400 個唯一station_id值，每個station_id.

創建索引之前：

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

唯一（成本=4347852.14..4450301.72 行=89 寬度=20）（實際時間=22202.080..27619.167 行=98 循環=1）
-> 排序（成本=4347852.14..4399076.93 行=20489916 寬度=20）（實際時間=22202.077..26540.827 行=20489812 循環=1）
排序鍵：station_id，submitted_at DESC
排序方法：外部合併磁碟：681040kB
-> Seq Scan on station_logs (cost=0.00..598895.16 rows=20489916 width=20) (實際時間=0.023..3443.587 rows=20489812 loops=$
規劃時間：0.072 ms
執行時間：27690.644 ms

創建索引：

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

創建索引後，對於相同的查詢：

唯一（成本=0.56..2156367.51 行=89 寬度=20）（實際時間=0.184..16263.413 行=98 循環=1）
-> 在 station_logs 上使用 station_id__submitted_at 進行索引掃描（成本=0.56..2105142.98 行=20489812 寬度=20）（實際時間=0.181..1$
規劃時間：0.206 ms
執行時間：16263.490 ms

有沒有辦法讓這個查詢更快？例如 1 秒，16 秒仍然太多。

僅對於 400 個站點，此查詢將大大加快：

SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
  SELECT submitted_at, level_sensor
  FROM   station_logs
  WHERE  station_id = s.station_id
  ORDER  BY submitted_at DESC NULLS LAST
  LIMIT  1
  ) l;

dbfiddle here （比較此查詢的計劃，Abelisto 的替代方案和您的原始方案）

結果**EXPLAIN ANALYZE**由 OP 提供：

Nested Loop  (cost=0.56..356.65 rows=102 width=20) (actual time=0.034..0.979 rows=98 loops=1)
  ->  Seq Scan on stations s  (cost=0.00..3.02 rows=102 width=4) (actual time=0.009..0.016 rows=102 loops=1)
  ->  Limit  (cost=0.56..3.45 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=102)
        ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..664062.38 rows=230223 width=16) (actual time=0.009$
              Index Cond: (station_id = s.id)
Planning time: 0.542 ms
Execution time: <b>1.013 ms</b>  -- !!

您需要的唯一索引是您創建的索引：station_id__submitted_at. 基本上，UNIQUE約束uniq_sid_sat也可以完成這項工作。維護兩者似乎浪費了磁碟空間和寫入性能。

我在查詢中添加了NULLS LASTtoORDER BY因為submitted_atis not defined NOT NULL。理想情況下，如果適用！NOT NULL向列添加約束submitted_at，刪除附加索引並NULLS LAST從查詢中刪除。

如果submitted_at可以NULL，請創建此UNIQUE索引以替換目前索引和唯一約束：

CREATE UNIQUE INDEX station_logs_uni ON station_logs(station_id, submitted_at DESC NULLS LAST);

考慮：

• 在 Postgres 列上創建唯一約束是否消除了對其編制索引的需要？
• PostgreSQL 如何強制執行 UNIQUE 約束/它使用什麼類型的索引？

這是假設一個單獨的表station，每個相關（通常是 PK）有一行station_id- 你應該有任何一種方式。如果沒有，請創建它。同樣，使用這種 rCTE 技術非常快：

CREATE TABLE station AS
WITH RECURSIVE cte AS (
  (
  SELECT station_id
  FROM   station_logs
  ORDER  BY station_id
  LIMIT  1
  )
  UNION ALL
  SELECT l.station_id
  FROM   cte c
  ,      LATERAL (   
     SELECT station_id
     FROM   station_logs
     WHERE  station_id > c.station_id
     ORDER  BY station_id
     LIMIT  1
     ) l
  )
TABLE cte;

我也在小提琴中使用它。您可以使用類似的查詢直接解決您的任務，無需station表格 - 如果您無法說服創建它。

詳細說明、解釋和替代方案：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄
• 在每個 GROUP BY 組中選擇第一行？

優化索引

您的查詢現在應該非常快。僅當您仍需要優化讀取性能時…

將level_sensor作為最後一列添加到索引以允許僅索引掃描可能是有意義的，例如joanolo commented。

*缺點：*它使索引更大 - 這為使用它的所有查詢增加了一點成本。

*優點：*如果你真的只掃描索引，手頭的查詢根本不需要訪問堆頁面，這使它的速度大約是原來的兩倍。但這對於現在非常快速的查詢來說可能是微不足道的收穫。

但是，我不希望這適用於您的情況。你提到：

… 每天大約 20k 行station_id。

通常，這將表明不斷的寫入負載（station_id每 5 秒 1 次）。並且您對最新的行感興趣。僅索引掃描僅適用於所有事務可見的堆頁面（可見性映射中的位已設置）。您將不得不為VACUUM表執行極其激進的設置以跟上寫入負載，而且它在大多數情況下仍然無法正常工作。如果我的假設是正確的，那麼僅索引掃描就出來了，不要添加level_sensor到索引中。

OTOH，如果我的假設成立，並且您的表格變得非常大，那麼BRIN 索引可能會有所幫助。有關的：

• 加快 Postgres 部分索引的創建

或者，更專業和更高效：僅用於最新添加的部分索引，以切斷大量不相關的行：

CREATE INDEX station_id__submitted_at_recent_idx ON station_logs(station_id, submitted_at DESC NULLS LAST)
WHERE submitted_at > '2017-06-24 00:00';

選擇一個您知道必須存在較年輕行的時間戳。您必須為所有查詢添加匹配WHERE條件，例如：

...
WHERE  station_id = s.station_id
AND    submitted_at > '2017-06-24 00:00'
...

您必須不時調整索引和查詢。

更多詳細資訊的相關答案：

• 日期索引優化
• 將日期時間約束添加到 PostgreSQL 多列部分索引
• 使用 LIMIT 1 索引 ORDER BY

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